October 10, 2012 at 8:07 am
I just had a question of my boss, for one of our clients we have a stored procedure that has to dynamically connect to a database table.
Both the database and the table are dynamic and have to be entered by the user.
Can you do this without the need of making a connection to that server.
October 10, 2012 at 8:17 am
maybe, with openrowset, and assuming there is connectivity between the two SQL servers:
here's a couple basic examples:
SELECT *
FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who')
SELECT *
FROM OPENROWSET('SQLOLEDB','Server=DEV223;Trusted_Connection=No;UID=Noobie;Pwd=NotARealPassword;Database=Master',
'Set FmtOnly OFF;EXEC dbo.sp_Who')
Lowell
October 10, 2012 at 8:29 am
Resender (10/10/2012)
I just had a question of my boss, for one of our clients we have a stored procedure that has to dynamically connect to a database table.Both the database and the table are dynamic and have to be entered by the user.
Can you do this without the need of making a connection to that server.
You cannot access any database or database object without connecting to the server where it is located.
October 10, 2012 at 8:39 am
Ok, that's basically what I told him, but he wanted me to look it up anyway.
He asked because you can do it in Oracle, but that is cause in Oracle a database is its own server
Txn for the answers
October 10, 2012 at 8:44 am
Resender (10/10/2012)
...you can do it in Oracle, but that is cause in Oracle a database is its own server...
I don't think that is true either.
Any true RDBMS does not allow access to databases or their objects without making a connection to the database server.
October 10, 2012 at 8:52 am
Resender (10/10/2012)
Ok, that's basically what I told him, but he wanted me to look it up anyway.He asked because you can do it in Oracle, but that is cause in Oracle a database is its own server
Txn for the answers
You have the same database, [OrderProcessing], on your dev, test and production servers, and you want to truncate the customer table in dev.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 10, 2012 at 8:54 am
Yes, but I believe that in Oracle you don't have a single rdbms to manage multiple database, I could be wrong in this aspect been at least 18 months since I dabbled with Oracle.
October 10, 2012 at 8:59 am
ChrisM@home (10/10/2012)
You have the same database, [OrderProcessing], on your dev, test and production servers, and you want to truncate the customer table in dev.
That's kind off what happens, basically the situation is like this:
Server = Factory
Database = Machine
Tables = Sensors on that machine
The idea is to get all the data of a particular sensor of a particular machine without knowing in which factory the machine is located.
October 10, 2012 at 9:12 am
Resender (10/10/2012)
Yes, but I believe that in Oracle you don't have a single rdbms to manage multiple database, I could be wrong in this aspect been at least 18 months since I dabbled with Oracle.
It is normal in Oracle to have one database for each instance, but you can have more than one instance on a server. It is usual to implement separate schemas in an Oracle database, while in SQL Server the same application would be implemented as separate databases.
However, even that is changing and will closer to the way SQL Server does things:
How Oracle Will Allow Multiple Databases to Work
http://www.cio.in/news/how-oracle-will-allow-multiple-databases-work-312132012
October 10, 2012 at 9:32 am
If I may ask - why is there a hangup about making a connection to the server?
If you can dynamically build the connection string to multiple databases, you can dynamically build a connection string to multiple databases on multiple servers. Not sure where the objection actually is.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 11, 2012 at 1:20 am
Matt Miller (#4) (10/10/2012)
If I may ask - why is there a hangup about making a connection to the server?If you can dynamically build the connection string to multiple databases, you can dynamically build a connection string to multiple databases on multiple servers. Not sure where the objection actually is.
The objection being that the same user exists on those databases but with a diffrent ID and their are no plans to make any user accounts across the severs
I'm not directly involved in this project as its for a different client that I'm currently working for
October 11, 2012 at 10:22 am
Resender (10/11/2012)
Matt Miller (#4) (10/10/2012)
If I may ask - why is there a hangup about making a connection to the server?If you can dynamically build the connection string to multiple databases, you can dynamically build a connection string to multiple databases on multiple servers. Not sure where the objection actually is.
The objection being that the same user exists on those databases but with a diffrent ID and their are no plans to make any user accounts across the severs
I'm not directly involved in this project as its for a different client that I'm currently working for
Which changes the question from "how do I conect to any number of DB's to pull tables" to "how do I connect to any number of DB's regardless of what the underlying user has access to". Meaning - it's a security problem, not a connection problem.
Without being able to use a service account model, you're going to be in a world of hurt. Within the constraints of SQL Server, you'd have to register those users on the server and to the DB's they can access, OR you'd have to create a "delegated access model", with something verifying that the user can access this application and then use a service account to actually access it on the user's behalf.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 12, 2012 at 11:01 am
Technically, with contained databases in 2012, you are bypassing connections through the server as you are authenticated directly by the user database.
Under Oracle an instance is a database, its an instance of a database, which then splits down into tablespaces.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply