I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.
This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.
Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.
I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.
Has anyone run across this before?
Thanks!
-BenWhat is the operating system on the new PC?
Is is connecting directly to the network?
What is the SQL Server ODBC driver version #?
The other thing I would do is find the developer that designed the application in Access and smack him in the back of the head for trying to develop a multi-user application in Access.
There are many many other alternatives that would work better and be much much faster.
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Sunday, March 25, 2012
Access cannot see MSDE when network cable disonnected
When my users are in the field and try to switch databases the
connection tool cannot see any servers even thought I know they are
there. The data link properties dialog does not see them. Also my data
entry program also cannot see them but is using sqldmo to list the
servers. However my SQL Service manager (inside my task bar) Can see
them. Any pointers on how to let the access project or the sqldmo
objects see the servers when they are disconnected. I even tried using
ODBC with no luck.
Thanks for any help.
Shane
Hello,
Personnaly, I use the following
Dim ListSvr As NameList
Set ListSvr = SQLDMO.ListAvailableSQLServers
' I add the servers to a collection after removing the ones I don't want
to show
' After that,
Set oServer = New SQLDMO.SQLServer2
Set ListSvr = oServer.ListInstalledInstances
' This function always work but only for the local instances (named or not)
' I add the needed servers to the other collection but I don't put the
servers already listed before
Marc Allard
Allcomp
Shane Lim wrote:
> When my users are in the field and try to switch databases the
> connection tool cannot see any servers even thought I know they are
> there. The data link properties dialog does not see them. Also my data
> entry program also cannot see them but is using sqldmo to list the
> servers. However my SQL Service manager (inside my task bar) Can see
> them. Any pointers on how to let the access project or the sqldmo
> objects see the servers when they are disconnected. I even tried using
> ODBC with no luck.
> Thanks for any help.
> Shane
>
|||That is PERFECT!!
On Fri, 25 Feb 2005 10:39:39 +0100, Allcomp <marc@.nospam.allcomp.be>
wrote:
[vbcol=seagreen]
>Hello,
>Personnaly, I use the following
>Dim ListSvr As NameList
>Set ListSvr = SQLDMO.ListAvailableSQLServers
>' I add the servers to a collection after removing the ones I don't want
>to show
>' After that,
>Set oServer = New SQLDMO.SQLServer2
>Set ListSvr = oServer.ListInstalledInstances
>' This function always work but only for the local instances (named or not)
>' I add the needed servers to the other collection but I don't put the
>servers already listed before
>Marc Allard
>Allcomp
>
>Shane Lim wrote:
connection tool cannot see any servers even thought I know they are
there. The data link properties dialog does not see them. Also my data
entry program also cannot see them but is using sqldmo to list the
servers. However my SQL Service manager (inside my task bar) Can see
them. Any pointers on how to let the access project or the sqldmo
objects see the servers when they are disconnected. I even tried using
ODBC with no luck.
Thanks for any help.
Shane
Hello,
Personnaly, I use the following
Dim ListSvr As NameList
Set ListSvr = SQLDMO.ListAvailableSQLServers
' I add the servers to a collection after removing the ones I don't want
to show
' After that,
Set oServer = New SQLDMO.SQLServer2
Set ListSvr = oServer.ListInstalledInstances
' This function always work but only for the local instances (named or not)
' I add the needed servers to the other collection but I don't put the
servers already listed before
Marc Allard
Allcomp
Shane Lim wrote:
> When my users are in the field and try to switch databases the
> connection tool cannot see any servers even thought I know they are
> there. The data link properties dialog does not see them. Also my data
> entry program also cannot see them but is using sqldmo to list the
> servers. However my SQL Service manager (inside my task bar) Can see
> them. Any pointers on how to let the access project or the sqldmo
> objects see the servers when they are disconnected. I even tried using
> ODBC with no luck.
> Thanks for any help.
> Shane
>
|||That is PERFECT!!
On Fri, 25 Feb 2005 10:39:39 +0100, Allcomp <marc@.nospam.allcomp.be>
wrote:
[vbcol=seagreen]
>Hello,
>Personnaly, I use the following
>Dim ListSvr As NameList
>Set ListSvr = SQLDMO.ListAvailableSQLServers
>' I add the servers to a collection after removing the ones I don't want
>to show
>' After that,
>Set oServer = New SQLDMO.SQLServer2
>Set ListSvr = oServer.ListInstalledInstances
>' This function always work but only for the local instances (named or not)
>' I add the needed servers to the other collection but I don't put the
>servers already listed before
>Marc Allard
>Allcomp
>
>Shane Lim wrote:
Access Boolean to SqlServer
Hi. In Access I can set a field as Boolean ... what′s the equivalent for SqlServer?
BIT, perhaps. (0, 1, or NULL).
Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
http://www.aspfaq.com/
(Reverse address to reply.)
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> Hi. In Access I can set a field as Boolean ... what′s the equivalent for
SqlServer?
|||Quick suggestion from years of experience...
Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
MS SQL with all it's connectivity potential with other tools - EXCEL, Crystal, etc - should not have "cryptic" values stored in columns. It's so much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1 or 0 depending on what denotes t
rue from false!).
Your non-tech users will appreciate this.
"Aaron [SQL Server MVP]" wrote:
> BIT, perhaps. (0, 1, or NULL).
> Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> SqlServer?
>
>
|||Steve,
Depends on whether you are talking about displaying information to users
or storing it in a database. A bit field is my preferred datatype for
storing booleans as they take up less space and can only accept three
values, 1, 0, NULL (if you can call NULL a value).
The front end application should translate the bit column to a Y/N, or
1/0, checked/unchecked so the user doesn't ahve to guess. That is the
function of the front-end.
You do make a good point for users that have direct access into SQL Server.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steve Z wrote:
> Quick suggestion from years of experience...
> Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
> MS SQL with all it's connectivity potential with other tools - EXCEL, Crystal, etc - should not have "cryptic" values stored in columns. It's so much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1 or 0 depending on what denotes
true from false!).[vbcol=seagreen]
> Your non-tech users will appreciate this.
> "Aaron [SQL Server MVP]" wrote:
>
|||And I fall in-between Mark and Ken...
I try to decide what is the primary function for the boolean... If the
answer is for high speed searching then I'll more likely use a bit ( or even
an integer for 32 booleans). If the primary use will simply be for
reporting, then I'd probably use a char(1), so drag.drop report stuff will
be easy...
You could also use bit in the table, and provide a view which exposes the
bit value as a char(1) and get the best of both worlds.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eZQIDKAYEHA.3012@.tk2msftngp13.phx.gbl...
> Steve,
> Depends on whether you are talking about displaying information to users
> or storing it in a database. A bit field is my preferred datatype for
> storing booleans as they take up less space and can only accept three
> values, 1, 0, NULL (if you can call NULL a value).
> The front end application should translate the bit column to a Y/N, or
> 1/0, checked/unchecked so the user doesn't ahve to guess. That is the
> function of the front-end.
> You do make a good point for users that have direct access into SQL
Server.[vbcol=seagreen]
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Steve Z wrote:
Crystal, etc - should not have "cryptic" values stored in columns. It's so
much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1
or 0 depending on what denotes true from false!).[vbcol=seagreen]
for[vbcol=seagreen]
sql
BIT, perhaps. (0, 1, or NULL).
Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
http://www.aspfaq.com/
(Reverse address to reply.)
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> Hi. In Access I can set a field as Boolean ... what′s the equivalent for
SqlServer?
|||Quick suggestion from years of experience...
Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
MS SQL with all it's connectivity potential with other tools - EXCEL, Crystal, etc - should not have "cryptic" values stored in columns. It's so much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1 or 0 depending on what denotes t
rue from false!).
Your non-tech users will appreciate this.
"Aaron [SQL Server MVP]" wrote:
> BIT, perhaps. (0, 1, or NULL).
> Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> SqlServer?
>
>
|||Steve,
Depends on whether you are talking about displaying information to users
or storing it in a database. A bit field is my preferred datatype for
storing booleans as they take up less space and can only accept three
values, 1, 0, NULL (if you can call NULL a value).
The front end application should translate the bit column to a Y/N, or
1/0, checked/unchecked so the user doesn't ahve to guess. That is the
function of the front-end.
You do make a good point for users that have direct access into SQL Server.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steve Z wrote:
> Quick suggestion from years of experience...
> Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
> MS SQL with all it's connectivity potential with other tools - EXCEL, Crystal, etc - should not have "cryptic" values stored in columns. It's so much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1 or 0 depending on what denotes
true from false!).[vbcol=seagreen]
> Your non-tech users will appreciate this.
> "Aaron [SQL Server MVP]" wrote:
>
|||And I fall in-between Mark and Ken...
I try to decide what is the primary function for the boolean... If the
answer is for high speed searching then I'll more likely use a bit ( or even
an integer for 32 booleans). If the primary use will simply be for
reporting, then I'd probably use a char(1), so drag.drop report stuff will
be easy...
You could also use bit in the table, and provide a view which exposes the
bit value as a char(1) and get the best of both worlds.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eZQIDKAYEHA.3012@.tk2msftngp13.phx.gbl...
> Steve,
> Depends on whether you are talking about displaying information to users
> or storing it in a database. A bit field is my preferred datatype for
> storing booleans as they take up less space and can only accept three
> values, 1, 0, NULL (if you can call NULL a value).
> The front end application should translate the bit column to a Y/N, or
> 1/0, checked/unchecked so the user doesn't ahve to guess. That is the
> function of the front-end.
> You do make a good point for users that have direct access into SQL
Server.[vbcol=seagreen]
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Steve Z wrote:
Crystal, etc - should not have "cryptic" values stored in columns. It's so
much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1
or 0 depending on what denotes true from false!).[vbcol=seagreen]
for[vbcol=seagreen]
sql
Access Boolean to SqlServer
Hi. In Access I can set a field as Boolean ... what′s the equivalent for Sq
lServer?BIT, perhaps. (0, 1, or NULL).
Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
http://www.aspfaq.com/
(Reverse address to reply.)
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> Hi. In Access I can set a field as Boolean ... what′s the equivalent for
SqlServer?|||Quick suggestion from years of experience...
Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
MS SQL with all it's connectivity potential with other tools - EXCEL, Crysta
l, etc - should not have "cryptic" values stored in columns. It's so much n
icer to connect to a table and see a Y or N instead of a 1 or 0 (or -1 or 0
depending on what denotes t
rue from false!).
Your non-tech users will appreciate this.
"Aaron [SQL Server MVP]" wrote:
> BIT, perhaps. (0, 1, or NULL).
> Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> SqlServer?
>
>|||Steve,
Depends on whether you are talking about displaying information to users
or storing it in a database. A bit field is my preferred datatype for
storing booleans as they take up less space and can only accept three
values, 1, 0, NULL (if you can call NULL a value).
The front end application should translate the bit column to a Y/N, or
1/0, checked/unchecked so the user doesn't ahve to guess. That is the
function of the front-end.
You do make a good point for users that have direct access into SQL Server.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steve Z wrote:
> Quick suggestion from years of experience...
> Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
> MS SQL with all it's connectivity potential with other tools - EXCEL, Crystal, etc
- should not have "cryptic" values stored in columns. It's so much nicer to connec
t to a table and see a Y or N instead of a 1 or 0 (or -1 or 0 depending on what deno
tes
true from false!).[vbcol=seagreen]
> Your non-tech users will appreciate this.
> "Aaron [SQL Server MVP]" wrote:
>|||And I fall in-between Mark and Ken...
I try to decide what is the primary function for the boolean... If the
answer is for high speed searching then I'll more likely use a bit ( or even
an integer for 32 booleans). If the primary use will simply be for
reporting, then I'd probably use a char(1), so drag.drop report stuff will
be easy...
You could also use bit in the table, and provide a view which exposes the
bit value as a char(1) and get the best of both worlds.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eZQIDKAYEHA.3012@.tk2msftngp13.phx.gbl...
> Steve,
> Depends on whether you are talking about displaying information to users
> or storing it in a database. A bit field is my preferred datatype for
> storing booleans as they take up less space and can only accept three
> values, 1, 0, NULL (if you can call NULL a value).
> The front end application should translate the bit column to a Y/N, or
> 1/0, checked/unchecked so the user doesn't ahve to guess. That is the
> function of the front-end.
> You do make a good point for users that have direct access into SQL
Server.[vbcol=seagreen]
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Steve Z wrote:
Crystal, etc - should not have "cryptic" values stored in columns. It's so
much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1
or 0 depending on what denotes true from false!).[vbcol=seagreen]
for[vbcol=seagreen]
lServer?BIT, perhaps. (0, 1, or NULL).
Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
http://www.aspfaq.com/
(Reverse address to reply.)
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> Hi. In Access I can set a field as Boolean ... what′s the equivalent for
SqlServer?|||Quick suggestion from years of experience...
Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
MS SQL with all it's connectivity potential with other tools - EXCEL, Crysta
l, etc - should not have "cryptic" values stored in columns. It's so much n
icer to connect to a table and see a Y or N instead of a 1 or 0 (or -1 or 0
depending on what denotes t
rue from false!).
Your non-tech users will appreciate this.
"Aaron [SQL Server MVP]" wrote:
> BIT, perhaps. (0, 1, or NULL).
> Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> SqlServer?
>
>|||Steve,
Depends on whether you are talking about displaying information to users
or storing it in a database. A bit field is my preferred datatype for
storing booleans as they take up less space and can only accept three
values, 1, 0, NULL (if you can call NULL a value).
The front end application should translate the bit column to a Y/N, or
1/0, checked/unchecked so the user doesn't ahve to guess. That is the
function of the front-end.
You do make a good point for users that have direct access into SQL Server.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steve Z wrote:
> Quick suggestion from years of experience...
> Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
> MS SQL with all it's connectivity potential with other tools - EXCEL, Crystal, etc
- should not have "cryptic" values stored in columns. It's so much nicer to connec
t to a table and see a Y or N instead of a 1 or 0 (or -1 or 0 depending on what deno
tes
true from false!).[vbcol=seagreen]
> Your non-tech users will appreciate this.
> "Aaron [SQL Server MVP]" wrote:
>|||And I fall in-between Mark and Ken...
I try to decide what is the primary function for the boolean... If the
answer is for high speed searching then I'll more likely use a bit ( or even
an integer for 32 booleans). If the primary use will simply be for
reporting, then I'd probably use a char(1), so drag.drop report stuff will
be easy...
You could also use bit in the table, and provide a view which exposes the
bit value as a char(1) and get the best of both worlds.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eZQIDKAYEHA.3012@.tk2msftngp13.phx.gbl...
> Steve,
> Depends on whether you are talking about displaying information to users
> or storing it in a database. A bit field is my preferred datatype for
> storing booleans as they take up less space and can only accept three
> values, 1, 0, NULL (if you can call NULL a value).
> The front end application should translate the bit column to a Y/N, or
> 1/0, checked/unchecked so the user doesn't ahve to guess. That is the
> function of the front-end.
> You do make a good point for users that have direct access into SQL
Server.[vbcol=seagreen]
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Steve Z wrote:
Crystal, etc - should not have "cryptic" values stored in columns. It's so
much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1
or 0 depending on what denotes true from false!).[vbcol=seagreen]
for[vbcol=seagreen]
Access Boolean to SqlServer
Hi. In Access I can set a field as Boolean ... what´s the equivalent for SqlServer?BIT, perhaps. (0, 1, or NULL).
Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> Hi. In Access I can set a field as Boolean ... what´s the equivalent for
SqlServer?|||Quick suggestion from years of experience...
Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
MS SQL with all it's connectivity potential with other tools - EXCEL, Crystal, etc - should not have "cryptic" values stored in columns. It's so much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1 or 0 depending on what denotes true from false!).
Your non-tech users will appreciate this.
"Aaron [SQL Server MVP]" wrote:
> BIT, perhaps. (0, 1, or NULL).
> Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> > Hi. In Access I can set a field as Boolean ... what�´s the equivalent for
> SqlServer?
>
>|||Steve,
Depends on whether you are talking about displaying information to users
or storing it in a database. A bit field is my preferred datatype for
storing booleans as they take up less space and can only accept three
values, 1, 0, NULL (if you can call NULL a value).
The front end application should translate the bit column to a Y/N, or
1/0, checked/unchecked so the user doesn't ahve to guess. That is the
function of the front-end.
You do make a good point for users that have direct access into SQL Server.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steve Z wrote:
> Quick suggestion from years of experience...
> Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
> MS SQL with all it's connectivity potential with other tools - EXCEL, Crystal, etc - should not have "cryptic" values stored in columns. It's so much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1 or 0 depending on what denotes true from false!).
> Your non-tech users will appreciate this.
> "Aaron [SQL Server MVP]" wrote:
>
>>BIT, perhaps. (0, 1, or NULL).
>>Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
>>--
>>http://www.aspfaq.com/
>>(Reverse address to reply.)
>>
>>
>>"KenA" <KenA@.discussions.microsoft.com> wrote in message
>>news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
>>Hi. In Access I can set a field as Boolean ... what�´s the equivalent for
>>SqlServer?
>>|||And I fall in-between Mark and Ken...
I try to decide what is the primary function for the boolean... If the
answer is for high speed searching then I'll more likely use a bit ( or even
an integer for 32 booleans). If the primary use will simply be for
reporting, then I'd probably use a char(1), so drag.drop report stuff will
be easy...
You could also use bit in the table, and provide a view which exposes the
bit value as a char(1) and get the best of both worlds.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eZQIDKAYEHA.3012@.tk2msftngp13.phx.gbl...
> Steve,
> Depends on whether you are talking about displaying information to users
> or storing it in a database. A bit field is my preferred datatype for
> storing booleans as they take up less space and can only accept three
> values, 1, 0, NULL (if you can call NULL a value).
> The front end application should translate the bit column to a Y/N, or
> 1/0, checked/unchecked so the user doesn't ahve to guess. That is the
> function of the front-end.
> You do make a good point for users that have direct access into SQL
Server.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Steve Z wrote:
> > Quick suggestion from years of experience...
> >
> > Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
> >
> > MS SQL with all it's connectivity potential with other tools - EXCEL,
Crystal, etc - should not have "cryptic" values stored in columns. It's so
much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1
or 0 depending on what denotes true from false!).
> >
> > Your non-tech users will appreciate this.
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> >
> >>BIT, perhaps. (0, 1, or NULL).
> >>
> >>Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
> >>
> >>--
> >>http://www.aspfaq.com/
> >>(Reverse address to reply.)
> >>
> >>
> >>
> >>
> >>"KenA" <KenA@.discussions.microsoft.com> wrote in message
> >>news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> >>
> >>Hi. In Access I can set a field as Boolean ... what´s the equivalent
for
> >>
> >>SqlServer?
> >>
> >>
> >>
Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> Hi. In Access I can set a field as Boolean ... what´s the equivalent for
SqlServer?|||Quick suggestion from years of experience...
Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
MS SQL with all it's connectivity potential with other tools - EXCEL, Crystal, etc - should not have "cryptic" values stored in columns. It's so much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1 or 0 depending on what denotes true from false!).
Your non-tech users will appreciate this.
"Aaron [SQL Server MVP]" wrote:
> BIT, perhaps. (0, 1, or NULL).
> Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> > Hi. In Access I can set a field as Boolean ... what�´s the equivalent for
> SqlServer?
>
>|||Steve,
Depends on whether you are talking about displaying information to users
or storing it in a database. A bit field is my preferred datatype for
storing booleans as they take up less space and can only accept three
values, 1, 0, NULL (if you can call NULL a value).
The front end application should translate the bit column to a Y/N, or
1/0, checked/unchecked so the user doesn't ahve to guess. That is the
function of the front-end.
You do make a good point for users that have direct access into SQL Server.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steve Z wrote:
> Quick suggestion from years of experience...
> Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
> MS SQL with all it's connectivity potential with other tools - EXCEL, Crystal, etc - should not have "cryptic" values stored in columns. It's so much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1 or 0 depending on what denotes true from false!).
> Your non-tech users will appreciate this.
> "Aaron [SQL Server MVP]" wrote:
>
>>BIT, perhaps. (0, 1, or NULL).
>>Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
>>--
>>http://www.aspfaq.com/
>>(Reverse address to reply.)
>>
>>
>>"KenA" <KenA@.discussions.microsoft.com> wrote in message
>>news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
>>Hi. In Access I can set a field as Boolean ... what�´s the equivalent for
>>SqlServer?
>>|||And I fall in-between Mark and Ken...
I try to decide what is the primary function for the boolean... If the
answer is for high speed searching then I'll more likely use a bit ( or even
an integer for 32 booleans). If the primary use will simply be for
reporting, then I'd probably use a char(1), so drag.drop report stuff will
be easy...
You could also use bit in the table, and provide a view which exposes the
bit value as a char(1) and get the best of both worlds.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eZQIDKAYEHA.3012@.tk2msftngp13.phx.gbl...
> Steve,
> Depends on whether you are talking about displaying information to users
> or storing it in a database. A bit field is my preferred datatype for
> storing booleans as they take up less space and can only accept three
> values, 1, 0, NULL (if you can call NULL a value).
> The front end application should translate the bit column to a Y/N, or
> 1/0, checked/unchecked so the user doesn't ahve to guess. That is the
> function of the front-end.
> You do make a good point for users that have direct access into SQL
Server.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Steve Z wrote:
> > Quick suggestion from years of experience...
> >
> > Don't use BIT fields - use the CHAR(1) with T or F; or with Y or N.
> >
> > MS SQL with all it's connectivity potential with other tools - EXCEL,
Crystal, etc - should not have "cryptic" values stored in columns. It's so
much nicer to connect to a table and see a Y or N instead of a 1 or 0 (or -1
or 0 depending on what denotes true from false!).
> >
> > Your non-tech users will appreciate this.
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> >
> >>BIT, perhaps. (0, 1, or NULL).
> >>
> >>Or, several workarounds. CHAR(1) with 'T' or 'F', 'Y' or 'N', etc.
> >>
> >>--
> >>http://www.aspfaq.com/
> >>(Reverse address to reply.)
> >>
> >>
> >>
> >>
> >>"KenA" <KenA@.discussions.microsoft.com> wrote in message
> >>news:1354FE6C-F45A-4D01-A8CA-AFFB0073A6DC@.microsoft.com...
> >>
> >>Hi. In Access I can set a field as Boolean ... what´s the equivalent
for
> >>
> >>SqlServer?
> >>
> >>
> >>
Thursday, March 22, 2012
Access a textbox from another textbox
Is it possible to get the value of a textbox from another textbox ? I can't
seem to find a way. What I am trying to do is sum a field that is only
listed on the report. Based on several factors there are multiple rows not
displayed and what I want to do is sum all of the rows that are displayed.
If I have a textbox called txtPrice, is there a way to access that (like
=txtPrice.text) in a different textbox and then additionally do a
=Sum(txtPrice.text).
Anyone?
Thanks!
ChrisTry
ReportItems!<textboxName>.Value
Jackson
"Chris Hastings" <chrshstngs@.comcast.net> wrote in message
news:2eOdne6G48T-ivTeRVn-jQ@.comcast.com...
> Is it possible to get the value of a textbox from another textbox ? I
> can't seem to find a way. What I am trying to do is sum a field that is
> only listed on the report. Based on several factors there are multiple
> rows not displayed and what I want to do is sum all of the rows that are
> displayed. If I have a textbox called txtPrice, is there a way to access
> that (like =txtPrice.text) in a different textbox and then additionally do
> a =Sum(txtPrice.text).
> Anyone?
> Thanks!
> Chris
>|||Hi Jackson,
Thanks! That answered my question, but unfortunately didn't solve my
problem.
I'm trying to get a sum of only fields that actually appear on the report
vs. fields that are in the entire dataset. Any ideas on that one?
Thanks!
Chris
"Jackson" <jackson_num5@.yahoo.com> wrote in message
news:uLHVad$3FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Try
> ReportItems!<textboxName>.Value
> Jackson
> "Chris Hastings" <chrshstngs@.comcast.net> wrote in message
> news:2eOdne6G48T-ivTeRVn-jQ@.comcast.com...
>> Is it possible to get the value of a textbox from another textbox ? I
>> can't seem to find a way. What I am trying to do is sum a field that is
>> only listed on the report. Based on several factors there are multiple
>> rows not displayed and what I want to do is sum all of the rows that are
>> displayed. If I have a textbox called txtPrice, is there a way to access
>> that (like =txtPrice.text) in a different textbox and then additionally
>> do a =Sum(txtPrice.text).
>> Anyone?
>> Thanks!
>> Chris
>>
>|||I think you can do that by adding a footer and in one of the footer fields
right click and go into expressions.
There you'll be able to sum up the fields that appear in the reports detail
area. So you can type this
=Sum(Fields!Field1.Value)+Sum(Fields!Field2.Value) etc.
Hope that helps,
Jackson
"Chris Hastings" <chrshstngs@.comcast.net> wrote in message
news:v5WdncrjzMToofTeRVn-pg@.comcast.com...
> Hi Jackson,
> Thanks! That answered my question, but unfortunately didn't solve my
> problem.
> I'm trying to get a sum of only fields that actually appear on the report
> vs. fields that are in the entire dataset. Any ideas on that one?
> Thanks!
> Chris
> "Jackson" <jackson_num5@.yahoo.com> wrote in message
> news:uLHVad$3FHA.1420@.TK2MSFTNGP09.phx.gbl...
>> Try
>> ReportItems!<textboxName>.Value
>> Jackson
>> "Chris Hastings" <chrshstngs@.comcast.net> wrote in message
>> news:2eOdne6G48T-ivTeRVn-jQ@.comcast.com...
>> Is it possible to get the value of a textbox from another textbox ? I
>> can't seem to find a way. What I am trying to do is sum a field that is
>> only listed on the report. Based on several factors there are multiple
>> rows not displayed and what I want to do is sum all of the rows that are
>> displayed. If I have a textbox called txtPrice, is there a way to access
>> that (like =txtPrice.text) in a different textbox and then additionally
>> do a =Sum(txtPrice.text).
>> Anyone?
>> Thanks!
>> Chris
>>
>>
>|||Hi again Jackson,
Thanks for taking the time.
A little background.
I have a query that joins multiple tables (I can't do subselects because my
ODBC driver doesn't support them), so for a given item that I want, I need
information from 4 other tables, for which I have to use joins. I group by
the main item and then use the First(field!..) to display the information
and I hide my detail line. By the way, one of the pieces of information I
want from another table is just a count, but in any case, there could be 1
record for an item or 1000, depending on the sub tables. It's annoying...
Anyway, the main item table has a price in it, and I want to sum all of the
prices for the items for a customer. The problem is that each item is
actual between 1 to 1000 actual records because of the other joins. So, my
Sums are completely goofy. The mental block I am trying to overcome is that
I can see exactly what I want to sum on the screen (because it's in a group
header and there are no detail lines) but I can't for the life of me figure
out a way to either sum the information that appears only on the report, or
sum pieces of the recordset based on other aggregate functions (like first
or count).
I'm contemplating forcing them to move their data to SQL, but I'm thinking
that is unlikely...
"Jackson" <jackson_num5@.yahoo.com> wrote in message
news:u%23Vke9$3FHA.2816@.tk2msftngp13.phx.gbl...
> I think you can do that by adding a footer and in one of the footer fields
> right click and go into expressions.
> There you'll be able to sum up the fields that appear in the reports
> detail area. So you can type this
> =Sum(Fields!Field1.Value)+Sum(Fields!Field2.Value) etc.
>
> Hope that helps,
> Jackson
>
> "Chris Hastings" <chrshstngs@.comcast.net> wrote in message
> news:v5WdncrjzMToofTeRVn-pg@.comcast.com...
>> Hi Jackson,
>> Thanks! That answered my question, but unfortunately didn't solve my
>> problem.
>> I'm trying to get a sum of only fields that actually appear on the report
>> vs. fields that are in the entire dataset. Any ideas on that one?
>> Thanks!
>> Chris
>> "Jackson" <jackson_num5@.yahoo.com> wrote in message
>> news:uLHVad$3FHA.1420@.TK2MSFTNGP09.phx.gbl...
>> Try
>> ReportItems!<textboxName>.Value
>> Jackson
>> "Chris Hastings" <chrshstngs@.comcast.net> wrote in message
>> news:2eOdne6G48T-ivTeRVn-jQ@.comcast.com...
>> Is it possible to get the value of a textbox from another textbox ? I
>> can't seem to find a way. What I am trying to do is sum a field that
>> is only listed on the report. Based on several factors there are
>> multiple rows not displayed and what I want to do is sum all of the
>> rows that are displayed. If I have a textbox called txtPrice, is there
>> a way to access that (like =txtPrice.text) in a different textbox and
>> then additionally do a =Sum(txtPrice.text).
>> Anyone?
>> Thanks!
>> Chris
>>
>>
>>
>
seem to find a way. What I am trying to do is sum a field that is only
listed on the report. Based on several factors there are multiple rows not
displayed and what I want to do is sum all of the rows that are displayed.
If I have a textbox called txtPrice, is there a way to access that (like
=txtPrice.text) in a different textbox and then additionally do a
=Sum(txtPrice.text).
Anyone?
Thanks!
ChrisTry
ReportItems!<textboxName>.Value
Jackson
"Chris Hastings" <chrshstngs@.comcast.net> wrote in message
news:2eOdne6G48T-ivTeRVn-jQ@.comcast.com...
> Is it possible to get the value of a textbox from another textbox ? I
> can't seem to find a way. What I am trying to do is sum a field that is
> only listed on the report. Based on several factors there are multiple
> rows not displayed and what I want to do is sum all of the rows that are
> displayed. If I have a textbox called txtPrice, is there a way to access
> that (like =txtPrice.text) in a different textbox and then additionally do
> a =Sum(txtPrice.text).
> Anyone?
> Thanks!
> Chris
>|||Hi Jackson,
Thanks! That answered my question, but unfortunately didn't solve my
problem.
I'm trying to get a sum of only fields that actually appear on the report
vs. fields that are in the entire dataset. Any ideas on that one?
Thanks!
Chris
"Jackson" <jackson_num5@.yahoo.com> wrote in message
news:uLHVad$3FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Try
> ReportItems!<textboxName>.Value
> Jackson
> "Chris Hastings" <chrshstngs@.comcast.net> wrote in message
> news:2eOdne6G48T-ivTeRVn-jQ@.comcast.com...
>> Is it possible to get the value of a textbox from another textbox ? I
>> can't seem to find a way. What I am trying to do is sum a field that is
>> only listed on the report. Based on several factors there are multiple
>> rows not displayed and what I want to do is sum all of the rows that are
>> displayed. If I have a textbox called txtPrice, is there a way to access
>> that (like =txtPrice.text) in a different textbox and then additionally
>> do a =Sum(txtPrice.text).
>> Anyone?
>> Thanks!
>> Chris
>>
>|||I think you can do that by adding a footer and in one of the footer fields
right click and go into expressions.
There you'll be able to sum up the fields that appear in the reports detail
area. So you can type this
=Sum(Fields!Field1.Value)+Sum(Fields!Field2.Value) etc.
Hope that helps,
Jackson
"Chris Hastings" <chrshstngs@.comcast.net> wrote in message
news:v5WdncrjzMToofTeRVn-pg@.comcast.com...
> Hi Jackson,
> Thanks! That answered my question, but unfortunately didn't solve my
> problem.
> I'm trying to get a sum of only fields that actually appear on the report
> vs. fields that are in the entire dataset. Any ideas on that one?
> Thanks!
> Chris
> "Jackson" <jackson_num5@.yahoo.com> wrote in message
> news:uLHVad$3FHA.1420@.TK2MSFTNGP09.phx.gbl...
>> Try
>> ReportItems!<textboxName>.Value
>> Jackson
>> "Chris Hastings" <chrshstngs@.comcast.net> wrote in message
>> news:2eOdne6G48T-ivTeRVn-jQ@.comcast.com...
>> Is it possible to get the value of a textbox from another textbox ? I
>> can't seem to find a way. What I am trying to do is sum a field that is
>> only listed on the report. Based on several factors there are multiple
>> rows not displayed and what I want to do is sum all of the rows that are
>> displayed. If I have a textbox called txtPrice, is there a way to access
>> that (like =txtPrice.text) in a different textbox and then additionally
>> do a =Sum(txtPrice.text).
>> Anyone?
>> Thanks!
>> Chris
>>
>>
>|||Hi again Jackson,
Thanks for taking the time.
A little background.
I have a query that joins multiple tables (I can't do subselects because my
ODBC driver doesn't support them), so for a given item that I want, I need
information from 4 other tables, for which I have to use joins. I group by
the main item and then use the First(field!..) to display the information
and I hide my detail line. By the way, one of the pieces of information I
want from another table is just a count, but in any case, there could be 1
record for an item or 1000, depending on the sub tables. It's annoying...
Anyway, the main item table has a price in it, and I want to sum all of the
prices for the items for a customer. The problem is that each item is
actual between 1 to 1000 actual records because of the other joins. So, my
Sums are completely goofy. The mental block I am trying to overcome is that
I can see exactly what I want to sum on the screen (because it's in a group
header and there are no detail lines) but I can't for the life of me figure
out a way to either sum the information that appears only on the report, or
sum pieces of the recordset based on other aggregate functions (like first
or count).
I'm contemplating forcing them to move their data to SQL, but I'm thinking
that is unlikely...
"Jackson" <jackson_num5@.yahoo.com> wrote in message
news:u%23Vke9$3FHA.2816@.tk2msftngp13.phx.gbl...
> I think you can do that by adding a footer and in one of the footer fields
> right click and go into expressions.
> There you'll be able to sum up the fields that appear in the reports
> detail area. So you can type this
> =Sum(Fields!Field1.Value)+Sum(Fields!Field2.Value) etc.
>
> Hope that helps,
> Jackson
>
> "Chris Hastings" <chrshstngs@.comcast.net> wrote in message
> news:v5WdncrjzMToofTeRVn-pg@.comcast.com...
>> Hi Jackson,
>> Thanks! That answered my question, but unfortunately didn't solve my
>> problem.
>> I'm trying to get a sum of only fields that actually appear on the report
>> vs. fields that are in the entire dataset. Any ideas on that one?
>> Thanks!
>> Chris
>> "Jackson" <jackson_num5@.yahoo.com> wrote in message
>> news:uLHVad$3FHA.1420@.TK2MSFTNGP09.phx.gbl...
>> Try
>> ReportItems!<textboxName>.Value
>> Jackson
>> "Chris Hastings" <chrshstngs@.comcast.net> wrote in message
>> news:2eOdne6G48T-ivTeRVn-jQ@.comcast.com...
>> Is it possible to get the value of a textbox from another textbox ? I
>> can't seem to find a way. What I am trying to do is sum a field that
>> is only listed on the report. Based on several factors there are
>> multiple rows not displayed and what I want to do is sum all of the
>> rows that are displayed. If I have a textbox called txtPrice, is there
>> a way to access that (like =txtPrice.text) in a different textbox and
>> then additionally do a =Sum(txtPrice.text).
>> Anyone?
>> Thanks!
>> Chris
>>
>>
>>
>
Tuesday, March 20, 2012
Access 2003 to SQL Server 2005 Express no hyperlink
I upgraded an Access 2003 database to SQL Server 2005 Express with the upsize wizard- worked great! Have a field that needs to be a hyperlink data type. There is no datatype in SQL that is hyperlink. Even if it were a text field, possibly inserting a hyperlink would work, but the insert hyplink in Access is unavailable, because the datasource is sql, maybe. I have a command button that ties to this function which won't work, obviously since it is not available. I need this field, it points to a file containing data that is being imported into the database. Huge piece of the database functionality. Making it easy for the user to find the file and using the vba to convert the data.
Saw the option of jump to url, but I don't think that will work? couldn't find the exact syntax either.
Any ideas would be greatly appreciated!
Robin
I guess you are talking about two different things, the database engine upgrade and the reporting upgrade. Which one is making your problems ? If I am wrong, please let me know, that we can help you fast with your problem.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Sorry, I didn't see it as two different things...didn't even realize I was talking about a reporting upgrade? I have a sql server 2005 backend - so the fields are in that database....I refer to all of those fields in the Access 2003 database and I need one of those fields to be a hyperlink field....it was a hyperlink field when the data was in Access and I need it to be a hyperlink field now. There must be a way to keep that consistency,right?
Thanks for your input....sorry if it was misinterpreted about what was going on.
Robin
|||There is no SQL Server equivalent to the Hyperlink field in Access, you will need to code a solution for following hyperlinks directly in your application. If your application is still in Access, you should explore the FollowHyperlink function. If you are moving to a Windows Forms application, you should check out the DataGridView, specifically the DataGridViewLinkColumns Namespace and the DataGridView.CellContentClick event to handle following the hyperlink.
As far as storing the hyperlink, I would suggest just using an nvarchar.
Mike
|||Mike, thanks for responding to my dilemma. I am going to keep the front end in Access so I'll check out the followhyperlink function. It was great with the insert hyperlink dialog box, but we'll have to work around that.
I'd also like to add, I've looked at a number of threads on this site and appreciate your input - you are to the point, explain things very well and make sure the person you are addressing gets the point. I was hoping you would respond to my issue because I knew you would answer it correctly.
Thanks again!
Take care
Robin
|||Mike, sorry to pick this up again, but is there any way for a user to easily find a file to insert into a field that is from a sql server be. I want them to be able to point to a file and have it copied into a field, insert hyperlink did it well. The field tells the database where to find a file\which file that is going to be imported into the database. Perhaps there is a more direct way in sql using the access front end?
Thanks!
Robin
Access 2003 iif function
Can someone tell me how i can create an iif function to do the following?
I need to look a field that has an amount in it. if it is 90K or less make i
t
1 if its 180 to 90001 make it 2 etc...
So for every 90K in that amount add 1..
I tried to do a VB code to create a ceiling funciton but Access keeps giving
me an error saying that my function is undefinded? Had a friend run my code
and it worked for him. I did update to the most current service pack and it
still errors out...
Can anyone tell me how I can make either the function work or how to do an
iif function to make it work?
Thank you so much for any help you can give me> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
> it
> 1 if its 180 to 90001 make it 2 etc...
SELECT val = CASE WHEN column < 90000 THEN 1
WHEN column < 180000 THEN 2
WHEN column < 270000 THEN 3
ELSE 4 END
FROM table
This is covered here (along with some other things that will help an Access
person moving to SQL Server):
http://www.aspfaq.com/2214|||Please repost this to a Access NG.
HTH
Jerry
"magikgb via webservertalk.com" <u15188@.uwe> wrote in message
news:566081edf8196@.uwe...
> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
> it
> 1 if its 180 to 90001 make it 2 etc...
> So for every 90K in that amount add 1..
> I tried to do a VB code to create a ceiling funciton but Access keeps
> giving
> me an error saying that my function is undefinded? Had a friend run my
> code
> and it worked for him. I did update to the most current service pack and
> it
> still errors out...
> Can anyone tell me how I can make either the function work or how to do an
> iif function to make it work?
> Thank you so much for any help you can give me|||ummmm... divide by 90k and add 1? Assuming integers, then:
select 89000 / 90000 + 1 -- = 1
select 90001 / 90000 + 1 -- = 2
select 810001 / 90000 + 1 -- = 10
Payson
magikgb via webservertalk.com wrote:
> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
it
> 1 if its 180 to 90001 make it 2 etc...
> So for every 90K in that amount add 1..
> I tried to do a VB code to create a ceiling funciton but Access keeps givi
ng
> me an error saying that my function is undefinded? Had a friend run my cod
e
> and it worked for him. I did update to the most current service pack and i
t
> still errors out...
> Can anyone tell me how I can make either the function work or how to do an
> iif function to make it work?
> Thank you so much for any help you can give me|||Thank you Aaron... I am new to VB is this VB code or just straight SQL?
Aaron Bertrand [SQL Server MVP] wrote:
>SELECT val = CASE WHEN column < 90000 THEN 1
> WHEN column < 180000 THEN 2
> WHEN column < 270000 THEN 3
> ELSE 4 END
>FROM table
>This is covered here (along with some other things that will help an Access
>person moving to SQL Server):
>http://www.aspfaq.com/2214|||This is T-SQL. You are posting to a SQL Server newsgroup. If you are
trying to write VB code or Access SQL, please post to a more appropriate
newsgroup. Then people won't waste their time providing you with useless
solutions...
"magikgb via webservertalk.com" <u15188@.uwe> wrote in message
news:5660b960e4dd5@.uwe...
> Thank you Aaron... I am new to VB is this VB code or just straight SQL?
> Aaron Bertrand [SQL Server MVP] wrote:|||Aaron.. Sorry new to this too...
Aaron Bertrand [SQL Server MVP] wrote:
>This is T-SQL. You are posting to a SQL Server newsgroup. If you are
>trying to write VB code or Access SQL, please post to a more appropriate
>newsgroup. Then people won't waste their time providing you with useless
>solutions...
>
>[quoted text clipped - 16 lines]
I need to look a field that has an amount in it. if it is 90K or less make i
t
1 if its 180 to 90001 make it 2 etc...
So for every 90K in that amount add 1..
I tried to do a VB code to create a ceiling funciton but Access keeps giving
me an error saying that my function is undefinded? Had a friend run my code
and it worked for him. I did update to the most current service pack and it
still errors out...
Can anyone tell me how I can make either the function work or how to do an
iif function to make it work?
Thank you so much for any help you can give me> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
> it
> 1 if its 180 to 90001 make it 2 etc...
SELECT val = CASE WHEN column < 90000 THEN 1
WHEN column < 180000 THEN 2
WHEN column < 270000 THEN 3
ELSE 4 END
FROM table
This is covered here (along with some other things that will help an Access
person moving to SQL Server):
http://www.aspfaq.com/2214|||Please repost this to a Access NG.
HTH
Jerry
"magikgb via webservertalk.com" <u15188@.uwe> wrote in message
news:566081edf8196@.uwe...
> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
> it
> 1 if its 180 to 90001 make it 2 etc...
> So for every 90K in that amount add 1..
> I tried to do a VB code to create a ceiling funciton but Access keeps
> giving
> me an error saying that my function is undefinded? Had a friend run my
> code
> and it worked for him. I did update to the most current service pack and
> it
> still errors out...
> Can anyone tell me how I can make either the function work or how to do an
> iif function to make it work?
> Thank you so much for any help you can give me|||ummmm... divide by 90k and add 1? Assuming integers, then:
select 89000 / 90000 + 1 -- = 1
select 90001 / 90000 + 1 -- = 2
select 810001 / 90000 + 1 -- = 10
Payson
magikgb via webservertalk.com wrote:
> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
it
> 1 if its 180 to 90001 make it 2 etc...
> So for every 90K in that amount add 1..
> I tried to do a VB code to create a ceiling funciton but Access keeps givi
ng
> me an error saying that my function is undefinded? Had a friend run my cod
e
> and it worked for him. I did update to the most current service pack and i
t
> still errors out...
> Can anyone tell me how I can make either the function work or how to do an
> iif function to make it work?
> Thank you so much for any help you can give me|||Thank you Aaron... I am new to VB is this VB code or just straight SQL?
Aaron Bertrand [SQL Server MVP] wrote:
>SELECT val = CASE WHEN column < 90000 THEN 1
> WHEN column < 180000 THEN 2
> WHEN column < 270000 THEN 3
> ELSE 4 END
>FROM table
>This is covered here (along with some other things that will help an Access
>person moving to SQL Server):
>http://www.aspfaq.com/2214|||This is T-SQL. You are posting to a SQL Server newsgroup. If you are
trying to write VB code or Access SQL, please post to a more appropriate
newsgroup. Then people won't waste their time providing you with useless
solutions...
"magikgb via webservertalk.com" <u15188@.uwe> wrote in message
news:5660b960e4dd5@.uwe...
> Thank you Aaron... I am new to VB is this VB code or just straight SQL?
> Aaron Bertrand [SQL Server MVP] wrote:|||Aaron.. Sorry new to this too...
Aaron Bertrand [SQL Server MVP] wrote:
>This is T-SQL. You are posting to a SQL Server newsgroup. If you are
>trying to write VB code or Access SQL, please post to a more appropriate
>newsgroup. Then people won't waste their time providing you with useless
>solutions...
>
>[quoted text clipped - 16 lines]
Access 2003 and SQL Server Express
Does anybody know if there is and update for access 2003 which could make it compatible with SQLServer Express. Actually XML Field are not supported, Diagram too, and surely much more.
Any help would be appreciate.
No, the special features are actually not supported. Only the SQL Server 2k compatible features are available.HTH, jens Suessmeyer.
http://www.sqlserver2005.de
Monday, March 19, 2012
Access - SQL 2005
Hi Guys/Girls...
I'm using SQL 2005 integration services to copy data from an existing access
database. The access datbase contains a field, with the type of NTEXT. I am
trying to write the corresponding data to a table with a nvarchar(8000)
field.
I'm also trying to use the 'Data Conversion' data flow transformation,
however I am unsure what to convert the NTEXT to? I've tried a variety of
supported types, however I always seem to get an error stipulating that the
output stream stipulated does not support the NTEXT conversion.
Has anybody tried anything like this yet?
Thanks,
Justin> I'm using SQL 2005 integration services to copy data from an existing
> access database. The access datbase contains a field, with the type of
> NTEXT.
Access does not have a data type called NTEXT. This is gong to be MEMO in
Access, and I think Integration Services is telling you to use NTEXT in SQL
Server. I am going to suggest you use NVARCHAR(MAX) as the destination data
type.
A|||Hi Aaron,
I do not wish to import into a NTEXT field in my SQL2005 database, but would
prefer that Integration Services do the implicit conversion to a
Varchar(8000) using the 'Data Conversion' data flow transformation.
Thanks,
Justin
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:enxdpaq%23FHA.984@.tk2msftngp13.phx.gbl...
> Access does not have a data type called NTEXT. This is gong to be MEMO in
> Access, and I think Integration Services is telling you to use NTEXT in
> SQL Server. I am going to suggest you use NVARCHAR(MAX) as the
> destination data type.
> A
>
I'm using SQL 2005 integration services to copy data from an existing access
database. The access datbase contains a field, with the type of NTEXT. I am
trying to write the corresponding data to a table with a nvarchar(8000)
field.
I'm also trying to use the 'Data Conversion' data flow transformation,
however I am unsure what to convert the NTEXT to? I've tried a variety of
supported types, however I always seem to get an error stipulating that the
output stream stipulated does not support the NTEXT conversion.
Has anybody tried anything like this yet?
Thanks,
Justin> I'm using SQL 2005 integration services to copy data from an existing
> access database. The access datbase contains a field, with the type of
> NTEXT.
Access does not have a data type called NTEXT. This is gong to be MEMO in
Access, and I think Integration Services is telling you to use NTEXT in SQL
Server. I am going to suggest you use NVARCHAR(MAX) as the destination data
type.
A|||Hi Aaron,
I do not wish to import into a NTEXT field in my SQL2005 database, but would
prefer that Integration Services do the implicit conversion to a
Varchar(8000) using the 'Data Conversion' data flow transformation.
Thanks,
Justin
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:enxdpaq%23FHA.984@.tk2msftngp13.phx.gbl...
> Access does not have a data type called NTEXT. This is gong to be MEMO in
> Access, and I think Integration Services is telling you to use NTEXT in
> SQL Server. I am going to suggest you use NVARCHAR(MAX) as the
> destination data type.
> A
>
Sunday, March 11, 2012
accept user from user to enter a field value for a sql
Hi-
and Parameters are used to provide the query with the parameter value that the user has input from a data entry screen. You do not necessarily need a stored procedure to accomplish this (although I believe that is the best way).
I am trying to achieve a similar query in ORACLE but donno how to do it in Ms SQL Server and MS Access (I have to do it for both these databases)
SELECT * FROM someTable
WHERE someField = &EnterValue
When this query is executed in ORACLE, it will ask user to enter value for &EnterValue and then execute the query using the user entered value.
Can this be done in MS SQL Server and MS ACCESS?
Please help...
ThanksHi-
Found the answer for MsAccess, I need to enter =[EnterValue] in Criteria for a given column.
Still looking for answer for MS SQL Server. Is Stored Proc the only answer?
Please help...
Best Wishes,|||SQL Server will not prompt the user for a parameter value. It needs to be supplied.
It is normally done like this:
SELECT * FROM someTable
WHERE someField = @.someValue
and Parameters are used to provide the query with the parameter value that the user has input from a data entry screen. You do not necessarily need a stored procedure to accomplish this (although I believe that is the best way).
Check out the ASP.NET tutorialServer-Side Data Access, especially the "Performing a Parameterized Select" section, to see how to use Parameters with ADO.NET.
Terri
Accentuated characters SQL
I use Webmatrix together with Frontpage 2002.
In one of my Webmatrix pages I refer to a field "Prénom" when doing queries.
In one of my Webmatrix pages I refer to a field "Prénom" when doing queries.
Sometimes after publishing these pages from my local to my hosted server the é disappears in the source code and SQL server does not find the column.
I have the feeling it has to do with codepage and/or Content.
Has anyone a precise idea.for a start, your columns should be of type nvarchar (or simlar n-prefixed column)
and literal strings in SQL should be n-prefixed also
UPDATE tblWhatever SET columnName = N'somé string with accénts'
this might help, if the problem is what I think it is.|||This is the case.
I have now realized that Frontpage produces pages with US/European character set, whereas Webmatrix uses UTF-8. Seems to be quite a mess making things compatible ...
Worse, when opening a Frontpage Ansi produced page with Web Matrix, this program strips out the accentuated characters, and you lose them in the process of saving.
Reverse, when you change the default in Frontpage to UTF-8, the european characters become unreadable in Web Matrix !
I do not yet know how to fix this.|||Frontpage?? Jesus H. Smith! I didn't think anyone still used that ;-)
1. what version? 2. is there anything in the options dialog which can force UTF-8 in FP?
Tuesday, March 6, 2012
about the type field : datetime and smalldatetime
hi all,
the field type :datetime and smalldatetime, i still can't understand.
everytime when i inserted the data to the db, i also get the error message"System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
i must change the field type tostring, so that i can insert data
my code
txt_datetime.text = '5/2/2006'
insert into datetime (datetime) values ('"& txt_datetime.text & "')"can anybody tell me the reason?
the field type :datetime and smalldatetime, i still can't understand.
everytime when i inserted the data to the db, i also get the error message"System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
i must change the field type tostring, so that i can insert data
my code
txt_datetime.text = '5/2/2006'
insert into datetime (datetime) values ('"& txt_datetime.text & "')"can anybody tell me the reason?
thank you!!In your SQL statement:
insert into datetime (datetime) values ('"& txt_datetime.text & "')"
Is your table named datetime? Isn't that a keyword? If that's not the problem, then can you post the source code where you are inserting the value?|||
What culture is your SQL Server running under?
I'm guessing this statement will fail as well:
SELECT CAST('5/2/2006' as datetime)
Try running that as a query.
But beyond that... Stop generating SQL Statements by using string concatenation and your problem will go away.
dim conn as new sqlconnection("{Connection string}")
conn.open
dim cmd as new sqlcommand("INSERT INTO [datetime](datetime) values (@.datetime)",conn)
cmd.parameters.add("@.datetime",sqldbtype.datetime).value='5/2/2006'
cmd.executenonquery
conn.close
|||
hi all,
first at all, thank you for reply
now i solved the problem and i think i know the reason, because the sql server and .net software are in difference language , sql server is tradition chinese version and .net software is in english version . the field type - datetime in sql server (english version ) is "dd/mm/yyyy" but in chinese is "mm/dd/yyyy" and when i insert the data to the db via the .net application, the datetime format is"dd/mm/yyyy", so i have to splite the datatime string and change it to "mm/dd/yyyy".
hijcasp,
my table name is called paper and the field name is deadline.
txt_deadline.text = '5/2/2006'
dim deadline_array = splite(txt_deadline.text, "/")
dim deadline as string
deadline = deadline_array.(1) & "/" & deadline_array(0) & "/" & deadline_array(2)
"insert into paper(deadline) values(" & deadline & ")"
hi,Motley
"Stop generating SQL Statements by using string concatenation ", why i should stop to use concatenation??
1) Because you run into problems like the one you mentioned.
2) Because string concatenating sql strings will suffer from SQL Injection attacks if you aren't really careful.
3) Performance.
4) Portability.
5) Maintanability.
6) Readability.
If at a later time you decide to change the SQL Server culture, your code breaks. If you decide to change your .NET culture, your code breaks. If you decide you want to localize your application to multiple cultures, your code breaks. The code I gave you runs no matter what culture your .NET appliction is, or what culture your SQL Server is, and runs faster and demands less resources of the SQL Server making it more scalable.
Just as a point of clarification, the issue wasn't that the language was different, it's that the culture formats of the .NET application and the SQL Server were different. The date format for en-US (English in the United States) is mm/dd/yy. The date format for en-GB (English in Great Britian) is dd/mm/yy. You had the right idea on the cause, just not the correct term. Cultures are made up of a language and a locale. The language part of the culture wasn't the problem, it's the locale part that determines what format dates, times, numbers, currency are in, and that's the part that caused you the problem.
Friday, February 24, 2012
About SQL field Type
Hi Friends
I have question.
I connected oracle and MS access tables(via Delphi7).
I wanna get type of field in indicated table. For example if it is
integer or boolean e.t.c
I have question.
I connected oracle and MS access tables(via Delphi7).
I wanna get type of field in indicated table. For example if it is
integer or boolean e.t.c
Please tell me the SQL function or whatever that retrievs type of field.
Thanks
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Ulan yrysbaev (ulasir@.yahoo.com) writes:
> Hi Friends
> I have question.
> I connected oracle and MS access tables(via Delphi7).
> I wanna get type of field in indicated table. For example if it is
> integer or boolean e.t.c
> Please tell me the SQL function or whatever that retrievs type of field.
Since you use different platforms, you should be using the
INFORMATION_SCHEMA views, which are part of the ANSI standard. But
I have no idea whether any of Access and Oracle supports them.
You are probably better off asking in comp.databases.ms-access and
comp.databases.oracle.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Thursday, February 16, 2012
About ODBC + VC++
Hi,
I can't get ntext field value from my database (or nvarchar).
If I write in my database in english, I get only the 2 first letters.
If I write in my database in hebrew, I get gibbrish letters.
please I need some help
I'm using sql serser 2000, CRecordset, CDatabase, ODBC
Code Snippet
CDBVariant val;
rs.GetFieldValue("activity",val);
CString activity = (*activityVal.m_pstring).GetBuffer(0);
I'm sorry but I've not used MFC very much, but I've used ODBC, and this looks to me like you're fetching Unicode characters into ANSI buffers. The first thing I would try is defining your application as a Unicode app by adding the following add the beginning of your code:
#define UNICODE
#define _UNICODE
Here's a link to using ODBC via MFC: http://msdn2.microsoft.com/en-us/library/s9ds2ktb(VS.80).aspx
Hopefully someone else on this alias is more familiar with MFC to help you out.
Good luck!
~Warren
|||Thank's,
Do you know how can I define these in my project? how to use this?
#define UNICODE
#define _UNICODE
Monday, February 13, 2012
About Indexes & Where caluse
Hi,
I am struggling with the sequence of parameters in my where clause.
In my databse table i have index on Broadcast_Date(some table field).The Index also include some other parameters which might become part of where clause.
The first field of the index is broadcast date.
So I want to know whether it is always compoulsory to have broadcast date as my first field of where clause.
Will it not scan the index if my where clause is starting with any other field which is also part of index.?
Will it hamper the query performance any way ?
Any help will really be appreciated.
Thanks
Vyanki
Hello,
the order of conditions in your where clause has no impact on index selection. Write in any way, it doesn't matter.
SQL chooses indexes(execution plan) based on the "logic" of your query and the information(statistics) that it available about the indexes that might be used to answer your query.
First, the SQL query is compiled into a "Query tree". This is a tree of algebraic operations, each of them has its corresponding SQL-counterpart.
Then, the query optimizer makes transformations of the query tree, and obtains new trees. These new trees are semantically equivalent to the initial tree, but they have different costs in terms execution.The cost of execution of each tree obtained is evaluated, based on index statistics. The one with the least cost is chosen as query plan.
Example. The query
select * from Table1 t1 join Table2 t2 on t1.fISN=t2.fISN join Table3 t3 on t2.fCODE=t3.fCODE
can be executed at least in 2 ways: first join Table2 and Table3, then join the resulting temporary table with Table1, or the other way around-join Table1 and Table2, then join the result with Table3. The optimiser considers both opportunities and chooses the "cheaper" one.
P.S. If it is permitted in this forum, I can post links to some books/external sources that cover this topic thoroughly.
|||No, the order is pretty much meaningless (it is completely meaningless until you have tons of joins and tons of search arguments (tons being a scientific term meaning a lot for the hardware you are using.)
Where broadcast_date = '20060101'
and other_column = 'othervalue'
is identical to:
Where other_column = 'othervalue'
and broadcast_date = '20060101'
because they are mathematically the same, the optimizer can choose to evaluate them in any order.
|||Yup. When you have really big number of tables and conditions involved in your query, there are many indexes that might be used to resolve the query, then the optimizer might consider not looking all possible execution plans. This is due to the number of such plans can be enormously big, and iterating through these plans means that a significant amount of time might be spent just to choose the appropriate plan.In such cases, the optimizer uses heuristics to prune the number of possible execution plans. These heuristics might be sensitive to the order of conditions etc. in the query code.|||
And I missed this part before:
P.S. If it is permitted in this forum, I can post links to some books/external sources that cover this topic thoroughly.
It is strongly encouraged to do this (as long as it isn't TOO self serving. Like just posting an answer that states "buy my book, it covers this" would seem wrong, but mentioning books, resources, etc that cover the topic along with some explanation is the best of all possiblities.) More things to read the better!
Of course linking to free resources as the answer to a question is often the best way to go to, there are a lot of good resources our there that answer the questions that have been asked by everyone at one time (me included :)
Louis
|||I think your question is the following:
index on columns (a, b)
what happens if the WHERE clause is:
WHERE b = 2
In this case, the index cannot be used to efficiently seek to the matching rows. The distribution statistics are maintained only for the primary column that is specified in a multi-column index. So you need to specify at least that to be able to use the index. Otherwise, SQL Server will pick a plan that will scan the entire index/table to find the matching rows. Additionally, you can specify some bogus condition like:
where a > 0 and b = 2
which will force the index to be used indirectly. But this might not be a good option since it will anyway result in scanning the entire index and the plan that scans the table directly might be more beneficial.
|||Hi ,
Its really good to see lots of responses. Thanks all.
So rading all the replies, I drawn a conclusion that, putting the sequence of where clause same as index will help if you have lots of join and lagrge table size.
Thanks & Rgds
vyanki
|||Change will to may...
...same as index may help if you have...
any you have it. And it is not the table size, it is the complexity of the query. table sizing is all taken care of by statistics which are very fast to work with. It is just that as you add more and more joins, the possible permutations of orders to evaluate criterium grows very large and unwieldly to check every possible combination. And the likely first candidate for an order of execution will be the order you code things in. If this expected cost is less than the expected cost of doing more optimizations, it might just be that the plan will coincide with the order of the tables.
All this to say, don't worry about ordering of where or join clauses for the most part. This is almost never an issue (but it is one of those fun facts that it is good to have in your head when you need it.)
|||If I read your question it is possible you are asking the question that Umachandar answered rahter than the one Louis answered. This is critical. If you have only a concatinated index and you are not querying agaist the first element in the concatination then the index will not be used at all.Also, the order of elements in the clauses of your SQL statement can alter the execution plan. In many cases the default plan is "good enough" but a hand tuned plan can have a huge performance impact. I have personally seen hand tuned SQL result in orders of magnitude response improvemnts.
|||
Hi,
Thanks for your suggestions.
But what I observed ,even if you have concateed index(having index on more than one field) the index get used when the first field in where clause is different than first field in index defination.
Thanks
Vyanki
|||Even if the index is used, it will be a scan not a seek. So you are essentially scanning the entire index (or table if it is clustered) to get the matching rows. You need to specify the primary column of the index to make use of efficient seeks.About Indexes & Where caluse
Hi,
I am struggling with the sequence of parameters in my where clause.
In my databse table i have index on Broadcast_Date(some table field).The Index also include some other parameters which might become part of where clause.
The first field of the index is broadcast date.
So I want to know whether it is always compoulsory to have broadcast date as my first field of where clause.
Will it not scan the index if my where clause is starting with any other field which is also part of index.?
Will it hamper the query performance any way ?
Any help will really be appreciated.
Thanks
Vyanki
Hello,
the order of conditions in your where clause has no impact on index selection. Write in any way, it doesn't matter.
SQL chooses indexes(execution plan) based on the "logic" of your query and the information(statistics) that it available about the indexes that might be used to answer your query.
First, the SQL query is compiled into a "Query tree". This is a tree of algebraic operations, each of them has its corresponding SQL-counterpart.
Then, the query optimizer makes transformations of the query tree, and obtains new trees. These new trees are semantically equivalent to the initial tree, but they have different costs in terms execution.The cost of execution of each tree obtained is evaluated, based on index statistics. The one with the least cost is chosen as query plan.
Example. The query
select * from Table1 t1 join Table2 t2 on t1.fISN=t2.fISN join Table3 t3 on t2.fCODE=t3.fCODE
can be executed at least in 2 ways: first join Table2 and Table3, then join the resulting temporary table with Table1, or the other way around-join Table1 and Table2, then join the result with Table3. The optimiser considers both opportunities and chooses the "cheaper" one.
P.S. If it is permitted in this forum, I can post links to some books/external sources that cover this topic thoroughly.
|||No, the order is pretty much meaningless (it is completely meaningless until you have tons of joins and tons of search arguments (tons being a scientific term meaning a lot for the hardware you are using.)
Where broadcast_date = '20060101'
and other_column = 'othervalue'
is identical to:
Where other_column = 'othervalue'
and broadcast_date = '20060101'
because they are mathematically the same, the optimizer can choose to evaluate them in any order.
|||Yup. When you have really big number of tables and conditions involved in your query, there are many indexes that might be used to resolve the query, then the optimizer might consider not looking all possible execution plans. This is due to the number of such plans can be enormously big, and iterating through these plans means that a significant amount of time might be spent just to choose the appropriate plan.In such cases, the optimizer uses heuristics to prune the number of possible execution plans. These heuristics might be sensitive to the order of conditions etc. in the query code.|||
And I missed this part before:
P.S. If it is permitted in this forum, I can post links to some books/external sources that cover this topic thoroughly.
It is strongly encouraged to do this (as long as it isn't TOO self serving. Like just posting an answer that states "buy my book, it covers this" would seem wrong, but mentioning books, resources, etc that cover the topic along with some explanation is the best of all possiblities.) More things to read the better!
Of course linking to free resources as the answer to a question is often the best way to go to, there are a lot of good resources our there that answer the questions that have been asked by everyone at one time (me included :)
Louis
|||I think your question is the following:
index on columns (a, b)
what happens if the WHERE clause is:
WHERE b = 2
In this case, the index cannot be used to efficiently seek to the matching rows. The distribution statistics are maintained only for the primary column that is specified in a multi-column index. So you need to specify at least that to be able to use the index. Otherwise, SQL Server will pick a plan that will scan the entire index/table to find the matching rows. Additionally, you can specify some bogus condition like:
where a > 0 and b = 2
which will force the index to be used indirectly. But this might not be a good option since it will anyway result in scanning the entire index and the plan that scans the table directly might be more beneficial.
|||Hi ,
Its really good to see lots of responses. Thanks all.
So rading all the replies, I drawn a conclusion that, putting the sequence of where clause same as index will help if you have lots of join and lagrge table size.
Thanks & Rgds
vyanki
|||Change will to may...
...same as index may help if you have...
any you have it. And it is not the table size, it is the complexity of the query. table sizing is all taken care of by statistics which are very fast to work with. It is just that as you add more and more joins, the possible permutations of orders to evaluate criterium grows very large and unwieldly to check every possible combination. And the likely first candidate for an order of execution will be the order you code things in. If this expected cost is less than the expected cost of doing more optimizations, it might just be that the plan will coincide with the order of the tables.
All this to say, don't worry about ordering of where or join clauses for the most part. This is almost never an issue (but it is one of those fun facts that it is good to have in your head when you need it.)
|||If I read your question it is possible you are asking the question that Umachandar answered rahter than the one Louis answered. This is critical. If you have only a concatinated index and you are not querying agaist the first element in the concatination then the index will not be used at all.Also, the order of elements in the clauses of your SQL statement can alter the execution plan. In many cases the default plan is "good enough" but a hand tuned plan can have a huge performance impact. I have personally seen hand tuned SQL result in orders of magnitude response improvemnts.
|||
Hi,
Thanks for your suggestions.
But what I observed ,even if you have concateed index(having index on more than one field) the index get used when the first field in where clause is different than first field in index defination.
Thanks
Vyanki
|||Even if the index is used, it will be a scan not a seek. So you are essentially scanning the entire index (or table if it is clustered) to get the matching rows. You need to specify the primary column of the index to make use of efficient seeks.Saturday, February 11, 2012
about Efficiency
I want to select one field from a table,but it should on some conditions
which refer to 5 table ,such as A.FILED1=B.FIELD1 AND B.FIELD2=C.FIELD3 AND
...
Should I use case "select sum(a.amount) from a,b,c,... where
a.field1=b.field1 and b.field2=c.field2 and ..." or "select sum(a.amount)
from select b.field1 from select c.field2 from..."?And which case is more
efficiency?
thanks!
I cannot make much sense of this. Please rephrase your question and
especially provide more detail (DDL, DML).
select sum(a.amount)
from a
join b on a.field1 = b.field1
join c on b.field2 = c.field2
...
which refer to 5 table ,such as A.FILED1=B.FIELD1 AND B.FIELD2=C.FIELD3 AND
...
Should I use case "select sum(a.amount) from a,b,c,... where
a.field1=b.field1 and b.field2=c.field2 and ..." or "select sum(a.amount)
from select b.field1 from select c.field2 from..."?And which case is more
efficiency?
thanks!
?е??ε?????? в??????select ..from ...where ..and ..and..and ..and ..select ..from
select ..from select ..from .....?Ч??
ллOn 20.09.2006 10:51, yicong wrote:
Quote:
Originally Posted by
I want to select one field from a table,but it should on some conditions
which refer to 5 table ,such as A.FILED1=B.FIELD1 AND B.FIELD2=C.FIELD3 AND
...
Should I use case "select sum(a.amount) from a,b,c,... where
a.field1=b.field1 and b.field2=c.field2 and ..." or "select sum(a.amount)
from select b.field1 from select c.field2 from..."?And which case is more
efficiency?
I cannot make much sense of this. Please rephrase your question and
especially provide more detail (DDL, DML).
A general remark: if you have two equivalent DML statements you can
easily check with MS tools (Profiler, Query Analyzer) which of the two
is more efficient.
Regards
robert|||yicong wrote:
Quote:
Originally Posted by
I want to select one field from a table,but it should on some conditions
which refer to 5 table ,such as A.FILED1=B.FIELD1 AND B.FIELD2=C.FIELD3 AND
...
Should I use case "select sum(a.amount) from a,b,c,... where
a.field1=b.field1 and b.field2=c.field2 and ..." or "select sum(a.amount)
from select b.field1 from select c.field2 from..."?And which case is more
efficiency?
select sum(a.amount)
from a
join b on a.field1 = b.field1
join c on b.field2 = c.field2
...
About Dts
Hi everybody,
I am new to the world of DTS in SQL.. Can anybody help me out to get good notes on DTS or help that you can provide me in this field.. I have to complete a project in DTS.
Thanks:angel:Start from this..
http://www.databasejournal.com/features/mssql/article.php/3086891
http://www.databasejournal.com/features/mssql/article.php/3579941
1. Use Books OnLine from SQL Query Analyzer.
2. Use google & search what you want.|||Also http://www.sqldts.com/|||try this sites
1. www.sqldts.com|||Is there an echo in here? ;)
HEEELLLLOOOOOOOOOOOOOOOO!!!!!!!!!!
(heellll0000000000)
(hellloooooo)|||OK, so why do you have to do a "project" in DTS?
Doesn't manglement know any better?
I am new to the world of DTS in SQL.. Can anybody help me out to get good notes on DTS or help that you can provide me in this field.. I have to complete a project in DTS.
Thanks:angel:Start from this..
http://www.databasejournal.com/features/mssql/article.php/3086891
http://www.databasejournal.com/features/mssql/article.php/3579941
1. Use Books OnLine from SQL Query Analyzer.
2. Use google & search what you want.|||Also http://www.sqldts.com/|||try this sites
1. www.sqldts.com|||Is there an echo in here? ;)
HEEELLLLOOOOOOOOOOOOOOOO!!!!!!!!!!
(heellll0000000000)
(hellloooooo)|||OK, so why do you have to do a "project" in DTS?
Doesn't manglement know any better?
Thursday, February 9, 2012
About Check Constraint
Dear all,
I know Check Constraint can help stop some unwanted data pattern insert into
the field.
It can also return a system message about this.
How can I modify the check constraint return system message to a more user
friendly message in SQL Server?
IvanOn 14 Jun, 05:33, "Ivan" <i...@.microsoft.com> wrote:
> Dear all,
> I know Check Constraint can help stop some unwanted data pattern insert into
> the field.
> It can also return a system message about this.
> How can I modify the check constraint return system message to a more user
> friendly message in SQL Server?
> Ivan
Use a TRY / CATCH block in your procedure to catch the error and
handle it accordingly.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Are TRY & CATCH SQL command?
Ivan
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1181802475.387039.108510@.d30g2000prg.googlegroups.com...
> On 14 Jun, 05:33, "Ivan" <i...@.microsoft.com> wrote:
>> Dear all,
>> I know Check Constraint can help stop some unwanted data pattern insert
>> into
>> the field.
>> It can also return a system message about this.
>> How can I modify the check constraint return system message to a more
>> user
>> friendly message in SQL Server?
>> Ivan
> Use a TRY / CATCH block in your procedure to catch the error and
> handle it accordingly.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||> Are TRY & CATCH SQL command?
Yes, introduced in SQL Server 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ivan" <ivan@.microsoft.com> wrote in message news:eq1CIi7rHHA.1848@.TK2MSFTNGP03.phx.gbl...
> Are TRY & CATCH SQL command?
> Ivan
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
> '?:1181802475.387039.108510@.d30g2000prg.googlegroups.com...
>> On 14 Jun, 05:33, "Ivan" <i...@.microsoft.com> wrote:
>> Dear all,
>> I know Check Constraint can help stop some unwanted data pattern insert
>> into
>> the field.
>> It can also return a system message about this.
>> How can I modify the check constraint return system message to a more
>> user
>> friendly message in SQL Server?
>> Ivan
>> Use a TRY / CATCH block in your procedure to catch the error and
>> handle it accordingly.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>
I know Check Constraint can help stop some unwanted data pattern insert into
the field.
It can also return a system message about this.
How can I modify the check constraint return system message to a more user
friendly message in SQL Server?
IvanOn 14 Jun, 05:33, "Ivan" <i...@.microsoft.com> wrote:
> Dear all,
> I know Check Constraint can help stop some unwanted data pattern insert into
> the field.
> It can also return a system message about this.
> How can I modify the check constraint return system message to a more user
> friendly message in SQL Server?
> Ivan
Use a TRY / CATCH block in your procedure to catch the error and
handle it accordingly.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Are TRY & CATCH SQL command?
Ivan
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1181802475.387039.108510@.d30g2000prg.googlegroups.com...
> On 14 Jun, 05:33, "Ivan" <i...@.microsoft.com> wrote:
>> Dear all,
>> I know Check Constraint can help stop some unwanted data pattern insert
>> into
>> the field.
>> It can also return a system message about this.
>> How can I modify the check constraint return system message to a more
>> user
>> friendly message in SQL Server?
>> Ivan
> Use a TRY / CATCH block in your procedure to catch the error and
> handle it accordingly.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||> Are TRY & CATCH SQL command?
Yes, introduced in SQL Server 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ivan" <ivan@.microsoft.com> wrote in message news:eq1CIi7rHHA.1848@.TK2MSFTNGP03.phx.gbl...
> Are TRY & CATCH SQL command?
> Ivan
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
> '?:1181802475.387039.108510@.d30g2000prg.googlegroups.com...
>> On 14 Jun, 05:33, "Ivan" <i...@.microsoft.com> wrote:
>> Dear all,
>> I know Check Constraint can help stop some unwanted data pattern insert
>> into
>> the field.
>> It can also return a system message about this.
>> How can I modify the check constraint return system message to a more
>> user
>> friendly message in SQL Server?
>> Ivan
>> Use a TRY / CATCH block in your procedure to catch the error and
>> handle it accordingly.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>
Subscribe to:
Posts (Atom)