Linking between two databases

Is it posable to link between two diferent Databases? I am putting membership on my visual studios website which by default builds it's own database. I am then building a database for our products and services. I don't want to have to recreate all the client inforation that I already have in the default member database. But I don't think I want to put all the other info in the membershipp database.

Thanks for any help and advice

Ed

Hi,

you can either access your database by using three part names:

SELECT * FROM Database.Schema.ObjectName [Schema is called owner in SQL Server 2000]

or (but this implies the above one) creating view based on the table and syntax information above.

You have to keep in mind that you have to deal with the security in any way. There is a feature called crossdatabase ownership chain which would apply if activated to grant the user the rights in the destination database if the owner is the same across the databases and he has access in the current database to the object (like a view).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

servers and performance

Hi,
I have a client that he needs to configure a linked server
on a server cluster with SQL Server 2000, windows 2000,
two instances: one default and one named instance.
Linked Server should be configured in the named instance
calling a database from default instance
There is any problem about performance using linked server
in that condition?
Thank's for information
I was searching in Microsoft pages and I didn't find
information over that topicsThere are no performance issues specific to that configuration. There is
the general consideration that remote queries use more resources and
encounter greater latency than local queries, so if a high percentage of
requests involve both servers then you could experience poor performance.
There is no specific guidance on when you would see a problem, but absent
real data I always use 80/20 as a rule of thumb. That is, 80% of the
requests should be completely satisfied locally and no more than 20% should
require access to the linked server. I use 90/10 when I need to make a more
conservative estimate.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"ruth G" <caubd@.isa.com.co> wrote in message
news:007301c36801$f96d1f00$a401280a@.phx.gbl...
> Hi,
> I have a client that he needs to configure a linked server
> on a server cluster with SQL Server 2000, windows 2000,
> two instances: one default and one named instance.
> Linked Server should be configured in the named instance
> calling a database from default instance
> There is any problem about performance using linked server
> in that condition?
> Thank's for information
> I was searching in Microsoft pages and I didn't find
> information over that topicssql

servers

I have created a link between server A and server B
(sever A contains the link info)
I set the default database of the user on server B to be the database I want
to link to on server B.
Inside Enterprise Manager I can view the tables on server B via the link
icon on server A.
When I start Visual Studio and go to the server explorer to design a query
using the GUI interface, when I try to code:
SEVER_NAME.DATABASE.DBO.TABLE or
SERVER_NAME.DBO.TABLE
(Thinking that if the user on the linked servers default database is set,
then no need to specified in the query?)
inside the query it aliases the table name but not columns display in the
design view, just the *. Trying to run the query said that the Name can only
be three parts or invalid object name SERVER_NAME.dbo.TABLE
Like I said I can view the tables via the linking server though EM. My
developers need to be able to use the VS gui to design their queries. I ran
into situations in the past were if I typed the query straight (not using
gui), and try to save it, I would get a message stated that the MS DTC could
not be started, when I know for a fact both server A & B have the DTC runnin
g.
Can anyone provide assistance?SQL Server Service Manager (SQL 2000), and SQL Server Configuration Manager
(SQL 2005).
ML
http://milambda.blogspot.com/|||SQL Server
DTC
and the SQL Ageent are all running.
Where else do I need to look or configure
--
JP
.NET Software Develper
"ML" wrote:

> SQL Server Service Manager (SQL 2000), and SQL Server Configuration Manage
r
> (SQL 2005).
>
> ML
> --
> http://milambda.blogspot.com/

server with SQLNCLI and a default database.

I have a linked server from one 2005 server (server A) to another 2005 server (server B). The linked server is created like this:

EXEC sp_addlinkedserver

@.server = N'TEST',

@.srvproduct=N'',

@.provider=N'SQLNCLI',

@.datasrc=N'twx-webdev',

@.catalog=N'Hornet'

This query works fine:

SELECT * FROM TEST.Hornet.dbo.NotificationTrigger

But, I need to be able to make a query from server A to server B without specifying the database name. I need the default database of the linked server to be used when making the following query:

SELECT * FROM TEST...NotificationTrigger

This query does not work. The query returns the following error:

Msg 7313, Level 16, State 1, Line 1

An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "TEST".

I also used the following syntax when creating the linked server hoping the Initial Catalog in the provider string would work.

EXEC sp_addlinkedserver

@.server = N'TEST',

@.srvproduct=N'',

@.provider=N'SQLNCLI',

@.datasrc=N'twx-webdev',

@.provstr=N'Initial Catalog=Hornet'

The query against this linked server returned this error:

Msg 7314, Level 16, State 1, Line 2

The OLE DB provider "SQLNCLI" for linked server "TEST" does not contain the table ""dbo"."NotificationTrigger"". The table either does not exist or the current user does not have permissions on that table.

Also, this query produces the same results:

SELECT * FROM TEST..dbo.NotificationTrigger

How can I force the linked server to actually use the default database and not have to specify the database in queries? The 2005 documentation for linked servers alludes to this being possible.

Any help you can provide is very appreciated.

Thank you,

Chris

http://msdn2.microsoft.com/en-us/ms188718.aspx

http://support.microsoft.com/kb/280106

http://sqlserver2000.databases.aspfaq.com/how-do-i-prevent-linked-server-errors.html - a good one as a whole to resolve the LS errors.

|||

The following quote is from your first link. Is this telling me that I cannot default the database in a query even if I have the default catalog set on the linked server?

"When you use four-part names, always specify the schema name. Not specifying a schema name in a distributed query prevents OLE DB from finding tables. When referencing local tables, SQL Server uses defaults if an owner name is not specified. The following SELECT statement would generate a 7314 error, even if the linked server login mapped to a dbo user in the AdventureWorks database on the linked server:"

Thanks,
Chris

server w/ text field gets Connection Broken error

We have a stored proc on Server B called:

my_sp_server_b it takes 1 parameter a text field as a parameter, with default set to NULL

this proc calls:

my_sp_server_a through a linked server (which happens to be the same server, different DB), it has two parameters: my_id int, my_text text w/ my_text having a default set to NULL

This second stored procedure just selects back an ID that is passed to it (to keep things simple).

If we pass any string value to my_sp_server_b we get the appropriate hardcoded ID passed to my_sp_server_a. If we pass NULL to my_sp_server_b we get the following error:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

If we remove the linked server, and just reference my_sp_server_a via the scoped DB, we do not get an error. If we change the data type in both procs to varchar(50) we do not get an error. If we change the data type to nText we still get an error. If we put IF logic into stored procedure: my_sp_server_b to check for NULL in the input parameter and if it true then to pass NULL explicitly to my_sp_server_a we do not get an error.

It seems to be a combination of using a linked server and trying to pass a text (or nText variable) with a NULL value to stored procedure. Sometimes the error changes based on which scenario I described above - but we consistantly receive an error unless we do some of the workarounds described above.

Any ideas?If I change the linked server from SQL Server to ODBC I get this error message:

Server: Msg 0, Level 19, State 1, Line 15
SqlDumpExceptionHandler: Process 244 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.|||Here is the DML:

-- Run on DATABASE_1
CREATE PROCEDURE my_sp_server_a
@.the_id int, @.the_text TEXT = NULL
AS

SELECT @.the_id

GO

-- Run on DATABASE_2
CREATE PROC my_sp_server_b @.my_text TEXT = NULL
AS

EXEC MY_LINKED_SERVER.DATABASE_1.DBO.my_sp_server_a @.the_id = 1, @.the_text = @.my_text

GO

server using default login credentials

I have to access data on a separate server, so I created a linked
server using this syntax (some names changed to be generic);
EXEC sp_addlinkedserver
@.server = N'LinkedServerName',
@.srvproduct = N' ',
@.provider = N'SQLOLEDB',
@.datasrc = N'MyServerName',
@.provstr = 'DRIVER={SQL
Server};SERVER=N'MyServerName';UID=MyAppUser;PWD=M yAppPW;',
@.catalog = N'MyTable'
This link is created, but when I try to do a simple select statement
on 'MyTable', it returns the error:
Login failed for user 'Domain\KirkH'
Therefore, it is trying to connect using my login credentials, INSTEAD
of the ones I specified when I created the linked server. Am I using
this incorrectly, or is my syntax wrong?
I would greatly appreciate any suggestions or comments.
Thank you!.
On Apr 16, 3:47Xpm, "John Bell" <jbellnewspo...@.hotmail.com> wrote:
> "Kirk" <lok...@.hotmail.com> wrote in message
> news:53688ba6-ce89-4f28-a02a-c486e8253a24@.2g2000hsn.googlegroups.com...
>
>
>
>
>
> Hi
> I would have expected your parameter to be
> @.provstr = 'DRIVER={SQL
> Server};SERVER=MyServerName;UID=MyAppUser;PWD=MyAp pPW;'
> i.e N'MyServerName' is not correct!
> Have you tried sp_addlinkedsrvlogin e.g.
> EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'false', NULL, 'MyAppUser',
> 'MyAppPW'
> John- Hide quoted text -
> - Show quoted text -
John,
You were correct - once I added the login using sp_addlinkedsrvlogin,
everything worked great.
Thank you very much for your help!

server using default login credentials

I have to access data on a separate server, so I created a linked
server using this syntax (some names changed to be generic);
EXEC sp_addlinkedserver
@.server = N'LinkedServerName',
@.srvproduct = N' ',
@.provider = N'SQLOLEDB',
@.datasrc = N'MyServerName',
@.provstr = 'DRIVER={SQL
Server};SERVER=N'MyServerName';UID=MyAppUser;PWD=MyAppPW;',
@.catalog = N'MyTable'
This link is created, but when I try to do a simple select statement
on 'MyTable', it returns the error:
Login failed for user 'Domain\KirkH'
Therefore, it is trying to connect using my login credentials, INSTEAD
of the ones I specified when I created the linked server. Am I using
this incorrectly, or is my syntax wrong?
I would greatly appreciate any suggestions or comments.
Thank you!."Kirk" <loki70@.hotmail.com> wrote in message
news:53688ba6-ce89-4f28-a02a-c486e8253a24@.2g2000hsn.googlegroups.com...
>I have to access data on a separate server, so I created a linked
> server using this syntax (some names changed to be generic);
> EXEC sp_addlinkedserver
> @.server = N'LinkedServerName',
> @.srvproduct = N' ',
> @.provider = N'SQLOLEDB',
> @.datasrc = N'MyServerName',
> @.provstr = 'DRIVER={SQL
> Server};SERVER=N'MyServerName';UID=MyAppUser;PWD=MyAppPW;',
> @.catalog = N'MyTable'
> This link is created, but when I try to do a simple select statement
> on 'MyTable', it returns the error:
> Login failed for user 'Domain\KirkH'
> Therefore, it is trying to connect using my login credentials, INSTEAD
> of the ones I specified when I created the linked server. Am I using
> this incorrectly, or is my syntax wrong?
> I would greatly appreciate any suggestions or comments.
> Thank you!.
Hi
I would have expected your parameter to be
@.provstr = 'DRIVER={SQL
Server};SERVER=MyServerName;UID=MyAppUser;PWD=MyAppPW;'
i.e N'MyServerName' is not correct!
Have you tried sp_addlinkedsrvlogin e.g.
EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'false', NULL, 'MyAppUser',
'MyAppPW'
John|||On Apr 16, 3:47=A0pm, "John Bell" <jbellnewspo...@.hotmail.com> wrote:
> "Kirk" <lok...@.hotmail.com> wrote in message
> news:53688ba6-ce89-4f28-a02a-c486e8253a24@.2g2000hsn.googlegroups.com...
>
>
> >I have to access data on a separate server, so I created a linked
> > server using this syntax (some names changed to be generic);
> > EXEC sp_addlinkedserver
> > @.server =3D N'LinkedServerName',
> > @.srvproduct =3D N' ',
> > @.provider =3D N'SQLOLEDB',
> > @.datasrc =3D N'MyServerName',
> > @.provstr =3D 'DRIVER=3D{SQL
> > Server};SERVER=3DN'MyServerName';UID=3DMyAppUser;PWD=3DMyAppPW;',
> > @.catalog =3D N'MyTable'
> > This link is created, but when I try to do a simple select statement
> > on 'MyTable', it returns the error:
> > =A0 Login failed for user 'Domain\KirkH'
> > Therefore, it is trying to connect using my login credentials, INSTEAD
> > of the ones I specified when I created the linked server. =A0Am I using
> > this incorrectly, or is my syntax wrong?
> > I would greatly appreciate any suggestions or comments.
> > Thank you!.
> Hi
> I would have expected your parameter to be
> @.provstr =3D 'DRIVER=3D{SQL
> Server};SERVER=3DMyServerName;UID=3DMyAppUser;PWD=3DMyAppPW;'
> i.e N'MyServerName' is not correct!
> Have you tried sp_addlinkedsrvlogin e.g.
> EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'false', NULL, 'MyAppUser',
> 'MyAppPW'
> John- Hide quoted text -
> - Show quoted text -
John,
You were correct - once I added the login using sp_addlinkedsrvlogin,
everything worked great.
Thank you very much for your help!