Hi all,
How to find out how many databases are there in a
single server.
example: server name 172.16.9.33 i want to find out how many
databases are there in this server.
Any solution for this plz help me.
Thanks&Regards,
Msrs
HI Guys,
I just need to add multiple catalogs to the same linked server, in Sql Server 2000 or Sql Server 2005. Here i need only to add 3 databases in to a single linked server name.
Thanks In Advance,
Krishna
have a Prod Server A having TDE enabled on 2 of those databases. I have a Prod Server B having TDE enabled on 3 of those databases. Now I have to create a single Dev server Server C for all the above 5 databases residing on the two servers. So how can I restore all the 5 database backup files on server C.
Does it mean that I need to copy the certificates and Keys from both the Prod server to this Dev Box and then restore the backup files. Once done, I can enable the encryption ON on those 5 database on Dev box or is there any different approach.Also how will tempdb behave in this scenario.
Hi,
I have two databases lets say DB1 and DB2.
Schemas for both databases is same.
In both database schemas there are tables which has identity columns as primary key.
Now i want to merge these two databases in a single database say DB3.
It may also possible that some master records in both databases are common so they should not repeat in DB3
Is there any way so that i can do it quickly and as soon as possible.
Thanks in advance
Rohit
Is it possible to have an ID that can access 2 databases? If it's possible, I have to create the same ID and password under each database?
View 6 Replies View RelatedJust wondering, in DTS, can I run a SELECT script that selects from 2 different databases (both on the same server)?
I can run this in Sql Query Analyzer, but in a DTS, it doesnt accept my database name prefixes:
SELECT
a.something
FROM
DB_ONE.dbo.product a, DB_ONE.dbo.mp_brand b, DB_TWO.dbo.lk_pcat_cutover c
WHERE
a.PCat <> c.Pcat
(where DB_ONE and DB_TWO are the 2 different db names)
I have one connection to DB_ONE... does this mean I cant access DB_TWO when using this connection? I want to try and avoid using a temporay table for storing DB_TWO's data in DB_ONE... Is this possible?
Thanks,
Andre
I have a single .bak file containing full backups of 20+ databases.
Whats the best way of creating and restoring these database onto an instance on a separate server.
note that this is to migrate the whole instance of sql server onto a newly build server.
Hi ...
I have two separete databases, what Ineed is that I want to make a single database of the two. Although all the table definitions of both the database are same.
I will be glad to know "how to make a single database from two" and i will be happy to see the query to insert the values from one database to another one.
Abdul Ghaffar
I'm building an app that will data stored in multiple databases. I have an endpoint with a single webmethod like so:
CREATE ENDPOINT [Rosters_Endpoint]
AUTHORIZATION [ADHEadministrator]
STATE=STARTED
AS HTTP (PATH=N'/SQL/Rosters', PORTS = (CLEAR), AUTHENTICATION = (DIGEST, NTLM, KERBEROS, INTEGRATED), SITE=N'10.0.1.116', CLEAR_PORT = 8080, COMPRESSION=DISABLED)
FOR SOAP (
WEBMETHOD 'GetACSStudents'( NAME=N'[fadb].[dbo].[GetACSStudents]'
, SCHEMA=DEFAULT
, FORMAT=ALL_RESULTS), BATCHES=DISABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]', SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'fadb', NAMESPACE=N'http://tempUri.org/', SCHEMA=STANDARD, CHARACTER_SET=XML)
Each webmethod will be accessed thru a multiview on the page.
It would seem to me that I can describe the webmethods using a fully qualified stored proc name and that wouldn't cause a big problem. But, my question is what does that DATABASE=N'fadb' clause do?
Does anybody see a problem with using a single endpoint to access multiple databases?
Any advice would be greatly appreciated as this is my first web app using this data access method.
Thanks.
Would this go a long way towards securing the data?
Database.Store(this)
Database.Delete(this)
Database.Load(this)
Where "this" is a single object mapped to a single stored procedure.
Two parameters are recommended: one to identify user, and one to specify an "action" or different SP.
Hi, I'm planning to develop a project on .NET using Sql server 2005. Just stuck at a point where I need to decide that Should I use 2 or 3 huge databases with a lot of tables and data into it or should i create a lot of databases which would contain small chunks of data. Please let me know which option would give me a better performance. Thanks in advance
God is great. We are not God. But we still can be great.
Hi,
I want to create two databases by restoring from a
single backup file in sql server. I am using 2005-sqlexpress .Is it possible?
Thanx in advance..
I have a backup that contains multiple databases. I am restoring it to a different server. How do I restore each database? Tried to restore under SQL Server Management Studio, but can only restore one of many from the backup (it appears it has only 1 logical file name in the backup) If you can refer me to a web page that would be great. I tried googling the topic but nothing seems to come up.
View 5 Replies View RelatedCan someone explain what does this mean? This is from http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
<quote>However, all these techniques for coordinating client redirection with a database mirroring have an important limitation. Database mirroring occurs only at the database level, not the server level. Be careful if your application relies on querying several databases on a server, or uses multi-part object names to query across several databases. When several databases reside on one server, and they are mirrored to a standby server, it is possible that one of several databases might fail over to the standby but the others remain on the original server. In that case, you might need one connection per database that you are querying, so that you do not attempt cross-database queries on a standby server where only one database is a principal and the remaining are mirrors.</quote>
Also I read somewhere that if one mirrored DB fails, all other mirrored DB should also be transferred to the mirror.
For example: Let's say Srv1 (principle) and Srv2 (mirror) and Srv3(witness) are SQL servers with only default instances. The SQL instance has 4 DBs (DB1, DB2, DB3, DB4) and all of them are mirrored to Srv2.
An ASP.NET application has four seperate connection strings like:
objConn1 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB1;Integrated Security=True;
objConn1.connect();
objConn2 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB2;Integrated Security=True;
objConn2.connect();
objConn3 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB3;Integrated Security=True;
objConn3.connect();
objConn4 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB4;Integrated Security=True;
objConn4.connect();
If DB2 failsover to Srv2 (mirror), why should all other DBs be failedover?
Thanks
i am having 2 sql servers one is web sql server server and other server is my local sql server. i am making web application through which i want to connect both sql servers at a time and by selecting data from web server i want to insert it into my local sql server both having same database on which i want to work , i want to know how it can be done
my idea as follows in web.config i specify 2 connection string and by selecting data from websql server database table i going to store it in dataset and then i return dataset to another function which connects to local sqlserver database.but database records are much large. can any body guide me .
i am going to place my web application on local server (C# asp.net 2.0) .
thank you,
i tried this code
c# file
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Uupdatedata();
}
public void Uupdatedata()
{
string constr1=ConfigurationSettings.AppSettings["abccon1"];
string constr2 = ConfigurationSettings.AppSettings["abccon2"];
System.Data.SqlClient.SqlConnection dbcon1 = new System.Data.SqlClient.SqlConnection(constr1);
dbcon1.Open();
System.Data.SqlClient.SqlConnection dbcon2 = new System.Data.SqlClient.SqlConnection(constr2);
dbcon2.Open();
SqlDataReader dr;
SqlCommand cmd = new SqlCommand("select * from Unit_Master",dbcon1);
dr = cmd.ExecuteReader();
while (dr.Read())
{
string Sql = "insert into Table1(Unit_Id,Unit_Desc) values(" + dr.GetInt32(0).ToString() + "," + dr.GetString(1) + ")";
SqlCommand cmd1=new SqlCommand (Sql,dbcon2);
//cmd1.EndExecuteNonQuery ();
}
//dr.Close();
//dbcon2.Close();
}
web.config<configuration>
<appSettings>
<add key="abccon1" value="Data Source=comp01;Initial Catalog=abc;User Id=sa ; Password=sa"/>
<add key="abccon2" value="Data Source=comp01;Initial Catalog=abctest;User Id=sa ; Password=sa"/>
</appSettings>
<connectionStrings/>
this 2 databases are from single my loacl server
it wont work it gives exception
please healp me.
I have to mirror 3 DBs on my sql server instance . I have restored there full backups and log backups on mirror.I have also created endpoints on principal and mirror using default ports 5022 and 5023.
know if i can use the same endpoint for the databases on principal instance ?
SQL Server 2012: Out of all the databases in the instance we have a requirement in which we need to maintain a high availability for the databases around (128). Our team believed log shipping will apt for this requirement but not sure on it's limitations, how many databases are allowed or supports this log shipping from a single instance.
View 7 Replies View RelatedI have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23
The results should be ordered by DateChangedOn.
Hi, I have compact sql databases which will be local on multiple users
devices. Due to space constraints, for one of the tables i have had to use
auto incrementing integer which works fine for the local database but i
would like to merge all of the users databases into a global database. The
table format can be seen below:
Code SnippetCREATE TABLE Players
(
ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
FirstName NVARCHAR(32),
LastName NVARCHAR(32)
);
CREATE TABLE Sessions
(
ID INTEGER NOT NULL IDENTITY,
PlayerID UNIQUEIDENTIFIER ,
SessionDateTime DATETIME,
CONSTRAINT pkSessions PRIMARY KEY (ID),
CONSTRAINT fkPlayerID FOREIGN KEY (PlayerID) REFERENCES Players(ID)
);
CREATE TABLE SessionDetail
(
SessionID INTEGER,
Time real,
Power real,
CONSTRAINT pkSessionDetail PRIMARY KEY (SessionID,StrokeTime),
CONSTRAINT fkSessionID FOREIGN KEY (SessionID) REFERENCES Sessions(ID)
);
The Players table will merge fine as GUIDs are used. However, how will the
sync capabilities of compact SQL handle the sessions table? When it pushes
the local data to the remote database will it change the Sessions.ID column
to a unique field (as no doubt lots of people will have 1, 2, 3 in their
local databases and the global database must have a unique ID), and then
transfer this changed ID back to the local database? Furthermore, if it
changes the Sessions.ID column during the merge it will also need to update
the SessionDetail.SessionID foreign key to maintain referential integrity,
is this also handled?
So my question is, how much of this sync and data merge is automated and are
there any good examples or pointers for my scenario? I cannot reasonably use
a GUID for Session.ID as there will be lots of SessionDetail entries and the
size would be far too much.
Many thanks,
Chris
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date
Code:
Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000
Should Appear
Code:
EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800
PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.
THANKS IN ADVANCE
When I proposed start to use SQL Server 2005 for new VS 2005 web sites, one of my co-workers responded that
we will update the old SQL Server 2000 databases to SQL Server 2005 when we are ready to use 2005 SQL Server.
Questions:
1. Any expected problems to upgrade old 2000 databases to new 2005 SQL Server?
2. I have installed both 2005/Management Studio Express and 2000/Enterprise Manager in my PC. Any expected
problems when running both 2000 and 2005 SQL Server at the same database server?
3. What is the best configuration for running SQL Server 2005 when we have old 2000 databases? Upgade or not upgrade?
TIA,
Jeffrey
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
I'm working to test out a recovery scenario involving a SQL 2008 R2 2 node cluster. I want to restore it to a standalone server. The nodes are VMware VMs using physical RDM for the shared storage. Using Veeam as the backup tool. Veeam doesn't work with physical RDM so I'm dumping the databases (all Simple) elsewhere to be picked up and replicated with Veeam. No problem there. For my DR I have a vanilla 2008 R2 server being replicated so that I can use it as a base to restore SQL. So far the testing has worked ok. What I'm trying to learn is the best way to restore the cluster to the standalone server and have it look the same to the affected app servers.
The cluster has several instances. In my testing, I've recreated the instances, but I don't think they show up as they do on the cluster. For example, if the Windows server name is SQL01, and I install an instance called SQL-APP, SELECT @@servername while connected to the instance still shows SQL01 but in production it shows SQL-APP. What do I need to do to get everything to match up? I suspect that, while I haven't seen any problems, there could be something I'm missing that would manifest itself later.
Also, in testing, am I better off to delete the cluster server account from AD and join the domain with the DR server under the same name? (this is an isolated bubble so no impact on production).For the purpose of this scenario, assume all other required servers (DC, DNS, application, etc.) are present in the test environment as they would be in production and nothing requires resources outside the bubble. The only item affected is the SQL cluster.
What is maximum number of databases on single SQL Server 2005?
View 3 Replies View RelatedWhile I was out of the office the Lan Team moved one of my SQL Server2000 servers to a new network domain. Since then the maintenance jobhas not ran.The error log for the SQL Agents has the message listed in the subjectline. I have not found any useful articles on the MS SQL Serversite. Anyone know what might be wrong and how to fix it.HTH -- Mark D Powell --
View 4 Replies View RelatedHi all
Which tool can I use for structure editing of SQL Server Compact 3.5 databases? I'm installed SQL Server Compact 3.5. I have SQL Server Management Studio Express which was installed with SQL Server 2005 Express. Unfortunately this SSMS can create and open only 3.1 databases.
This question is about SQL Server 2005:
I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn
How many database a single SQL server can manage. Assume that I have enough hard disk space. (eg. 25 MB DB can I have 500 databases). What should be accurate setting required for SQL server configuration like open database, objects, locks, connections etc.
Help me out with suggestions and proper source of Information for the above.
Thanks
Rajasekar
How can I install SQL Server in single user mode? I want this because I want only one user to work on the application.
Thanks in advance
Regards,
Sushma
I need to backup a single table in SQL server. I use SQL Server 2008 r2 and this is new for me
View 2 Replies View RelatedI need out put in single row for below 2 queries using joins
SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB],
CPU_Count AS NumberofCPU
FROM [master].[sys].[dm_os_sys_memory]
CROSS JOIN sys.dm_os_sys_info
[code]....
Hello FacultiesWhen doing recovery after loss of data files, one would like tostart SQL Server in single user mode. Is this possible?Thanks in advanceDebashish
View 1 Replies View RelatedI get an error in my ASP.Net application which states:
"The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor."
I checked out the database properties, it shows that SQL is set for 'MultiUser' mode. What else do I need to check and how can I fix this?