Cross Server Database Dumps
Oct 5, 2004I am attempting to remember the string to dump/restore a database to a different physical server.
I have the database on Server 1 and want to back it up to server 2. Can anyone refresh my memory?
Thanks
ADVERTISEMENT
STACK DUMPS AND SYMPTOM DUMPS
Apr 26, 1999Can anyone tell me where I can find information about why sql server might be executing a stack and/or symptom dump?
That is, what are some of the conditions that might cause this. Every time I try to DBCC DBREINDEX on a certain table, stack
and symptom dumps occur and SQL Server remains incommunicado (can't connect, even locally) until I restart it. I'm going to try to fix the table by using
rename table, create new table (and dependent objects) , transfer data, and toast the original table. No problem, I've done that
before with smashing success. But, if this solves my problem, I'll still be no wiser about what it was that caused the problem in
the first place. Any suggestions, questions, comments, or even jeers would be appreciated.
Thanks,
Chris
Cross Server Database Dump
Oct 5, 2004I am attempting to remember the string to dump/restore a database to a different physical server.
I have the database on Server 1 and want to back it up to server 2. Can anyone refresh my memory?
Thanks
SQL Server 2014 :: Cross-database Function Call
Jul 2, 2015I have 2 databases on the same server
One has a function, the other has the tables and views
using dba
select dbo.function(t.column) as x from dbb.dbo.table as t
gives m an invalid object name of dbo.table
using dba
select top 1 * from dbb.dbo.table works fine.
also if i create the function on the same db it works.
Can I Read Transaction Dumps (*.TRN) File In SQL Server 7.0
Jul 28, 2000our users believe that we lost some valid data, but no one knows who did it,
I thought I can find it from the transaction dumps I take every hour
so ,Can I read Transaction Dumps (*.TRN) file in SQL server 7.0 or Can I get this information through other means.
Thanks for your help
DP
Multiple Database / Cross Database Referencial Integrity (foreign Keys)
Feb 12, 2008Using SQL Server Express 2005, I have two databases. AppDB - The main application database.GeoDB - A somewhat static ZIP code / states / other geographic stuff databaseI need to have some foreign key columns in tables in AppDB reference columns in the GeoDB database tables. Eventually other application database besides AppDB will be doing the same thing in our infrastructure. After googling and reading for days, here is what I
think I know:You cannot create foreign keys that reference tables in another database in SQL Server.You
cannot create foreign keys that reference columns in a view, and you definitely cannot make an index on a view that has base tables in another database.You can create a trigger that references tables in another database, but this can be flaky? (nested/recursive trigger problem).SQLServer
2005 supports multiple schemas within the same database. Maybe I should logically separate my databases this way? Seems like it would be a tough solution to manage since I already have some databases live in production that will eventually use this 'static' GeoDB. Also, seems like it
wouldn't be as portable as keeping the GeoDB info in its own database,
but maybe I'm being too software engineer-ish here - afraid of low
cohesion, high coupling.I will greatly appreciate any advice I can get, or any more options I am missing. Thanks,Adam Nofsingerucnmedia.com
Cross Database
May 23, 2005" I have two sql server2000 database named db1 and db2.
i have a user named 'user1' who has permission in both database.I have used a 'Select * from tableOne'in db1 when i have this table 'tableOne'in db1.
now this table was droped and created in db2.
what i need is i should log in to db1 and access the same select statement which is there in application used by my clients.
i have created a view in db1 with the same name as
'create view tableOne as select * from db2..tableOne'
now i can access.
Is there some othere way with out creating view?
Cross-database References
Oct 22, 2007Hi,
I'm doing a web application that will get some information from an ERP.
At this moment I have 2 databases:
1) The aspnetdb, where I have the tables for Merbership and Role
2) The ERP database
I need to put my web application tables on one of these two DB's. This tables will reference the users from the membership and some products from the ERP DB.
I will store products requests that will store both UserID (from aspnetdb) and ProductID (from ERP DB). I'm thinking to put these tables on the aspnetdb, so that all web application tables stick together. But, I will loose tha ability to make joins with the ERP database, right?
Do you think this will work? Can someone make some comments about this situation, and give me some tips?
Thank you!
Cross Database Updates?
May 8, 2008I am interested in adding a new row to a table 'Table05' that exists in a SQL Server 2005 database whenever a table 'Table00' in another SQL Server 2000 database has a row added to it. Can someone tell me a way to implement the above solution?
Cross-database Query From ASP.NET
Feb 7, 2005How do you write a SQL SELECT statement for a cross-database query in ASP.NET (ADO.NET). I understand the server.database.owner.table structure, but the command runs under a connection. How do I run a query under two connections?
View 1 Replies View RelatedCross Database Operations
May 6, 2008Hi,
I'm trying to get data from several databses of different kinds (Sql Server and Oracle) and from different servers - is that possible ?
If not - is there any way to copy some data from one database to another - like tables and their content (or partial content) ?
I tried to use "select name from <database name>..sysobjects where xtype = 'U'" but I get security error.
thanks,
Naama
Cross Database Queries
Apr 27, 2004i know that db.owner.tablename works in the query analyzer, but what i really appreciate from anyone is how to apply this in vb6 code since the recordset is opened only from one db using the following syntax:
rs.open "select ...", dbname, ..., adopendynamic, adlockoptimistic ...
thanks
Cross Database Manipulation
May 4, 2004Hi everyone!
I recently found out that using ADO to connect to the SQL server without mentionning the data source; then, by sending a query with the following syntax: dbname.owner.tablename.columnname... an implicit connection to the data source (database) is performed automatically. This way of connecting allowed me to manipulate accross two or more databases thing that is necessary in my project.
My question is:
1- Do anyone have any bad experience and/or negative consequence to such connectivity (memory consumption, unexpected disconnection, ...)? Please consider a very high frequency of manipulation since we are dealing here with a 24/24 hour operational site.
2- Are there any alternative solutions for cross database manipulation (select, insert, update, delete)?
Thanks
Cross Database Dependencies
Jan 3, 2008As far as I know SQL Server 2005 lists only dependencies within the same database. In many cases though there are objects in one database depending on objects in a different database on the same server. I unsuccessfully looked for a tool to list those and I ended up creating a script to do the job instead. Any improvements are appreciated, e.g. the script uses syscomments which may no longer be available in the next SQL Server version - I have not found a replacement sys.XXX table yet. Please note that you need to use a connection with access to all the SQL databases on a server and that it may take a while to run the script below if you have many databases with numerous objects in there. On the other hand these are exactly the cases when you need to know which of the thousands of objects are dependent on the table or view you are looking at.
P.S. Does anybody know where the extended properties get stored (hint - not in syscomments)? I was looking for those too, since often I document in the description of a field that it links to "DatabaseABC.dbo.TableXYZ.Field1"
Plamen Kouzov
==========
declare @searchstring varchar(50)
declare @dbid int
declare @DBName varchar(1024)
declare @command varchar(1024)
declare @ServerDBs table([dbid] smallint, DBName varchar(1024))
select @searchstring = 'DatabaseABC.dbo.TableXYZ'
insert into @ServerDBs ([dbid], DBName)
select [dbid], [name]
from master.dbo.sysdatabases
where [name] not in ('master','tempdb','model','msdb','pubs','Northwind')
create table #ObjectsFound (DBName varchar(500), ObjectName varchar(500))
while (select count(*) from @ServerDBs)>0
BEGIN
set @dbid = (select TOP 1 dbid from @ServerDBs)
set @DBName = (select DBName from @ServerDBs where dbid = @dbid)
set @command = 'use ' + @DBName + '; insert into #ObjectsFound(DBName, ObjectName) select ''' + @DBName + ''', [name] from sysobjects where id in (select [id] from syscomments where text like ''%' + @searchstring + '%'') order by 1'
exec sp_sqlexec @command
delete from @ServerDBs where dbid = @dbid
END
select * from #ObjectsFound order by 1,2
drop table #ObjectsFound
Cross Geo Database Mirroring
Feb 26, 2008I'm not personally a DBA or claim to be a database expert, instead I am a software architect who works closely with database architects and DBA's. I'm presently architecting a system where we will have a smart client used in both Europe and the United States from the same database instance.
The system is to be used for performance testing of devices and then the results will be available to management through the same smart client used by test engineers etc. The first release is operational in the United States today with Poland coming online shortly.
Today we are weighing our options between having cross-geo database mirroring or merely having the database server in the United States with application server level caching implemented in Poland and the United States.
The .net framework's Windows Communication Foundation is used with a netTcpBinding and the service layer is asynchronous with the reliableSession option enabled.
Does anyone have any feedback on whether you would go with cross-geo database mirroring or would you use local application servers with possibly some caching capabilities at that layer? Please also describe the benefits and any risks associated with each also...
Thanks in advance,
Doug Holland
Cross Database Query
Feb 18, 2008I have Two databases e.g. PDC,STB(has a table named "user" and fields of the table is "name","time_of_login").i want to create a stored procedure in PDC database which will insert data into STB's "user" table.
OR
how to run query from QueryAnalyzer by setting database from combobox at top as PDC and run select * from STB.user;
AS
in oracle we can connect via one user
and can select the tables of another user.
like
connect internal/oracle
select * from scott.emp;
How Can I Create A Cross-database Dependencies?
Nov 4, 2002I have a table EMPLOYEE in database PERSONNEL, and a table JOBS in database JOBMAN, I want to create a dependencies on the primary key EMP_NUM in table EMPLOYEE and the foreign key EMP_NUM in table JOBS, how can I do it in Enterprise Manager or any other tools?
View 5 Replies View RelatedCross-database Dependency Check
Oct 28, 2004Is there anyway to check for cross-database dependencies?
For example:
I have a stored proc in the Pubs database that contains a query that references a table in the Northwind database (as well as reference a table in the Pubs database.) I'm having no problems finding the depencency for the Pubs table. I can't seem to find the dependency for the Northwind table.
Any help would be appreciated.
Thanks in advance.
Scott
Cross Database Ownership Chaining
Oct 5, 2006I saw this option in SSMS under "Servers, Property, Security tab". I looked at BOL but I still don't understand what it is or what it is used. Any help appreciated.
TIA,
Barkingdog
Cross Database Referencial Integrity
Feb 12, 2008Using SQL Server 2005, I have two databases.
AppDB - The main application database.
GeoDB - A somewhat static ZIP code / states / other geographic stuff database
I need to have some foreign key columns in tables in AppDB reference columns in the GeoDB database tables. Eventually other application database besides AppDB will be doing the same thing in our infrastructure. After googling and reading for days, here is what I think I know:
You cannot create foreign keys that reference tables in another database in SQL Server.
You cannot create foreign keys that reference columns in a view, and you definitely cannot make an index on a view that has base tables in another database.
You can create a trigger that references tables in another database, but this can be flaky? (nested/recursive trigger problem).
SQLServer2005 supports multiple schemas within the same database. Maybe I should logically separate my databases this way? Seems like it would be a tough solution to manage since I already have some databases live in production that will eventually use this 'static' GeoDB. Also, seems like itwouldn't be as portable as keeping the GeoDB info in its own database, but maybe I'm being too software engineer-ish here - afraid of low cohesion, high coupling.
I will greatly appreciate any advice I can get, any links to articles, or any more options I am missing.
Thanks,Adam Nofsinger
ucnmedia.com
Cross Database Ownership Chaining
Mar 10, 2008i have a database with cross database ownership chaining enabled. data base was detached and reatached as a result owner changed from sa to account that was used during reataching. will this affect chaining?
View 1 Replies View RelatedCross Database Activation Security
May 25, 2006I have two databases Basket_ODS and Intelligence_ODS.
I created a user in the Basket_ODS and Intelligence_ODS databases as follows:
USE Basket_ods
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*******'
CREATE USER BasketServiceUser WITHOUT LOGIN
ALTER AUTHORIZATION ON SERVICE::[Order Send] TO BasketServiceUser
GRANT CONTROL ON SERVICE::[Order Send]
TO BasketServiceUser
CREATE CERTIFICATE BasketServiceCertPriv
AUTHORIZATION BasketServiceUser
WITH SUBJECT = 'ForBasketService'
BACKUP CERTIFICATE BasketServiceCertPriv
TO FILE = 'BasketServiceCertPub'
In the other database...
I created the following:
USE Intelligence_ODS
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '************
USE Intelligence_ODS
GO
CREATE USER BasketServiceUser WITHOUT LOGIN
CREATE CERTIFICATE BasketServiceCertPub
AUTHORIZATION BasketServiceUser
FROM FILE = 'BasketServiceCertPub'
My Queue is in BASKET_ODS and is set up as:
ALTER QUEUE ODS.[Order Process Queue] WITH
ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = ODS.ProcessOrderQueue,
MAX_QUEUE_READERS = 4,
EXECUTE AS 'BasketServiceUser'
)
I have performed the following grants in Basket_ODS
grant execute on ODS.ProcessOrderQueue to BasketServiceUser
ProcessOrderQueue calls [ODS].[MoveOrderTotals_Core] in the Intelligence_ODS database.
grant execute on [ODS].[MoveOrderTotals_Core] to BasketServiceUser
ProcessOrderQueue proc is set as follows:
ALTER procedure [ODS].[ProcessOrderQueue]
WITH EXECUTE AS 'BasketServiceUser'
[ODS].[MoveOrderTotals_Core] is set up as follows:
when I run ProcessOrderQueue I get an error message:
ALTER procedure [ODS].[MoveOrderTotals_Core](@Orderid uniqueidentifier)
with execute as 'BasketServiceUser'
I just don't understand when I run ProcessOrderQueue I get the following error message (when database trust is turned off)
The server principal "sa" is not able to access the database "Intelligence_ODS" under the current security context.
Can you help me figure out what I'm doing wrong. I've spent so much time on this security stuff. Is there another way to do this that is more straight forward without using database trust?
Mirroring And Cross Database Queries
Feb 4, 2008This may be a newbie question but...
Say for example DatabaseA has failed over to the mirror. Then you call a stored procedure in that database that queries a table on DatabaseB that is on the principle (has not failed). What happens? Is mirroring smart enough to know that? If not, are there workarounds? Perphaps manually failing over the other databases?
Thanks
Workaround For Cross Database Queries
Feb 6, 2007Hi all,
Could you please suggest workaround for systems that have cross database queries and want to use mirroring.
Thanks,
Avi
How Efficient Is Cross Database Selects
May 23, 2008
Hello,
I want to know how efficient are cross database selects such as
select column from data.dbo.table;
The reason I'm inquiring is because I want to design a database whereas I'm tightly coupling the tables in several databases that are all referencing each other in some way shape or form.
If it is not too efficient or if you know of a way to make these cross database selects more efficient please offer the suggestion.
Thank,
Sharp_At_C
Cross Database Query Performance
Feb 1, 2008What is the relative performance cost of peforming joins, inserts across databases as opposed to performing them on tables within the same physical database? I can't find much on this at all..
Thanks
Cross Database Query Performance
Feb 17, 2008Hello All,
I have an issue where my SQL query is taking an exceedingly long time to execute. It joins tables in 2 different databases. When I import the tables from both my databases into a single database and execute the query, it executes instantly!
I had a look at the execution plans of the same query when executed within a single database and the query when executed with tables across databases and there were differences in the execution plan. The single database query had parallelism whereas the query for cross database joins didnt have any parallelism.
Both the databases are in the same server.
I havent seen a case where cross database joins impact query performance so much. Any pointers?
Thanks
Arun
Application Roles For Cross-Database Joins
Aug 25, 2005I have an application that segregates data into two differentdatabases. Database A has stored procs that perform joins betweentables in database A and database B. I am thinking that I have reachedthe limits of Application Roles, but correct me if I am wrong.My application creates a connection to database A as 'testuser' withread only access, then executes sp_setapprole to gain read writepermissions. Even then the only way 'testuser' can get data out of thedatabases is via stored procs or views, no access to tables directly.Anyone know of a solution? Here is the error I get:Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationInfo, Line38Server user 'testuser' is not a valid user in database 'DatabaseB'The system user is in fact in database A and B.thanksJason Schaitel
View 4 Replies View RelatedConfusing Cross-Database Permissions Issue
Jul 25, 2007We're trying to follow the principle of least privilege here in setting up a user account for our website to use to access SQL Server 2005, but we're having a nightmarish time getting it to work.
The issue seems to be trying to get a limited access user account the ability to cross databases.
Here's the situation:
We have a User [WebUser] that we want to grant access to the database. This account has a login [WebUser] that has username=WebUser and password=ALongPassword.
This user only calls stored procedures in the database [WebData].
However, some of the stored procedures in [WebData] call stored procedures in the database [dbutil].
One of the stored procedures in [dbutil] inserts records into a table in a third database [dbutil_temp].[DebugLog].
This all works out great from my development account using Windows Authentication.
But as you might guess, if I do something like "EXECUTE AS [WebUser]" and run the same procedure on [WebData] things fall apart quickly. I've looked online regarding cross-database ownership chaining, but quite frankly, the whole users/logins/roles/schemas security model is confusing, and I'm getting nowhere fast on my own.
We really only want [WebUser] to have CONNECT and EXECUTE permissions on the primary [WebData] database, but it seems like we've got to do a lot more than that to get this to work.
I'd appreciate any help...
Transact SQL :: Bug In REVERT During Cross Database Call
Jul 28, 2015I have noticed rather strange behaviour of EXECUTE AS and REVERT sequence during the cross database calls which appear to be a bug. I tested this issue on developer edition of SQL Server 2012
Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
May 4 2015 19:11:32
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
This issue causes problems in SSISDB where similar piece of code appears in [catalog].[start_execution] and some other scripts in the [internal] schema. This was previous discussed in [URL] The following script illustrates the issue:
USE [master]
GO
CREATE DATABASE [Test1]
GO
CREATE DATABASE [Test2]
GO
-- Set Database to Trustworthy to allow cross database connection
ALTER DATABASE [Test1] SET TRUSTWORTHY ON;
GO
ALTER DATABASE [Test2] SET TRUSTWORTHY ON;
GO
USE [Test2]
GO
CREATE PROCEDURE [dbo].[TestContext]
AS
SELECT 'EXECUTION CONTEXT BEFORE EXECUTE AS CALLER', SUSER_NAME(), USER_NAME();
[code]....
Need Of SQL Dumps
Feb 13, 2008HiIam new to this groupSO can any one provide me with dumps of 1z0-007regdsAJax
View 4 Replies View RelatedUser Define Roles And Cross Database Queries
May 28, 2003We maintain a few applications that query multiple databases on our server. We also have groups of users that multitask using different applications.
I've always created a Role in each database for every application.
Wondering if someone is in two Roles at once will we have security conflicts?
How To Use Triggers To Enforce Cross-database Referential Integrity?
Aug 1, 2001Can someone give me an example on how to enforce cross-database referential integrity with triggers in SQL Server 2000?
Thanks,
Joel