Thread: Fails pasting SQL data to Excel

Hi

Very sorry for the delay in replying. Am trying to back through the un answered posts at the moment... In any case sorry for the delay.

This issue seems comes up from time to time and I hope we can get the common causes for this set out soon enough.

My first guess would be to see if specific characters are causing the problem.

One way to investigage this would be to use trial and error to limit your dataset gradually.

So for example, if you know the problem occurs say after 70 records, then you could limit the recordset by using the TOP keyword and say limit to the first 65 records and say this is ok, try 66, and say if this is ok try 67 etc - till you find the problem row. Once you have the problem row you could try the same process at a field level.

This way, hopefully you could narrow the problem down to a specific row and field. Then by examing the field value you will hopefully get a better idea of what is causing the problem.

I am guessing it a problem character that is in the database but not on the client. But cant be sure at all.

Sorry, cant be of any more help. Please let me know how it goes and sorry again for taking so long to get back to you.

Al

Hi Al,

I cut my database down to just one record (I have a test system I can play with) and then loaded some free text into one of my data fields - text came from just something out of a Word document and I made sure it didn\'t have any formatting or funny control characters...

Ran the Excel SQL query and tried to paste the one record - bang, same error message. Also then tried clearing the SQL data field down and just typing in hundreds of XXXXXX\'s directly via MS SQL Studio Manager, ran the Excel SQL query again and paste ... still falied.

Final test was to reduce number of characters (both from the Word doc and the typed in XXXXX\'s) down to around 240\'ish and it all worked fine. Obvioulsy I can copy and paste any amount of text into an Excel cell but the Excel SQL \"paste\" seems to hit a limit around 255 characters....

Thanks
Andy

Hi Andy

Thanks very much for narrowing this issue down like that. It is a big help.

This issue has been reported a few times now and hopefully we can get this sorted this time.

Please can you let me know which database system (and version) and also the driver (and version) you are using.
The version of Excel too is good to know.

I need to try to reproduce this issue to try to get a good grip around this.

Please let me know when you can and thanks again for all the help.

Have a nice evening.

Al

Hi ,
Excellent product .
But after you click paste or embed sql ...the excel file is created but u need to login to sql server again to create addl excel files . How can that be avoided ?
rgds
tapas

Hi Tapas
Hope you are fine.

Thanks for the complement on the tool. Needs a good amount of work but hopefully wil get there one of these months.

Sorry, you can only paste one at a time with the tool.

If you have a bunch of queries you want to run, you could do that with the embedded queries ?

Or, if that dont work, it might be a good idea to check out doing it with VBA. You could set up a routine to do that.

One other possiblity, is that I think you will be able to do that with DB Addin. It is an addin by Roland. Please check it out here when you get a chance. I think you can do what you want with DB Addin.
http://dbaddin.sourceforge.net/

Hope that helps.

Take it easy and good luck on that
Al

Al,

apologies in the delay - my version of Excel is Office 2003 SP2 (11.8105.8005) and the database I\'m connecting to in MS SQL Server 2005 (sorry, don\'t know too much more about it as it\'s a corporate wide system). I have access to a table within the database and can view, upload or modify data via MS Server Management Studio Express.

Andy