Linked Server missing some rows

Hi Experts,

I have a linked server with Excel linked to SQL Server on my local system.  I have six worksheets.  Five of the six worksheets appear correctly in SQL Server with the correct number of records.  One of the six only shows 999 records in SQL Server, but it should be 36,921.  

What could cause this?  Here is the meat of my stored procedure:  Any advice would be appreciated. (Code Snippet attached).
EXEC sp_addlinkedserver 
   @server = 'XLHybrid', 
   @provider = 'Microsoft.ACE.OLEDB.12.0',
   @srvproduct = 'Excel',
   @datasrc = 'C:\Database\XLHybrid.xlsm'
GO

Open in new window

brohjoeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkpieterseCommented:
Could you try to explain why you are using sql server linked to an Excel file?
0
brohjoeAuthor Commented:
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?
0
jkpieterseCommented:
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?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

brohjoeAuthor Commented:
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'

Open in new window

0
jkpieterseCommented:
So what about the # of records in the other sheets?
0
brohjoeAuthor Commented:
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.




0
brohjoeAuthor Commented:
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.
0
jkpieterseCommented:
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

Open in new window

0
brohjoeAuthor Commented:
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.
0
brohjoeAuthor Commented:
typo: "I need a method for Excel to share data with Excel and for Excel to share data with SQL SERVER
0
brohjoeAuthor Commented:
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."

0
jkpieterseCommented:
Of course. And if you want it done reliably, I guess all you can do is write the code to do exactly what you describe:

- Pull table into Excel
 user does editing here
- push changed/new records to SQL server

You'd need code that for each record either constructs INSERT statements or -if record exists- contructs an UPDATE query and executes that.

If however you get the stored procedure working, you could try to call the procedure from Excel VBA (never done it, no idea if it is hard).

Could it be possible that the offending sheet has a record with data type mismatches?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brohjoeAuthor Commented:
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.


 
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.