Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,572
    Thanks
    5
    Thanked 1,057 Times in 926 Posts
    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

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by Niven View Post
    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).
    Wendell

  4. #4
    Lounger
    Join Date
    Dec 2009
    Location
    White Bear Lake, MN
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts
    With the limited info provided, the best I can do is start a "try this and post the result" deal.

    Connect to ServerB\DB2 and run the following in a new query:

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

  5. #5
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:-

    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

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  8. #8
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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
    Andrew

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  11. #11
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  12. #12
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  14. #14
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Niven View Post
    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
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •