Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Statement parameter in Mailmerge.OpenDataSource
i'm trying to make some MailMerge by automating word2003.
If i call MailMerge.OpenDataSource, what can i pass for the Satement Parameter? For Connectioninformation i'm using a *.odc file, wich connects to a SQL-Server. The name of the odc-file i pass as the Name parameter. when I try SELECT * FROM MyView it fails. SELECT * FROM "MyView" succeeds. Also passing the name of the view suceeds only with quotationmarks What i'm trying to do is, passing a statement calling a table-valued Function with parameters and/or a statement with a where clause containing Filterconditions. Does anyone know, how to do this? Thanks |
#2
|
|||
|
|||
You can certainly pass an SQL SELECT statement with a WHERE clause. Getting
the syntax right can be difficult, as you have discovered. What I find generally works is a. quoting table and column names. I think it is clearer to use `` or [] rather than "" but it may be less standard these days b. aliasing tables - even when aliasing should not be syntactiaclly required - and always using the alias names when referencing individual columns c. using single quotes ' ' as text literal delimiters e.g. (not tested - you'll need to play around to check) SELECT [MV].* FROM [MyView] MV WHERE [MV].[MyColumn] = 'abc' You may also be able to pass a call to a Transact-SQL procedure using the old ODBC escape syntax which looks something like { call functionname(p1,p2) } You may be able to find out more about that in SQL Server Books Online. However, I have had a mixed experience using that approach in the past because, for example, procedures that return multiple results do not seem to work. Peter Jamieson "Christof Nordiek" wrote in message ... i'm trying to make some MailMerge by automating word2003. If i call MailMerge.OpenDataSource, what can i pass for the Satement Parameter? For Connectioninformation i'm using a *.odc file, wich connects to a SQL-Server. The name of the odc-file i pass as the Name parameter. when I try SELECT * FROM MyView it fails. SELECT * FROM "MyView" succeeds. Also passing the name of the view suceeds only with quotationmarks What i'm trying to do is, passing a statement calling a table-valued Function with parameters and/or a statement with a where clause containing Filterconditions. Does anyone know, how to do this? Thanks |
#3
|
|||
|
|||
OH yes, also SQLStatement has a maximum length of around 255 characters. You
/may/ be able to get more by using SQLStatement1 as well, in which case the complete statement is the concatenation of SQLStatement and SQLStatement1 and you may get up to around 511 characters, but it does vary depending on the version of Word, the data source and the connection method. Peter Jamieson "Peter Jamieson" wrote in message ... You can certainly pass an SQL SELECT statement with a WHERE clause. Getting the syntax right can be difficult, as you have discovered. What I find generally works is a. quoting table and column names. I think it is clearer to use `` or [] rather than "" but it may be less standard these days b. aliasing tables - even when aliasing should not be syntactiaclly required - and always using the alias names when referencing individual columns c. using single quotes ' ' as text literal delimiters e.g. (not tested - you'll need to play around to check) SELECT [MV].* FROM [MyView] MV WHERE [MV].[MyColumn] = 'abc' You may also be able to pass a call to a Transact-SQL procedure using the old ODBC escape syntax which looks something like { call functionname(p1,p2) } You may be able to find out more about that in SQL Server Books Online. However, I have had a mixed experience using that approach in the past because, for example, procedures that return multiple results do not seem to work. Peter Jamieson "Christof Nordiek" wrote in message ... i'm trying to make some MailMerge by automating word2003. If i call MailMerge.OpenDataSource, what can i pass for the Satement Parameter? For Connectioninformation i'm using a *.odc file, wich connects to a SQL-Server. The name of the odc-file i pass as the Name parameter. when I try SELECT * FROM MyView it fails. SELECT * FROM "MyView" succeeds. Also passing the name of the view suceeds only with quotationmarks What i'm trying to do is, passing a statement calling a table-valued Function with parameters and/or a statement with a where clause containing Filterconditions. Does anyone know, how to do this? Thanks |
#4
|
|||
|
|||
Hi Peter
OK, i now can use a statement with a where-clause for filtering. The next thing i have to do is, call a table-valued function and give it an actual value for a parameter. SELECT * FROM MyFuction(58) works from QueryAnalyzer and from .NET-application but not for Mail-Merge. Do You know, how to do this?? Thanks "Peter Jamieson" schrieb im Newsbeitrag ... You can certainly pass an SQL SELECT statement with a WHERE clause. Getting the syntax right can be difficult, as you have discovered. What I find generally works is a. quoting table and column names. I think it is clearer to use `` or [] rather than "" but it may be less standard these days b. aliasing tables - even when aliasing should not be syntactiaclly required - and always using the alias names when referencing individual columns c. using single quotes ' ' as text literal delimiters e.g. (not tested - you'll need to play around to check) SELECT [MV].* FROM [MyView] MV WHERE [MV].[MyColumn] = 'abc' You may also be able to pass a call to a Transact-SQL procedure using the old ODBC escape syntax which looks something like { call functionname(p1,p2) } You may be able to find out more about that in SQL Server Books Online. However, I have had a mixed experience using that approach in the past because, for example, procedures that return multiple results do not seem to work. Peter Jamieson "Christof Nordiek" wrote in message ... i'm trying to make some MailMerge by automating word2003. If i call MailMerge.OpenDataSource, what can i pass for the Satement Parameter? For Connectioninformation i'm using a *.odc file, wich connects to a SQL-Server. The name of the odc-file i pass as the Name parameter. when I try SELECT * FROM MyView it fails. SELECT * FROM "MyView" succeeds. Also passing the name of the view suceeds only with quotationmarks What i'm trying to do is, passing a statement calling a table-valued Function with parameters and/or a statement with a where clause containing Filterconditions. Does anyone know, how to do this? Thanks |
#5
|
|||
|
|||
Do You know, how to do this??
No, I haven't been able to find a way to use a table-valued function using OLEDB. The only way I can get something similar to work is to use ODBC. For example, if you have a user/system DSN called sql2000, in Word 2002/2003 the following works here with suitable values for server, database and security values: OpenDataSource _ Name:="", _ Connection:="DSN=sql2000;SERVER=myserver;DATABASE= mydb;UID=myID;PWD=mypw;Trusted_Connection=No;", _ SQLStatement:="SELECT * FROM myfunc(1)", _ Subtype:=wdMergeSubtypeWord2000 Or use Connection:="DSN=sql2000;SERVER=myserver;DATABASE= mydb;Trusted_Connection=Yes;" for a trusted connection. I've tested the above. Experience suggests that if that works you can also use a file dsn (which IMO is likely to be more easily distributed, like a ..odc) via OpenDataSource _ Name:="pathname of the .dsn file", _ connection:="FILEDSN=pathnameof the .dsn file;additional parameters;", _ SQLStatement:="SELECT * FROM myfunc(1)" Peter Jamieson "Christof Nordiek" wrote in message ... Hi Peter OK, i now can use a statement with a where-clause for filtering. The next thing i have to do is, call a table-valued function and give it an actual value for a parameter. SELECT * FROM MyFuction(58) works from QueryAnalyzer and from .NET-application but not for Mail-Merge. Do You know, how to do this?? Thanks "Peter Jamieson" schrieb im Newsbeitrag ... You can certainly pass an SQL SELECT statement with a WHERE clause. Getting the syntax right can be difficult, as you have discovered. What I find generally works is a. quoting table and column names. I think it is clearer to use `` or [] rather than "" but it may be less standard these days b. aliasing tables - even when aliasing should not be syntactiaclly required - and always using the alias names when referencing individual columns c. using single quotes ' ' as text literal delimiters e.g. (not tested - you'll need to play around to check) SELECT [MV].* FROM [MyView] MV WHERE [MV].[MyColumn] = 'abc' You may also be able to pass a call to a Transact-SQL procedure using the old ODBC escape syntax which looks something like { call functionname(p1,p2) } You may be able to find out more about that in SQL Server Books Online. However, I have had a mixed experience using that approach in the past because, for example, procedures that return multiple results do not seem to work. Peter Jamieson "Christof Nordiek" wrote in message ... i'm trying to make some MailMerge by automating word2003. If i call MailMerge.OpenDataSource, what can i pass for the Satement Parameter? For Connectioninformation i'm using a *.odc file, wich connects to a SQL-Server. The name of the odc-file i pass as the Name parameter. when I try SELECT * FROM MyView it fails. SELECT * FROM "MyView" succeeds. Also passing the name of the view suceeds only with quotationmarks What i'm trying to do is, passing a statement calling a table-valued Function with parameters and/or a statement with a where clause containing Filterconditions. Does anyone know, how to do this? Thanks |
#6
|
|||
|
|||
OK
i tried it with ODBC with dsn file. Looked good at first. But then i noticed that all the textfields are empty. When I try simple queries that work with odc file the result is the same: textfields are empty. "Peter Jamieson" schrieb im Newsbeitrag ... Do You know, how to do this?? No, I haven't been able to find a way to use a table-valued function using OLEDB. The only way I can get something similar to work is to use ODBC. For example, if you have a user/system DSN called sql2000, in Word 2002/2003 the following works here with suitable values for server, database and security values: OpenDataSource _ Name:="", _ Connection:="DSN=sql2000;SERVER=myserver;DATABASE= mydb;UID=myID;PWD=mypw;Trusted_Connection=No;", _ SQLStatement:="SELECT * FROM myfunc(1)", _ Subtype:=wdMergeSubtypeWord2000 Or use Connection:="DSN=sql2000;SERVER=myserver;DATABASE= mydb;Trusted_Connection=Yes;" for a trusted connection. I've tested the above. Experience suggests that if that works you can also use a file dsn (which IMO is likely to be more easily distributed, like a .odc) via OpenDataSource _ Name:="pathname of the .dsn file", _ connection:="FILEDSN=pathnameof the .dsn file;additional parameters;", _ SQLStatement:="SELECT * FROM myfunc(1)" Peter Jamieson "Christof Nordiek" wrote in message ... Hi Peter OK, i now can use a statement with a where-clause for filtering. The next thing i have to do is, call a table-valued function and give it an actual value for a parameter. SELECT * FROM MyFuction(58) works from QueryAnalyzer and from .NET-application but not for Mail-Merge. Do You know, how to do this?? Thanks "Peter Jamieson" schrieb im Newsbeitrag ... You can certainly pass an SQL SELECT statement with a WHERE clause. Getting the syntax right can be difficult, as you have discovered. What I find generally works is a. quoting table and column names. I think it is clearer to use `` or [] rather than "" but it may be less standard these days b. aliasing tables - even when aliasing should not be syntactiaclly required - and always using the alias names when referencing individual columns c. using single quotes ' ' as text literal delimiters e.g. (not tested - you'll need to play around to check) SELECT [MV].* FROM [MyView] MV WHERE [MV].[MyColumn] = 'abc' You may also be able to pass a call to a Transact-SQL procedure using the old ODBC escape syntax which looks something like { call functionname(p1,p2) } You may be able to find out more about that in SQL Server Books Online. However, I have had a mixed experience using that approach in the past because, for example, procedures that return multiple results do not seem to work. Peter Jamieson "Christof Nordiek" wrote in message ... i'm trying to make some MailMerge by automating word2003. If i call MailMerge.OpenDataSource, what can i pass for the Satement Parameter? For Connectioninformation i'm using a *.odc file, wich connects to a SQL-Server. The name of the odc-file i pass as the Name parameter. when I try SELECT * FROM MyView it fails. SELECT * FROM "MyView" succeeds. Also passing the name of the view suceeds only with quotationmarks What i'm trying to do is, passing a statement calling a table-valued Function with parameters and/or a statement with a where clause containing Filterconditions. Does anyone know, how to do this? Thanks |
#7
|
|||
|
|||
Another special Word "feature" :-(
If they are Unicode fields (NVARCHAR, NTEXT etc.) Word may not see the contents (certainly with the ODBC route, but I thought it worked with OLEDB). The best you can do in that case is use TransactSQL CAST/CONVERT to return the VARCHAR, TEXT version. Unfortunately you may well be using characters that are unavailable in the target character set. Peter Jamieson "Christof Nordiek" wrote in message ... OK i tried it with ODBC with dsn file. Looked good at first. But then i noticed that all the textfields are empty. When I try simple queries that work with odc file the result is the same: textfields are empty. "Peter Jamieson" schrieb im Newsbeitrag ... Do You know, how to do this?? No, I haven't been able to find a way to use a table-valued function using OLEDB. The only way I can get something similar to work is to use ODBC. For example, if you have a user/system DSN called sql2000, in Word 2002/2003 the following works here with suitable values for server, database and security values: OpenDataSource _ Name:="", _ Connection:="DSN=sql2000;SERVER=myserver;DATABASE= mydb;UID=myID;PWD=mypw;Trusted_Connection=No;", _ SQLStatement:="SELECT * FROM myfunc(1)", _ Subtype:=wdMergeSubtypeWord2000 Or use Connection:="DSN=sql2000;SERVER=myserver;DATABASE= mydb;Trusted_Connection=Yes;" for a trusted connection. I've tested the above. Experience suggests that if that works you can also use a file dsn (which IMO is likely to be more easily distributed, like a .odc) via OpenDataSource _ Name:="pathname of the .dsn file", _ connection:="FILEDSN=pathnameof the .dsn file;additional parameters;", _ SQLStatement:="SELECT * FROM myfunc(1)" Peter Jamieson "Christof Nordiek" wrote in message ... Hi Peter OK, i now can use a statement with a where-clause for filtering. The next thing i have to do is, call a table-valued function and give it an actual value for a parameter. SELECT * FROM MyFuction(58) works from QueryAnalyzer and from .NET-application but not for Mail-Merge. Do You know, how to do this?? Thanks "Peter Jamieson" schrieb im Newsbeitrag ... You can certainly pass an SQL SELECT statement with a WHERE clause. Getting the syntax right can be difficult, as you have discovered. What I find generally works is a. quoting table and column names. I think it is clearer to use `` or [] rather than "" but it may be less standard these days b. aliasing tables - even when aliasing should not be syntactiaclly required - and always using the alias names when referencing individual columns c. using single quotes ' ' as text literal delimiters e.g. (not tested - you'll need to play around to check) SELECT [MV].* FROM [MyView] MV WHERE [MV].[MyColumn] = 'abc' You may also be able to pass a call to a Transact-SQL procedure using the old ODBC escape syntax which looks something like { call functionname(p1,p2) } You may be able to find out more about that in SQL Server Books Online. However, I have had a mixed experience using that approach in the past because, for example, procedures that return multiple results do not seem to work. Peter Jamieson "Christof Nordiek" wrote in message ... i'm trying to make some MailMerge by automating word2003. If i call MailMerge.OpenDataSource, what can i pass for the Satement Parameter? For Connectioninformation i'm using a *.odc file, wich connects to a SQL-Server. The name of the odc-file i pass as the Name parameter. when I try SELECT * FROM MyView it fails. SELECT * FROM "MyView" succeeds. Also passing the name of the view suceeds only with quotationmarks What i'm trying to do is, passing a statement calling a table-valued Function with parameters and/or a statement with a where clause containing Filterconditions. Does anyone know, how to do this? Thanks |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible BUG: Multiple HTTPrequests send from one includetext statement | Mailmerge | |||
How do I copy a scanned signature into an if/or statement in Word | Mailmerge | |||
How to include quotation marks in If/Then Statement? | Microsoft Word Help | |||
Insert carriage return in mergefield statement | Mailmerge | |||
Using Hyperlinks in Mail Merge IF...THEN...ELSE Statements | Mailmerge |