Linked Server missing some rows

Could you try to explain why you are using sql server linked to an Excel file?

Excel Spreadsheets are everywhere.  Estimates are that there is more data in Excel spreadsheets than there is in all the database management systems combined.

That said, I am conducting a test of my ability to connect the two using a stored procedure.on my local SQL Server database and my local Excel spreadsheet.  Ultimately, I need to link the Excel spreadsheet on a client machine to SQL Server in the clouds.  So this is only the first step.

Now that I have explained myself....:-)  Can you help?

The tables that work correctly, do they have over 999 records?
I still am not convinced of the usefulness of linking Excel files to SQL server. I would be worried about what happens when the Excel file moves. Or is the data copied to sql server by doing this?
If so, why not use ADO from Excel VBA to push the data to SQL Server tables?

I've discovered that I needed to make some adjustments to the stored procedure parameters.  Since I'm using an Excel.xlsm file, I need to make the @provstr = 'Excel 12.0 Macro'.  So I've made that change.  

Still getting the same problem.

EXEC sp_addlinkedserver 
   @server = 'XLHybrid', 
   @provider = 'Microsoft.ACE.OLEDB.12.0',
   @srvproduct = 'Excel',
   @provstr = 'Excel 12.0 Macro',
   @datasrc = 'C:\Database\XLHybrid.xlsm'

Select allOpen in new window

So what about the # of records in the other sheets?

Sorry, I posted the previous post prior to noticing you had a response already.

 The tables that work correctly have row counts that range from 60 to 36,921.  

I think I'm inclined to agree with you about using ADO on the Excel side to push the data instead of using a stored procedure on the SQL Server end to pull the data, because ultimately, a link will have to be made with SQL Server on a web-based server and how will SQL Server identify a client machine using that datasource parameter?   The reason why I even used the stored procedure idea in the first place is because I tried using ADO on the Excel side using the 'OPENDATASOURCE method to push the data, but I got an ISAM error that I couldn't resolve.  (maybe I should have put in a question for that).  

Anyway, I open to suggestions.   I know one of my requirements is to have a persistent link between SQL Server and Excel.  Data will be uploaded to SQL Server and/or Excel then a link needs to be made between the two to allow for updates, deletes, inserts and from what I've read on MSDN, and other sites,  the Linked method is the way to go.  But I'm open.  I've been working on this for some time now.

I've done a little bit of research.
Here are some of my site resources:

 http://msdn.microsoft.com/en-us/library/ms190479.aspx

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=135
 
  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstdatatypeenum.asp

http://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/

http://www.sommarskog.se/dynamic_sql.html

http://www.connectionstrings.com/sql-server-2005

http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

The problem with a lot of these websites is that they are written as if you already know the proper steps and configuration actions to take to make the code work.  Some give some very good examples, but I have not seen a comprehensive example of how to do what I'm trying to do.  At least I didn't understand it enough to do it.

Any help is appreciated.

Basically, I need a method to dynamically transfer data both ways...to keep the Excel data and the Sql data in synch.  That's why I thought the sp_addLinkedServer stored procedure would do the trick.

Well, the code below should succesfully be able to insert a record into a table.

Tip: To get the proper connection string, just build a live querytable (Data, get external data) to the sql database from Excel and use :
?ActiveCell.QueryTable.Connection
from the VBA immediate window to get the string.

Sub foo()
    Dim sConn As String
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    sConn = "ODBC;Description=MyDatabase;"
    sConn = sConn & "DRIVER=SQL Server;SERVER=ServerName;"
    sConn = sConn & "UID=UserName;APP=Microsoft Office 2003;"
    sConn = sConn & "WSID=NotSureWhatGoesHereOnYourSystem;"
    sConn = sConn & "DATABASE=DatabaseName;Trusted_Connection=Yes"
    Set cn = New ADODB.Connection
    cn.Open sConn
    cn.Execute "INSERT INTO myTable (fld1,fld2,fls3) VALUES (2,'2',2)"
End Sub

Select allOpen in new window

Well, thanks, but I don't think that's going to help.  All that's doing is manually entering VALUES.  I have 6 spreadsheets and thousands of records.  Your method is too manual.  I need a method for SQL Server to share data with Excel and for Excel to share data with Excel.  Records need to be synchronized.  I could probably use a sql query to refresh the data each way...on subroutine to load data from Excel to the remote SQL Server, and another subroutine to load data visa-versa with an ability to recognize data that does not need to be replaced.  Only data new to either side would be loaded.  I would guess that one side would have to be the "winner" in case of conflicts.  I'm thinking out loud.   I'm thinking about this the way Blackberry does it's synchronization with Outlook.  But it doesn't have to be that elaborate.

typo: "I need a method for Excel to share data with Excel and for Excel to share data with SQL SERVER

It's early in the morning..."I need a method for Excel to share data with SQL server and for SQL server to share data with Excel."

You have presented the exact scenario that needs to take place, with one caveat:  The data needs to first be pushed up to the remote SQL Server database.

Yes, if I could call a stored procedure to execute on the remote SQL Server database from a local Excel client using VBA, that would be a solution.   According to some additional research I've done on the subject, RPC or remote procedure calls are not recommended for new code.  It is a legacy process that should no longer be used.  But if that is the only way to make a remote procedure call, then what is the harm in using it?   What is the new way to conduct remote procedure calls if the RPC procss is deprecated?

 Q. Could it be possible that the offending sheet has a record with data type mismatches?
A. This is something that I've looked into as well.  I understand that using "IMEX=1" in the connection string will take care of type mis-matches.  

I'm going to play around with the code some more today and see how it goes.