C
charles
I am in the process of moving my Access 2000
application's tables out to MS SQL Server and still use
MS Access as the front end of the application. My
approach is to dynamically create the tables is SQL
Server via pass through queries from Access and then link
the tables back into Access and perform operations (SQL)
on them as I normally would...
The problem that I am running into is with a MS SQL
Server property called CONCAT_NULL_YIELDS_NULL. When SET
CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value
with a string yields a NULL result. For example,
SELECT 'abc' + NULL yields NULL. When SET
CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null
value with a string yields the string itself (the null
value is treated as an empty string). For example,
SELECT 'abc' + NULL yields abc.
So, the fix seems simple, turn the property to OFF and be
done with it - but unfortunately it's not that simple...
according to MSDN: The "Connection-level settings (set
using the SET statement) override the default database
setting for CONCAT_NULL_YIELDS_NULL. By default, ODBC and
OLE DB clients issue a connection-level SET statement
setting CONCAT_NULL_YIELDS_NULL to ON for the session
when connecting to SQL Server. For more information, see
SET CONCAT_NULL_YIELDS_NULL."
So now my question becomes how do I control
the "connection-level" settings for the table links that
I am creating using MS Access? Is there a specific
parameter that I need to pass to turn the
CONCAT_NULL_YIELDS_NULL property OFF? Any help would be
very much appreciated!!!
-Charles-
application's tables out to MS SQL Server and still use
MS Access as the front end of the application. My
approach is to dynamically create the tables is SQL
Server via pass through queries from Access and then link
the tables back into Access and perform operations (SQL)
on them as I normally would...
The problem that I am running into is with a MS SQL
Server property called CONCAT_NULL_YIELDS_NULL. When SET
CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value
with a string yields a NULL result. For example,
SELECT 'abc' + NULL yields NULL. When SET
CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null
value with a string yields the string itself (the null
value is treated as an empty string). For example,
SELECT 'abc' + NULL yields abc.
So, the fix seems simple, turn the property to OFF and be
done with it - but unfortunately it's not that simple...
according to MSDN: The "Connection-level settings (set
using the SET statement) override the default database
setting for CONCAT_NULL_YIELDS_NULL. By default, ODBC and
OLE DB clients issue a connection-level SET statement
setting CONCAT_NULL_YIELDS_NULL to ON for the session
when connecting to SQL Server. For more information, see
SET CONCAT_NULL_YIELDS_NULL."
So now my question becomes how do I control
the "connection-level" settings for the table links that
I am creating using MS Access? Is there a specific
parameter that I need to pass to turn the
CONCAT_NULL_YIELDS_NULL property OFF? Any help would be
very much appreciated!!!
-Charles-