Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: connection to another sql server

    Hi,

    I am wondering about the viability of my project plan, as I am relatively new to SQL. I want to connect to another SQL server and automatically pull a set of data into a table of the SQL server I am connecting from. The connection is already set up, as in I can connect to this other server using the object explorer, I just want to be able to include the connection in an automatic procedure. Does this sound doable? Would I use the SqlConnect function?

    As a follow up question, if I am to use the SqlConnect function, how do I determine the connection handle I need to input?

    Thanks in advance

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Are you planning to connect from a stored procedure, or from a front-end code? I don't recall SQLConnect being a function. Maybe SQLConnection? But that's an object. ...unless you're not talking about .NET...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    SqlConnect appears to be a FoxPro function, unless my Google-fu is weak.

    Are you dealing with trying to connect FoxPro to SQL Server? Or an application that is trying to connect to two databases in the back?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    wow...last summer, when cleaning my guarage, I got rid of some DOS and VMS books, and almost hurt myself with FoxPro 2.0 manual...So he was NOT talking about .NET, which is what I said
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Mar 2011
    Posts
    2

    RE: connection to another sql server

    Thanks for the replies. I feel like I am sort of out of my league at the moment, as, yes, I just Googled "sql connection", and the SQLConnect function, at least it's definition, seemed to be the most applicable to what I am trying to do. But, I'm not using FoxPro, so it appears I was looking in the wrong place.

    Let me try to clarify what I am trying to do. My company has been using a dashboard software program that connects to SQL Server 2008 in our office, which provides the data to create dashboard graphs and tables. We are now seeking to expand the use of this dashboard for one of our clients, and so the original plan was to use the dashboard software to connect directly to their database (SQL 2003) and visualize their data. The problem is that the information they have that we are trying to visualize is all located in one massive table, with hundreds of thousands of rows, and is continuously being updated. The dashboard software times out trying to sort through it and create a graph. I contacted the dashboard software company to see if they had any ideas, and they basically said I would need to optimize the database in some way, but since this is our client's database, I can't be messing around with it.

    I noticed, however, that obtaining the same numbers I would need for a graph in the dashboard software by executing a search query in Management Studio was very fast. This got me thinking, if I was able to automatically connect to their database from a stored procedure on our SQL server, say every hour, and pull/copy the data I needed from their massive table, and organize it in our database, and then have the dashboard software connect the newly organized database, this would solve the timeout problem.

    The core issue is that I really have no idea what I'm talking about, or if this is possible, as I'm not sure of SQL server's capabilities in this area, nor does anyone in my office. So, my first question, is the theory sound? If so, then my second question, how do you connect from a stored procedure?

    Apologies for the length, and thanks so much for the help

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You would be looking into a linked server in that case. I have had some performance problems (table scans at the remote server) that I was not too happy with, so ideally you will have to do some load testing while they are pumping data into a QA system of some sort.

Posting Permissions

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