Category: sql server dataaccess
Panos Klaoudatos on Wed, 08 May 2013 14:05:07
I am having the following problem and although I tried to find a solution, I had no luck.
The scenario is as follows:
1. I have a .net 4 windows form application, which references Microsoft.Office.Interop.Word version 14 (that is Office 2010).
2. I build a query from my code by simply adding text from parameters. The final query string is the following:
ent_tech2006_progr @Ent='E1', @ProgrDrasiID=453, @d = '08/05/2013', @bViewAlt = 0, @bViewPractice = 0, @sFromDate = '08/05/1913', @sToDate = '08/05/2113', @PracticeCompID = 28, @ParOAEDPeriod = 1
(which actually calls a stored procedure with this parameters).
3. Then I am calling the MailMerge.OpenDataSource method of an opened Word Document to do the mail merge and display the data as merge fields, passing the query text and the connection string as parameters.
Now, the following strange thing occurs:
In some computers, the mail merge fails. I found out by using the SQL Profiler, that while in some computers (Windows 7 64bit connected to SQL 2005 or SQL 2008), the query passed is as above:
exec ent_tech2006_progr @Ent='E1', @ProgrDrasiID=453, @d = '08/05/2013', @bViewAlt = 0, @bViewPractice = 0, @sFromDate = '08/05/1913', @sToDate = '08/05/2113', @PracticeCompID = 28, @ParOAEDPeriod = 1
and this work OK,
in some other cases, (again with windows 7 64bit connected either to SQL 2005 or 2000), the same query called from the same code, is show in the profiler as:
declare @P1 int
declare @P2 int
declare @P3 int
declare @P4 int
exec sp_cursoropen @P1 output, N'exec ent_tech2006_progr @Ent=''E1'', @ProgrDrasiID=453, @d = ''08/05/2013'', @bViewAlt = 0, @bViewPractice = 0, @sFromDate = ''08/05/1913'', @sToDate = ''08/05/2113'', @PracticeCompID = 28, @ParOAEDPeriod = 1', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
This causes the mail merge to fail, returning no data, since it is formatted in this way.
Why does this happen? I checked both computers (the one that the scenario works with and the other that it does not work) and they are the same as far as the specs are concerned. Both computers are even development machines (with visual studio 2012 installed on them and I don't think that some reference is missing). Some other production computers, have the above behavior (query formatting 1 and query formatting 2) although they do not differ in specs.
GladToHelpYou on Fri, 10 May 2013 01:51:16
use same c# codes, got dif tsql codes? sql 2000 has dif sp2 vs later versions, the final tsql codes may dif. check the c# function with the correct .net version, sql version, may get some info about the function
Panos Klaoudatos on Fri, 10 May 2013 07:54:32
Well, I am using the same and exact c# code, from both machines (Win7 64bit, Win8, 64bit, with the same Office 2010 Pro) which as described above, calls the MailMerge.OpenDataSource method, passing the string "ent_tech2006_progr @Ent='E1'...." as query text, on the same SQL 2005 SP3 Server. Both machines have Visual Studio 2012 so I hit the break point just before the method is called. The query text is as above.
Then, just after the method is called and by watching the SQL Profiler, from the win7 machine I get the first formatted text that works and from the second machine (win8), I get the second formatted text that doesn't work!
Is it the sql driver? I can't understand, how the query text if formatted when calling the Word Interpo Method.
Panos Klaoudatos on Fri, 10 May 2013 09:26:03
I finally solved the problem!
On a 64bit machine, in order to access the SQL Server 2005 or 2000 (32bit), I have to create a System DSN from SysWow64\odbc32.exe driver's shortcut, otherwise a lot of macros fail MS Access.
In some computers, I have the problem mentioned in this post while in some other I don't (always accessing the same SQL Server).
This morning, on the Win8 machine, I created the same System DSN entry with the same name that already exists as 32bit mode, as 64bit mode. That is from Control Panel-> Administrative Tools -> ODBC.
For some reason I dodn't know, this 'fixed' the problem, the mail merge is completed successfully and the query is formatted as in the first case.
Anyway, although I found a solution, I still don't know the source of the problem. Please fell free to give everybody an insight.