Connecting from one SQL DB to Another

Topic

New Reply

Hi there,

I need to pull data into DB from a different SQL server and DB within (i.e. ServerA and DB1).

I have created the server as a new registration, but am a bit stuck as to how to say run what will be a stored proc or even simple select statement from ServerB and DB2.

Any help will be greatly appreciated!

Cheers,

Niven

Reply | Quote

Replies

The company I work for is an agent for a middleware product that connects and syncs almost any SQL database on almost any platform with any other SQL database. If you are interested let me know and I can get you in touch with the appropriate person.

Joe

--Joe

Reply | Quote

I have created the server as a new registration, but am a bit stuck as to how to say run what will be a stored proc or even simple select statement from ServerB and DB2.

It would be useful to know the versions of the two SQL Server Instances (2000, 2005, 2008 or the Express version of these). If you are using 2005 or 2008, the Import/Export utility will let you copy tables, or selected data from those tables into a new table or and existing table. If you are using 2000, the tools are a little older and require a bit more explanation.

Also note that if you have registered the other SQL Server, then you can work with it in SQL Server Management Studio (or Enterprise Manager in 2000).

Reply | Quote

With the limited info provided, the best I can do is start a “try this and post the result” deal.

Connect to ServerBDB2 and run the following in a new query:

USE DB2
SELECT *
INTO NewTable
FROM [ServerA].[dB1].dbo.TableName

Reply | Quote

Reaching back into my memory banks (a risky proposition at best), I seem to remember that you need pass-through queries to run stored procedures. It’s an option when you build a query.

Reply | Quote

Wendell/Guitarzan8

The source ServerA is running 2000 and the Target ServerB SQLExpress 2008. The datawarehouse (DB1) on ServerA is read only, so I can’t create stored procs etc on there which means I will have to execute everything form ServerB.

ServerA is hosted by our managing company and I am able to connect SQL Server Management Studio sessions via a user name and password.

Even though I have registered ServerA alongside ServerB, when I run

Code:

use DB2

SELECT * FROM 

[ServerA].[DB1].[dbo].[PolicySection] 

I get:-

Msg 18452, Level 14, State 1, Line 0
Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.

I have tried running:-

[indent][/indent]EXEC sp_addlinkedsrvlogin [ServerA], ‘false’, null, ‘name’, ‘password’

where name and password is my normal login to the remote server and this seems to run ok, i.e. “Command(s) completed successfully.”

However, when I combine this with the aforementioned code I still get the original error.

I’m somewhat puzzled as I can have both the Servers sitting side by side in SQL SMS and switch between them, but don’t seem to be able to link them together.

Also for good measure I ran “sp_addlinkedserver [ServerA]”, but once again whilst the system said it had run successfully, it hasn’t made any difference.

Cheers,

Niven

Reply | Quote

The basic process using SQL Server Management Studio is to connect to the SQL 2000 system, right click on the database you want to extract data from, select Tasks and then select Export Data. That brings up a dialog box which lets you specify the Server Name and Database for the Source, the Server Name and Database for the Destination, and lets you use either Windows Authentication or SQL Server Authentication – use the former if you can. It then lets you pick tables or run a query and will create a new table(s) in the Destination database. I think that will help with your task.

Reply | Quote

Hi there,

I am looking to copy to a backup DB only those tables which are populated in the source DB. How can I tell which tables are populated and which are not other than opening each one up? Is there a stats table somewhere within SQLServer that I can interrogate for the source DB? There are over 300 tables in the source DB and copying all of them across takes over two hours.

I don’t wish to copy the DB over as I’ll be creating stored procs specifically for and within the backup DB. Effectively the backup will be refreshed on a daily basis. The tables which are empty will pretty much remain that way and will only clutter up the backup as they do the source.

Cheers,

Niven

Reply | Quote

Not sure if any of this will be helpful, but

This SQL View lists all the User Defined Tables in a SQL Database showing their names and Record Count

Code:

CREATE VIEW      vw_ListUserTablesAndRowCount 
AS
SELECT TOP 100 PERCENT *
FROM (
    SELECT
                 t.TABLE_SCHEMA + '.' + t.TABLE_NAME As TableName, 
                 SUM(sp.[rows]) AS RecordCount 
    FROM         INFORMATION_SCHEMA.TABLES t
    INNER JOIN   sys.partitions sp
    ON           sp.object_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
    INNER JOIN   sys.allocation_units sau
    ON           sau.container_id = sp.partition_id
    WHERE        TABLE_TYPE = 'BASE TABLE' 
    GROUP BY     t.TABLE_SCHEMA + '.' + t.TABLE_NAME ) A
    ORDER BY     TableName

You could then use the result set somehow to list only those with non zero row count

Code:

SELECT TableName FROM vw_ListUserTablesAndRowCount WHERE RecordCount  0 

Reply | Quote

How are you currently copying the tables? If you are doing it using an Access front-end, 2 hours seems pretty reasonable. However if you are doing it in SQL Server I would expect it to be much faster. We routinely copy and restore an entire database of gigabyte size in less than 10 minutes, and it is possible to do that in an automated fashion using the SQL Server 2008 tools. The easiest is the Copy Database unnder Tasks. The Detach/Copy/Reattach method, which you could use at off hours when noone is accessing the database. The other alternative is to use the SQL Management Object which can be used while the source dB is in use, but is a fair bit slower. However that second approach can be setup to only copy selected tables. A third option is to do a backup and restore, which also goes pretty quickly. Let us know if you want further details on any of these approaches.

Reply | Quote

Hi Wendell,

In retrospect, I think I should have added this to thread 776302L, as it relates to that issue. How do we go about adding this to the original thread?

To follow on from the original thread I found a document relating to the issue of connecting from a 64 bit server to a 32 bit. Extract text as below

Recently we started adding SQL Server 2008 64-bit servers to our production set and we ran into the following issue. When we ran queries on a linked 2000 server, we were getting the following error:

OLE DB provider “SQLNCLI10” for linked server “XXXXXX” returned message “The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.”.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10” for linked server “XXXXXX”. The provider supports the interface, but returns a failure code when it is used.

This article from MS website describes the issue pretty well, although it says the issue applies to 2005 but we are using 2008. As suggested, we ran the Instcat.sql file on our development system first, and we ended up getting errrors left and right, so we didn’t dare to run it on our main production server. In the end, this workaround worked for us – we needed to create a procedure in the master database on the linked 2000 server. The proc is called sp_tables_info_rowset_64 and it is needed because it is called by 64-bit servers when running remote queries.

Here is the text of the proc in case you ever need to do the same, create it in the master database:

create procedure sp_tables_info_rowset_64

@table_name sysname,

@table_schema sysname = null,

@table_type nvarchar(255) = null

as

declare @Result int set @Result = 0

exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
go

I am unable to try out the above solution as I only have read only access to the source DB and would probably be told to get lost if I requested anything else.

I moved on to trying the Import/Export routine which works fine apart from 1) Taking a long time and 2) needing to schedule it, where SSIS nows comes into play.

I have a learning curve where this is concerned, so any help in how to set up and schedule a job would be greatly appreciated! Typically I would want to schedule it around 7:30 am, when the overnight restore of the source datawarehouse has completed. I am also looking to make a backup of the target DB prior to refreshing so as to anticipate any problems that may have occurred with the source overnight refresh. I also felt that if I could ignore the redundant tables in the source DB then that would reduce the refresh time of the destination DB, hence my post on determining the empty tables.

Cheers,

Niven

Reply | Quote

Hi Andrew,

Many thanks your reply. However, I am unable to create views on the source server as I only have read only access.

I tried runnning the code without the create view aspect but am getting the following errors:-

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.partitions’.
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.allocation_units’.

Any help as always, greatly appreciated.

Cheers

Niven

Reply | Quote

Hi Andrew,

Many thanks your reply. However, I am unable to create views on the source server as I only have read only access.

I tried runnning the code without the create view aspect but am getting the following errors:-

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.partitions’.
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.allocation_units’.

Any help as always, greatly appreciated.

Cheers

Niven

They are System Views from your database.

You might have to add

USE [YourDBName]
GO

to the query.

On the other hand you might not have the authorisation to use the System Views

Reply | Quote

First, I have merged your two threads together per your suggestion. Second, I’m not sure I understand where you are encountering issues. Are you getting errors when you try to copy the tables, and is that what led you to the issue with 32-bit versus 64-bit? And another question – how large is the database you are copying? Also, is there a maintenance plan that creates a backup of the database each night. If so, doing a restore from that backup might be another option. With regard to the copy process, you won’t see much improvement by excluding the empty tables – copying empty tables takes very little time.

Reply | Quote