Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Posts
    25

    Unanswered: reading in data..

    I have a stored procedure that recieves a 1,2,3, or 4(quarters of the year) and then returns the respected data in a table. I enter the quarter I want in the SQL Query Analyzier when running the stored procedure.
    example:

    getSales 1

    getSales being the procedure and 1 being the quarter

    Is there anyway I could enter the qauarter I want in a text file and have it read in by the stored procedure???

  2. #2
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    You can use some Transact-SQL [Command OPENDATASOURCE ] to run a select straight from an excel worksheet (uses the JET drivers)

    Or use the [ Stored Procedure sp_addlinkedserver ]



  3. #3
    Join Date
    May 2003
    Posts
    25
    Originally posted by WingMan
    You can use some Transact-SQL [Command OPENDATASOURCE ] to run a select straight from an excel worksheet (uses the JET drivers)

    Or use the [ Stored Procedure sp_addlinkedserver ]



    I put this code into a stored procedure...

    EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'c:\dt.xls',
    NULL,
    'Excel 5.0'
    GO
    EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL


    and it created the ExcelSource server... but how do I run queries on the spreadsheet???

    I typed SELECT *
    FROM ExcelSource...Quarter(Quarter is my datarange in my spreadsheet dt.xls)

    This gives me an error each time.. I checked the books online and they said this was the exact way to get info from this spreadsheet.. Any suggestions???????"

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    What was the error you're getting?
    Which version of SQL and SP used?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    May 2003
    Posts
    25
    Originally posted by Satya
    What was the error you're getting?
    Which version of SQL and SP used?

    I am using SQL Server 2000 and the error I recieve is very vague.

    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
    OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    How about the service pack on SQL?
    How about the authentication mode on SQL server and also the account privileges on NT Level to access that excel sheet?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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