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
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.
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.
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 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 ?
I 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 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
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
I am trying to find out what records on in one DB and not the other. This SQL statement works find if the DBs are on the same Server. If I replace on the DBs with a linked DB (on another server) I get the following error.
SELECT *
FROM [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price]
WHERE NOT EXISTS
(SELECT [LocalDB].[dbo].[Sales Price].*
FROM [LocalDB].[dbo].[Sales Price]
WHERE ([LocalDB].[dbo].[Sales Price].[Item No_] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Item No_]) AND
([LocalDB].[dbo].[Sales Price].[Unit of Measure Code] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Unit of Measure Code]) )
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Item No_" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Unit of Measure Code" could not be bound.
Does anyone know how to configure a mirrored pair as a linked server on a 3rd instance?
Say I have a mirrored database on two servers: PRIMARY and SECONDARY.
I want to create a linked server on a 3rd machine that allows me to access the database on the mirrored pair.
This is what I'm using:
EXEC master.dbo.sp_addlinkedserver
@server = N'MIRROR',
@srvproduct=N'',
@provider=N'SQLNCLI',
@provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;'
select count (*) from mirror.pubs.dbo.authors
and it works fine if the database on PRIMARY is alive. however when the mirror has failed over to SECONDARY and PRIMARY is no longer available, I get the following when I try to query the database via the linked server:
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.
As far as I can tell, it doesn't try to contact SECONDARY at all. It seems like SQL Server is ignoring the FailoverPartner attribute.
If I switch PRIMARY and SECONDARY in the connection string (ie @provstr=N'Server=SECONDARY;FailoverPartner=PRIMARY;') then it works when SECONDARY is online, but not when the mirror has failed back to PRIMARY.
Any ideas?
Piers.
Does anyone know how to configure a mirrored pair as a linked server on a 3rd instance?
Say I have a mirrored database on two servers: PRIMARY and SECONDARY.
I want to create a linked server on a 3rd machine that allows me to access the database on the mirrored pair.
This is what I'm using:
EXEC master.dbo.sp_addlinkedserver
@server = N'MIRROR',
@srvproduct=N'',
@provider=N'SQLNCLI',
@provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;'
select count (*) from mirror.pubs.dbo.authors
and it works fine if the database on PRIMARY is alive. however when the mirror has failed over to SECONDARY and PRIMARY is no longer available, I get the following when I try to query the database via the linked server:
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.
As far as I can tell, it doesn't try to contact SECONDARY at all. If I switch PRIMARY and SECONDARY in the connection string (ie @provstr=N'Server=SECONDARY;FailoverPartner=PRIMARY;') then it works when SECONDARY is online, but not when the mirror has failed back to PRIMARY.
Any ideas?
Piers.
New to MSSQL
Management is getting frustrated with the increase in number of NT servers for MSSQL and Vendor packages. Currently we purchase a box for each app. We would like to go to one or two MSSQL servers running multiple application database's (based on size etc) . How are you handling this? Do you do this for test only or both test and production? Any gotcha's. Any help will be appreciated.
Thanks
john
Here is my SQL SEVER QUERY.
Need to change vertical execution of query into horizontal in sql server
SELECT TL.TERMINAL_ID,T.IP_ADDRESS, T.LOGICAL_CONNECT_STATUS, SI.SCHEDULER_ID,
SI.INSTRUCTION, SI.GROUP_ID, SI.MAX_READ_RETRIES, SI.DATA_CHAR, SI.SCHEDULE_TYPE,SI.FILEPATH_FLAG,
T.STATION_NAME,T.BANK_ID FROM SCHEDULERINFO SI WITH (NOLOCK)
INNER JOIN TERMINALGROUP TG WITH (NOLOCK) ON SI.GROUP_ID = TG.GROUP_ID INNER JOIN TERMINALGROUPLINK TL WITH (NOLOCK) ON TG.GROUP_ID = TL.GROUP_ID
[Code] ....
Output of the query is:
SCHEDULER_ID
TERMINAL_ID
IP_ADDRESS
LOGICAL_CONNECT_STATUS
INSTRUCTION
[Code] ....
Desire output required:
SCHEDULER_ID
TERMINAL_ID
IP_ADDRESS
LOGICAL_CONNECT_STATUS
INSTRUCTION
[Code] ....
Hello,
we are thinking about consolidating our Vendor Sqlserver needs into single big Sqlserver box.
We currently have dedicated boxs for each vendor who requires a Sqlserver Dbs, for example Siemen Sqlserver Dbs are on one box, Clintrac Dbs are on one bx, etc.
It looks like we can save some money on HW/SW and maintenance cost by the consolidation. That's assuming that the Dbs can get along fine.
I was wondering if folks on this group can share their experience or thoughts about above setting.
Thanks,
yi
I need the requirements of merging multiple rows of same ID as single row.
My Table Data:
IDLanguage1Language2Language3Language4
1001NULL JAPANESENULL NULL
1001SPANISH NULL NULL NULL
1001NULL NULL NULL ENGLISH
1001NULL NULL RUSSIAN NULL
Required Output Should be,
IDLanguage1Language2Language3Language4
1001SPANISH JAPANESERUSSIAN ENGLISH
How to achieve this output. Tried grouping but its not working also producing the same result.
How to combine multiple rows to single rows for the below sql query.
SELECT dbo.AccessLog.RCDID, dbo.AccessLog.EMPLOYEEID, dbo.AccessLog.LOGDATE, LEFT(dbo.AccessLog.LOGTIME, 5) AS LOGTIME,
dbo.AccessLog.INOUT
FROM dbo.AccessLog LEFT OUTER JOIN
dbo.LogType ON dbo.AccessLog.INOUT = dbo.LogType.INOUT LEFT OUTER JOIN
dbo.viwEmployee ON dbo.AccessLog.EMPLOYEEID = dbo.viwEmployee.Employee_ID
WHERE dbo.AccessLog.EMPLOYEEID='10763' AND (dbo.AccessLog.LOGDATE BETWEEN '01/04/2015' AND '01/04/2015')
ORDER BY dbo.AccessLog.EMPLOYEEID
The reult for the above query is:
RCDID | EmployeeID | LOGDATE | LOGTIME | INOUT
1 10763 01/04/2015 08:00 0
1 10763 01/04/2015 19:46 1
I need the result like the below
RCDID | EmployeeID | LOGDATE | IN | OUT
1 10763 01/04/2015 08:00 19:46
Two Questions.
1.) I have developed some SSIS packages that are currently out in production. We are on to the next phase of development and enhancments to the packages are being developed as a phase 2 release. I can push these changes to a QA setup for testing. But I also need to do fixes to the existing packages out in production and run them through testing. Unfortunatly I do not have an extra server to have a QA and a QA Current Production setup for seperate testing. So is there any way to run two versions of the same package on one (the same) server? I presently store the packages in the MSDB. Can I have or specify which version of package to run?
2.) Additionally, can you run multiple configurations for a single version of a package but only call one of them? For example can I create two configurations and then specify which one to use when calling the package to run? So that I could have code pass which configuration to load which would change connection strings and such. Any examples would be awesome.
Thanks.
We are in the design phase of a project where it is estimated there will be 150 SQL Server databases - each one housing similar information on each of 150 client sites. The client needs a reporting tool to analyze and make comparisons across databases. We were thinking about a using a data warehouse. Does SQL Server have a native solution to this type of problem ? Or is there another, better way to approach this ?
View 1 Replies View RelatedHi please could someone give me a explination of how SQLserver deals with multiple databases, We have multiple website that are all run from the same server all from SQLserver. Now we have a couple of databases which are very busy and have a lot of people constantly accessing them and then we have quite a few with much less traffic.
How would these databases effect each other? Would the busy database, mean that the many small databases will all be slowed dramtically?
Everything is okay at the moment but we are considering putting another fairly large database on the server and am worried about it having a sever effect on the smaller databases. Cheers Ed
I have a situation where I need to do some reporting for simular asset records but the data is listed on different database from the same server.
I was curious as to the way to write the queries that I will need.
Lets say I have 3 databases and I am looking for a asset of some type and the ID is a serial number.
The serial will be on the main table from each database. Is there a way I can set a relation from a view between these records?
I.E.:
1: Database A -DSNA_tblMaiin - Serialnumber
2: Database B -DSNB_tblMaiin - Serialnumber
3: Database C -DSNC_tblMaiin - Serialnumber
I believe that this is a dot notation question.
Thanks in advance,
Gene
Hi,
I have to manage many SQL Server databases on several servers. How can I manage the jobs, backups and the space on the disc without going to each and every server and database and job? Is there any script to run this? It will be very helpful if you can provide me the sample script or point me to any web site where I can get the info/script for this. Thanks in advance...
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 need to create a linked server that can access more than one database.
I assume, the only wat this can be done by creating two separate links from the local server using Microsoft OLE DB Provider for SQL server. But the
problem is the Server Name. I cannot use the same servername twice and
if I use a name that is not an exisiting SQL server name I get an error
that "server not found".
So, how do I addlink the same server a mulitple times to access different databases in the server?
Thanks.
Ranjit
Can we assign two logins for two different databases on target server. For example, I would like to create a Linked Server between ServerA and ServerB.
ServerB has two databases on it and both using different logins to access. Say DB1 has Login1 and DB2 has Login2
So can I create something like this:
EXEC master.dbo.sp_addlinkedserver @server = N'db_link', @srvproduct=N'SQLServer OLEDB Provider', @provider=N'SQLNCLI', @datasrc=N'ServerB'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'db_link',@useself=N'False',@locallogin=N'Login',@rmtuser=N'Login1',@rmtpassword='Password1'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'db_link',@useself=N'False',@locallogin=N'login',@rmtuser=N'Login2',@rmtpassword='Password2'
Am I able to access two different databases using this same linked server. I tried but with no success.
I have 2 tables People and Scores. A person might have 1-5 scores (unknown at time of Query). I would like to query the two tables into a results table and if person does not have a record the score will be zero. Scores also have a test number so you know which score it is. I can get it done with Stored Proc but I have to use Temp tables and then put the temp tables together.
People
Name ID
Tom5
Dick2
Harry3
Larry4
Curly1
Scores
PrimaryKeyPeopleIDScoreTestNumber
12801
[code]....
Results
PrimaryKeyPeopleIdScore1Score2Score3Score4Score5Name
1110090807090Curly
22800000Dick
33909010000Harry
44507090900Larry
559070000Tom
I have resulting rows from a query similar to the following:
The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:
SELECT
[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,
[Code] ....
I basically want to merge the like Employee ID's together into a single row like the following:
I know I have done this before and it is probably pretty simple.
I am looking a script which allow me add single coilumn to multiple table of my database.
For Example :-
I am having 4 table
1-Emp , 2-Dept , 3-Location , 4-Salary like this I have around 100 of table
Now I want to run below command to add column Rowchecksum in all table where table name start with Archivebbx keywords.
Alter table EMP
Add Rowchecksum varbinary(8000)
I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.
For Eg:
Description
APPEARANCE UNIT
BDV KV
DENSITY KG/L
I want to create multiple partition schema on a single table.
For example - i need to create partition base on region id and Territory Id.