anyone can give solution for this problem

2004-08-21 11:52:38.64 spid52 Error: 0, Severity: 19,
State: 0
2004-08-21 11:52:38.64 spid52 language_exec: Process 52
generated an access violation. SQL Server is terminating
this process..
2004-08-21 11:56:12.93 spid52 Using 'sqlimage.dll'
version '4.0.5'
Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQL00062.dmp
2004-08-21 11:56:12.94 spid52 Error: 0, Severity: 19,
State: 0
2004-08-21 11:56:12.94 spid52 SqlDumpExceptionHandler:
Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
process..
************************************************** *********
********************
*
* BEGIN STACK DUMP:
* 08/21/04 11:56:12 spid 52
*
* Exception Address = 0055AB2F
(COpArg::DeriveNormalizedGroupProperties(class CTreeHandle
*) + 0000001E Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 4088 bytes -
* SELECT glmedium.module_id,
glmedium.vouchertype,
* glmedium.vouchernumber,
glmedium.voucherdate,
* glmedium.accountcode,
glmedium.amountdebit,
* glmedium.amountcredit,
glmedium.jobid, cstcon
* tract.ContractName,
glaccounts.AccountName , gljobwisep
* rofit.groupcode ,
glmedium.accountremarks,
glmedium.partycode,
* arcustomer.customername,
contractvalue,provisionamt FROM glm
* edium, cstcontract,
glaccounts, gljobwisep
* rofit, arcustomer WHERE (
glmedium.company_id = cstcontract.Co
* mpany_id ) and ( glmedium.jobid =
cstcontract.ContractNo ) a
* nd ( glmedium.company_id =
glaccounts.Company_id ) and
* ( glmedium.accountcode =
glaccounts.AccountCode ) AND VOUCHER
* TYPE NOT IN ('GI','SR','DN','DJ') and
glmedium.accountcode = gljobwi
* seprofit.accountcode and
glmedium.company_id = arcustomer.company_i
* d and partycode =
arcustomer.customercode and
glmedium.company_i
* d = '05' and
glmedium.voucherdate >= '1-JAN-2000' and
glmedium.
* voucherdate <= '31-DEC-2004' and
glmedium.jobid >= '0' and
glmed
* ium.jobid <= 'ZZZZZZZZ' UNION SELECT
glmedium.module_id,
* glmedium.vouchertype,
glmedium.vouchernumber,
* glmedium.voucherdate,
glmedium.accountcode,
* glmedium.amountdebit, 0,
glmedium.jobid,
* cstcontract.ContractName,
glaccounts.AccountName
* , 'ZI' ,
glmedium.accountremarks,
glmedium.partycode,
* arcustomer.customername,
contractvalue,provisionamt FROM glmedi
* um, cstcontract, glaccounts,
arcustomer
* WHERE ( glmedium.company_id =
cstcontract.Company_id ) and
* ( glmedium.jobid = cstcontract.ContractNo )
and ( glmed
* ium.company_id = glaccounts.Company_id )
and ( glmedium.acco
* untcode = glaccounts.Account
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B19FFF
0071a000
* ntdll 77F80000 77FFCFFF
0007d000
* KERNEL32 7C570000 7C627FFF
000b8000
* ADVAPI32 7C2D0000 7C331FFF
00062000
* RPCRT4 77D30000 77DA0FFF
00071000
* USER32 77E10000 77E74FFF
00065000
* GDI32 77F40000 77F7DFFF
0003e000
* OPENDS60 41060000 41065FFF
00006000
* MSVCRT 78000000 78044FFF
00045000
* UMS 41070000 4107CFFF
0000d000
* SQLSORT 42AE0000 42B6FFFF
00090000
* MSVCIRT 780A0000 780B1FFF
00012000
* sqlevn70 41080000 41086FFF
00007000
* NETAPI32 75170000 751BEFFF
0004f000
* Secur32 7C340000 7C34EFFF
0000f000
* NTDSAPI 77BF0000 77C00FFF
00011000
* DNSAPI 77980000 779A3FFF
00024000
* WSOCK32 75050000 75057FFF
00008000
* WS2_32 75030000 75043FFF
00014000
* WS2HELP 75020000 75027FFF
00008000
* WLDAP32 77950000 77979FFF
0002a000
* NETRAP 751C0000 751C5FFF
00006000
* SAMLIB 75150000 7515EFFF
0000f000
* wmi 76110000 76113FFF
00004000
* SSNETLIB 42CF0000 42D05FFF
00016000
* SSNMPN70 410D0000 410D5FFF
00006000
* security 75500000 75503FFF
00004000
* crypt32 7C740000 7C7C6FFF
00087000
* MSASN1 77430000 7743FFFF
00010000
* userenv 7C0F0000 7C150FFF
00061000
* rnr20 782C0000 782CBFFF
0000c000
* iphlpapi 77340000 77352FFF
00013000
* ICMP 77520000 77524FFF
00005000
* MPRAPI 77320000 77336FFF
00017000
* OLE32 77A50000 77B3EFFF
000ef000
* OLEAUT32 779B0000 77A4AFFF
0009b000
* ACTIVEDS 773B0000 773DEFFF
0002f000
* ADSLDPC 77380000 773A2FFF
00023000
* RTUTILS 77830000 7783DFFF
0000e000
* SETUPAPI 77880000 7790DFFF
0008e000
* RASAPI32 774E0000 77512FFF
00033000
* RASMAN 774C0000 774D0FFF
00011000
* TAPI32 77530000 77551FFF
00022000
* COMCTL32 71710000 71793FFF
00084000
* SHLWAPI 70A70000 70AD4FFF
00065000
* DHCPCSVC 77360000 77378FFF
00019000
* winrnr 777E0000 777E7FFF
00008000
* rasadhlp 777F0000 777F4FFF
00005000
* msafd 74FD0000 74FEDFFF
0001e000
* wshtcpip 75010000 75016FFF
00007000
* SSmsLPCn 42CD0000 42CD6FFF
00007000
* SQLFTQRY 41020000 4103CFFF
0001d000
* CLBCATQ 775A0000 7762FFFF
00090000
* SQLOLEDB 75370000 753E7FFF
00078000
* MSDART 2AD60000 2AD82FFF
00023000
* VERSION 77820000 77826FFF
00007000
* LZ32 759B0000 759B5FFF
00006000
* comdlg32 76B30000 76B6DFFF
0003e000
* SHELL32 782F0000 78534FFF
00245000
* MSDATL3 2AD90000 2ADA5FFF
00016000
* oledb32 2B0B0000 2B11EFFF
0006f000
* OLEDB32R 2B120000 2B130FFF
00011000
* rsabase 7CA00000 7CA22FFF
00023000
* xpstar 410F0000 41133FFF
00044000
* SQLUNIRL 41090000 410BCFFF
0002d000
* WINSPOOL 77800000 7781DFFF
0001e000
* MPR 76620000 7662FFFF
00010000
* SQLRESLD 42AC0000 42AC6FFF
00007000
* SQLSVC 42C40000 42C56FFF
00017000
* ODBC32 2B150000 2B184FFF
00035000
* odbcbcp 41150000 41156FFF
00007000
* W95SCM 41140000 4114BFFF
0000c000
* NDDEAPI 769A0000 769A6FFF
00007000
* odbcint 2B290000 2B2A5FFF
00016000
* clusapi 73930000 7393FFFF
00010000
* resutils 689D0000 689DCFFF
0000d000
* SQLSVC 43970000 43975FFF
00006000
* xpstar 439E0000 439EBFFF
0000c000
* msv1_0 2B600000 2B620FFF
00021000
* srchadm 60000000 60042FFF
00043000
* mssws 2B850000 2B858FFF
00009000
* athprxy 2BBB0000 2BBB7FFF
00008000
* DBGHELP 2BCF0000 2BD02FFF
00013000
* msdbi 6BE90000 6BEABFFF
0001c000
* sqlimage 4A400000 4A40CFFF
0000d000
*
* Edi: 00000000:
* Esi: 1D9F6B50: 00982848 00000002 209B8030
1DA15268 1DA12690 00000000
* Eax: 00000000:
* Ebx: 00000000:
* Ecx: 00000000:
* Edx: 2A88C228: 00000124 20940168 2090D5D0
208FE358 1D9B9838 20940168
* Eip: 0055AB2F: 50FF038B D8F74810 8940C01B
1375EC45 5FF44D8B 5B5EC38B
* Ebp: 2A88BD5C: 2A88BED0 0055A7BB 2A88BD74
00000000 1D9B9A48 00000000
* SegCs: 0000001B:
* EFlags: 00010246: 0061006A 00610076 0063005C
0061006C 00730073 00730065
* Esp: 2A88BD1C: 00000000 1D9F6B50 00000000
2A88BD38 0040204E 209B7400
* SegSs: 00000023:
************************************************** *********
********************
Short Stack Dump
0055AB2F Module(sqlservr+0015AB2F)
(COpArg::DeriveNormalizedGroupProperties(class CTreeHandle
*)+0000001E)
0055A7BB Module(sqlservr+0015A7BB)
(COptExpr::DeriveGroupProperties(unsigned long)+000000B3)
0055A765 Module(sqlservr+0015A765)
(COptExpr::DeriveGroupProperties(unsigned long)+0000005D)
0048BEA2 Module(sqlservr+0008BEA2)
(CImpRuleBaseJoinToIdxLookup::BuildSubstitutes(cla ss
COptExpr *,class CRuleContext *,class CRuleReturn *)
+00000ECC)
00589104 Module(sqlservr+00189104)
(CTask_ApplyRule::Perform(int)+00000268)
0058C939 Module(sqlservr+0018C939) (CMemo::ExecuteTasks
(class COptTask *,int,int)+0000014D)
0058CF2D Module(sqlservr+0018CF2D) (CMemo::OptimizeQuery
(class CQuery *,class COptExpr *,double *,int,int,struct
s_OptimPlans *)+0000051A)
0058CADF Module(sqlservr+0018CADF)
(COptContext::PexprSearchPlan(class COptExpr *)+00000155)
0055E100 Module(sqlservr+0015E100)
(COptContext::PcxteOptimizeQuery(class COptExpr *,class
DRgCId *)+00000B7A)
0055FFBB Module(sqlservr+0015FFBB) (CQuery::Optimize(void)
+00000416)
0055FD54 Module(sqlservr+0015FD54) (CQuery::Optimize
(unsigned long)+00000030)
005642C6 Module(sqlservr+001642C6) (CCvtTree::PqryFromTree
(class TREE *,class IMemObj *,class CRangeCollection
*,unsigned long,class CCompPlan *)+000002C4)
00564019 Module(sqlservr+00164019) (BuildQueryFromTree
(class TREE *,class IMemObj *,class IMemObj *,class
IQueryObj * *,class CRangeCollection *,unsigned long,class
CCompPlan *)+00000046)
00563F78 Module(sqlservr+00163F78) (CStmtQuery::InitQuery
(class CAlgStmt *,class CCompPlan *,unsigned long)
+0000014B)
0049DA48 Module(sqlservr+0009DA48) (CStmtSelect::Init
(class CAlgStmt *,class CCompPlan *,class IBrowseMode *)
+00000091)
00447078 Module(sqlservr+00047078) (CCompPlan::FCompileStep
(class CAlgStmt *,class CStatement * *)+00000AE7)
004510FE Module(sqlservr+000510FE) (CProchdr::FCompile
(class CCompPlan *,class CParamExchange *)+00000D15)
00415080 Module(sqlservr+00015080) (CSQLSource::FTransform
(class CParamExchange *)+0000037C)
004592CE Module(sqlservr+000592CE) (CSQLStrings::FTransform
(class CParamExchange *)+000001A8)
0041534F Module(sqlservr+0001534F) (CSQLSource::Execute
(class CParamExchange *)+00000176)
00459A54 Module(sqlservr+00059A54) (language_exec(struct
srv_proc *)+000003C8)
004175D8 Module(sqlservr+000175D8) (process_commands
(struct srv_proc *)+000000E0)
410735D0 Module(UMS+000035D0) (ProcessWorkRequests(class
UmsWorkQueue *)+00000264)
4107382C Module(UMS+0000382C) (ThreadStartRoutine(void *)
+000000BC)
78008454 Module(MSVCRT+00008454) (_endthread+000000C1)
7C57438B Module(KERNEL32+0000438B) (TlsSetValue+000000F0)
2004-08-21 11:56:16.43 spid52 Error: 0, Severity: 19,
State: 0
2004-08-21 11:56:16.43 spid52 language_exec: Process 52
generated an access violation. SQL Server is terminating
this process..
You may want to contact PSS with details on how this occured:
http://support.microsoft.com/default...s;Prodoffer41a
( If this is due to a bug, you may get your money refunded )
Anith

Any way to optimize sp_sproc_columns

We just upgraded from SQL 6.5 to SQL 2000. We also have moved SQL Server to
a much beefier machine. Actual data access in our database is much faster,
but we've noticed that the speed of the application itself has slowed down
some because sp_sproc_columns now seems to take quite a bit longer to run
than it did in 6.5. It's really apparent in portions of our app where we
issue several consecutive calls to the DB in rapid succession. We're using
RDO to access the database, and right now changing that is not a viable
option. Does anyone have any ideas how to either tweak sp_sproc_columns, or
replace it with something more efficient?
Thanks in advance,
BrettMaybe you could adapt something like this?
http://www.aspfaq.com/2463
"Brett" <do.not.spam.me.you.jerks.maelstrom1335.NO.s.p.a.m@.swbell.net> wrote
in message news:Om6k29BYDHA.1748@.TK2MSFTNGP12.phx.gbl...
> We just upgraded from SQL 6.5 to SQL 2000. We also have moved SQL Server
to
> a much beefier machine. Actual data access in our database is much
faster,
> but we've noticed that the speed of the application itself has slowed down
> some because sp_sproc_columns now seems to take quite a bit longer to run
> than it did in 6.5. It's really apparent in portions of our app where we
> issue several consecutive calls to the DB in rapid succession. We're
using
> RDO to access the database, and right now changing that is not a viable
> option. Does anyone have any ideas how to either tweak sp_sproc_columns,
or
> replace it with something more efficient?
> Thanks in advance,
> Brett
>

Any way to generate SQL Script for application role?

Is there any way to generate the SQL to create an application role and all t
he associated grants for table and stored procedure access? Or does anyone h
ave a suggestion for how to migrate an application role from one environment
to another? The developers
originally create the role by clicking on things.The easiest way is to script the existing application role permissions
and edit the script for the new environment. See sp_addapprole in SQL
BOL for the syntax for creating a new application role.
--Mary
On Fri, 16 Apr 2004 10:01:10 -0700, Charlotte
<anonymous@.discussions.microsoft.com> wrote:

>Is there any way to generate the SQL to create an application role and all the asso
ciated grants for table and stored procedure access? Or does anyone have a suggestio
n for how to migrate an application role from one environment to another? The develo
per
s originally create the role by clicking on things.sql

Any way to disable SQL logins after failed tries?

We're using a canned app that only uses SQL Server logins (it will NOT use Windows logins to access SQL Server)

SQL Server logins seem to lack even the most rudimentary security features such as expiring passwords and automatic disabling after a set number of failed logins. Bad. Bad Microsoft.

Has anyone figured out a way to graft this on after-the-fact?

I can do it in an awkward fashion by auditing failed logins and going back to read the error log, but this isn't real time by any stretch.To the best of my knowledge, there is no innate feature in MS SQL that will allow you manage SQL Server logins as you wish. This lack has been noted before (cross your fingers, MS will deal with this in Yukon).

We wrote a custom app and created this kind of failed-login checking, but that does not sound like an option for you.

Can you do something with a scheduled job at the SQL error log (you'd have to enable logging of failed login attempts)?

I think there is a way to load the error log as a table. Then you could search for failed logins and (if the number exceeded your threshold) disable it.

I think it's doable, but I have not had the need to accomplish this particular task.

Regards,

hmscott|||Originally posted by hmscott
To the best of my knowledge, there is no innate feature in MS SQL that will allow you manage SQL Server logins as you wish. This lack has been noted before (cross your fingers, MS will deal with this in Yukon).

We wrote a custom app and created this kind of failed-login checking, but that does not sound like an option for you.

Can you do something with a scheduled job at the SQL error log (you'd have to enable logging of failed login attempts)?

I think there is a way to load the error log as a table. Then you could search for failed logins and (if the number exceeded your threshold) disable it.

I think it's doable, but I have not had the need to accomplish this particular task.

Regards,

hmscott

I doubt it...since SQL Server "security" is not the way to go...you can "See" passwords as plain as day...oh, I forgot...M$ describes this as a feature....

Any valid login can access Enterprise Manager

Hi.
When creating a SQL Server2000 login (NT Authen) with read-only rights to
user tables in a user database, this very same login can:
1. Login into EM
2. Though cannot change any objects, but can
- 1. view all system objects (logins, DTS etc)
3. STOP SQL Server Agent
4. RESTART SQL SERVER!!!!
This all seem to be traced back to the fact every login is a member of the
PUBLIC role, and the PUBLIC role allow u to do all of the above!!!
Can anyone tell me how to:
1. Prevent user (not DBA, DBO's etc) login into EM?
2. Prevent user login into QA?
Cheers!> 2. Though cannot change any objects, but can
> - 1. view all system objects (logins, DTS etc)
You can disable the msdb guest user (EXEC msdb..sp_dropuser 'guest') to
prevent access to msdb. This will prevent viewing DTS packages. See
http://support.microsoft.com/defaul...b;en-us;282463.
You can 'REVOKE SELECT FROM syslogins' to prevent non privileged users from
enumerating logins via EM.

> 3. STOP SQL Server Agent
> 4. RESTART SQL SERVER!!!!
The ability to stop and start services is controlled through Windows
permissions, not SQL Server security. If the account is a member of the
Windows 'Administrators' or 'Power Users' groups, then the user can stop and
start services using any tool or command. EM will not allow non-privileged
users to stop/start services.
Hope this helps.
Dan Guzman
SQL Server MVP
"Oddie" <Oddie@.discussions.microsoft.com> wrote in message
news:2DB045CF-4B2F-4493-BEB5-FA684D5800A1@.microsoft.com...
> Hi.
> When creating a SQL Server2000 login (NT Authen) with read-only rights to
> user tables in a user database, this very same login can:
> 1. Login into EM
> 2. Though cannot change any objects, but can
> - 1. view all system objects (logins, DTS etc)
> 3. STOP SQL Server Agent
> 4. RESTART SQL SERVER!!!!
> This all seem to be traced back to the fact every login is a member of the
> PUBLIC role, and the PUBLIC role allow u to do all of the above!!!
> Can anyone tell me how to:
> 1. Prevent user (not DBA, DBO's etc) login into EM?
> 2. Prevent user login into QA?
> Cheers!|||Thks Dan - it sure works - but still no way of preventing a valid SQL Login
to access other objects on EM or seeing them using other tools (such as
Visual Studio).
Thks again!
"Dan Guzman" wrote:

> You can disable the msdb guest user (EXEC msdb..sp_dropuser 'guest') to
> prevent access to msdb. This will prevent viewing DTS packages. See
> http://support.microsoft.com/defaul...b;en-us;282463.
> You can 'REVOKE SELECT FROM syslogins' to prevent non privileged users fro
m
> enumerating logins via EM.
>
> The ability to stop and start services is controlled through Windows
> permissions, not SQL Server security. If the account is a member of the
> Windows 'Administrators' or 'Power Users' groups, then the user can stop a
nd
> start services using any tool or command. EM will not allow non-privilege
d
> users to stop/start services.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oddie" <Oddie@.discussions.microsoft.com> wrote in message
> news:2DB045CF-4B2F-4493-BEB5-FA684D5800A1@.microsoft.com...
>
>|||By default, SQL 2000 users can read catalog meta data in those databases
they have permissions to access. It's possible to revoke public permissions
from some of the catalog objects but this can break data access API's so
proceed at your own risk. SQL 2005 provides more control over meta data
access.
Hope this helps.
Dan Guzman
SQL Server MVP
"Oddie" <Oddie@.discussions.microsoft.com> wrote in message
news:5D081158-8F2B-428B-ABE2-32892258C3C0@.microsoft.com...[vbcol=seagreen]
> Thks Dan - it sure works - but still no way of preventing a valid SQL
> Login
> to access other objects on EM or seeing them using other tools (such as
> Visual Studio).
> Thks again!
> "Dan Guzman" wrote:
>|||Thks Dan for all your help!
"Dan Guzman" wrote:

> By default, SQL 2000 users can read catalog meta data in those databases
> they have permissions to access. It's possible to revoke public permissio
ns
> from some of the catalog objects but this can break data access API's so
> proceed at your own risk. SQL 2005 provides more control over meta data
> access.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oddie" <Oddie@.discussions.microsoft.com> wrote in message
> news:5D081158-8F2B-428B-ABE2-32892258C3C0@.microsoft.com...
>
>

any user access audit programs available?

Hello all, does anyone know of a SS2005RS user audit program that an administrator can run on a RS server to show which userids have access to folders? I have in mind a pgm that would show:

folder users

Home user01, user02, user03

folderA user01,user02, user05

folderB user02, user06

Is there a pgm available as a download, or does someone have a home-grown pgm whose source they would let out?

Has anyone else faced this need?

Thanks in advance

I don't know of such a program but writing your own shouldn't be that difficult. You can call ReportingService2005.GetPolicies method to obtain the secuity polices per folder.|||Mr. Lachev, thanks so much, I'll do that. P.S. I like your website.

Any suggestions on this error when trying to connect SQL Server 2000?

SQL State: 08001Native Error: 17
State: 1Severity: 16
SQL Server Message: [DBNETLIB][ConnectionOpen (Connect()).]
SQL Server does not exist or access denied.
Hi,
Possibilities for this error are:-
1. SQL Server service may be down or hung
2. Network issue
3. Need to create an Alias using "Client network utility" specifying IP
address and port number. Use this alias to connect.
Thanks
Hari
MCDBA
"zenDebra" <anonymous@.discussions.microsoft.com> wrote in message
news:addd01c436ac$ba6088e0$a601280a@.phx.gbl...
> SQL State: 08001 Native Error: 17
> State: 1 Severity: 16
> SQL Server Message: [DBNETLIB][ConnectionOpen (Connect()).]
> SQLeat Server does not exist or access denied.
|||Have a look at
Potential causes of the "SQL Server Does Not Exist or Access Denied" error
message
http://support.microsoft.com/default...;en-us;Q328306
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"zenDebra" <anonymous@.discussions.microsoft.com> wrote in message
news:addd01c436ac$ba6088e0$a601280a@.phx.gbl...
> SQL State: 08001 Native Error: 17
> State: 1 Severity: 16
> SQL Server Message: [DBNETLIB][ConnectionOpen (Connect()).]
> SQL Server does not exist or access denied.
|||Try creating an ODBC DSN to the server and post the entire Error message.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Any resolution for this error

Error: 0, Severity: 19, State: 0-language_exec: Process 71
generated an access violation. SQL Server is terminating
this process..Any idea what statement was being run by process 71?
--
Denny Cherry
DBA
GameSpy Industries
"Shrikrishna" <srikrishna.s@.ge.com> wrote in message
news:07a901c35bf5$fda01db0$a501280a@.phx.gbl...
> Error: 0, Severity: 19, State: 0-language_exec: Process 71
> generated an access violation. SQL Server is terminating
> this process..
>

Any part of field" match

How can I make my search button have the "Any part of field" match as a default? with a simple query...

I have a field in MS access with hundreds of words (cv)... I want to be able to find a word in "Any part of field"

my try:

WHERE ((([cv].[detail cv])=[detail]));

detail is nowhere to find... i am prompt to give a value. fine.but it
equals whole field; detail must be the sole value of the field detail cv...

help!

mchelIf you are patient enough to wait for the results, try using the LIKE clause.

-PatP|||what do you mean ? I dont know in advance what is the value ...

do you have an example ?|||See MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbprovjet_sql_support.asp).

-PatP|||Try

WHERE [cv].[detail cv]) LIKE '%whatever_word_you_want%'

but as Pat said, prepare for a long wait if there are many records. Full Text Indexing is another option although I haven't used it much. It's much faster than the above method when doing frequent searches.|||This is the SQLServer forum, not de Acces (bleah...) forum, but anyway:

select ... from ... where [Field] like '*' & trim([ParamName]) & '*'.

PS
In SqlServer '%' is the wildcard for anything, but in Access is '*'. Why the hell Bill considered to use different chars for wildcards in every SGBD that he owns, I don't really know.|||what is SGBD?|||Access 97 did use the asterisk (*) as it's wildcard character. Uncle Bill changed this in Access 2002 and it now is the percent sign (%). This was not well documented but welcome all the same.

To clarify a bit more, the change occurred in the switch from JET 3.5 to JET 4.0, not really in Access, which is just a GUI into JET like Enterprise Manager is to SQL Server.

Any one tell export Sql Database in access hole object

hi All
I want to export my mssqlserver 2000 database with all relation ship and all
default value and all data save in sqlserver Database (I want to export hole
data base object ) in MS Access database is it possible
if it is possible from mssqlserver please tell me if it is possible from any
other third party tool then please tell me
thanks in advanced
from
khurram
I am not aware of tool that does a complete transfer of all objects from SQL
Server to MS Access. You can certainly use SQL Server DTS to move data to
Access. You might want to post this to an Access group as well.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"khurram alam" <khurram.alam@.eintelligencesoft.com> wrote in message
news:O1N0qQnZEHA.3988@.tk2msftngp13.phx.gbl...
hi All
I want to export my mssqlserver 2000 database with all relation ship and all
default value and all data save in sqlserver Database (I want to export hole
data base object ) in MS Access database is it possible
if it is possible from mssqlserver please tell me if it is possible from any
other third party tool then please tell me
thanks in advanced
from
khurram
|||Hi,
You can either use Upsizing wizard or SQL DTS to upgrade objects/data from
Access to SQL server.
The details about the tools and steps are detailed in the below
knowledgebase article.
http://support.microsoft.com/default...b;en-us;237980
Thanks
Hari
MCDBA"khurram alam" <khurram.alam@.eintelligencesoft.com> wrote in message
news:O1N0qQnZEHA.3988@.tk2msftngp13.phx.gbl...
> hi All
> I want to export my mssqlserver 2000 database with all relation ship and
all
> default value and all data save in sqlserver Database (I want to export
hole
> data base object ) in MS Access database is it possible
> if it is possible from mssqlserver please tell me if it is possible from
any
> other third party tool then please tell me
> thanks in advanced
> from
> khurram
>

Any need to convert DAO to ADO?

I have recently completed upsizing a large Access 97 system from a Jet
backend to SQL Server 2000.
It uses linked tables and DAO exclusively.
It's working fine.
Sooner or later, the front end will need to go to A2003 or whatever,
which I asssume won't be too much of a problem.
Would there be any advantage converting to ADO, now, or when it goes to
A2003?
I can't see any justification at the moment.
Terry BellHi
There probably isn't any significant reasons to do this if you are keeping
Access as the front end, although it should help reduce the impact of the
upgrade to 2003.
John
<dreadnought8@.hotmail.com> wrote in message
news:1119167899.670346.155260@.g47g2000cwa.googlegroups.com...
>I have recently completed upsizing a large Access 97 system from a Jet
> backend to SQL Server 2000.
> It uses linked tables and DAO exclusively.
> It's working fine.
> Sooner or later, the front end will need to go to A2003 or whatever,
> which I asssume won't be too much of a problem.
> Would there be any advantage converting to ADO, now, or when it goes to
> A2003?
> I can't see any justification at the moment.
> Terry Bell
>|||BTW
You may want to post to an access newsgroup as they are more likely to have
indepth experience of this sort of conversion.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uzQgLjKdFHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hi
> There probably isn't any significant reasons to do this if you are keeping
> Access as the front end, although it should help reduce the impact of the
> upgrade to 2003.
> John
> <dreadnought8@.hotmail.com> wrote in message
> news:1119167899.670346.155260@.g47g2000cwa.googlegroups.com...
>>I have recently completed upsizing a large Access 97 system from a Jet
>> backend to SQL Server 2000.
>> It uses linked tables and DAO exclusively.
>> It's working fine.
>> Sooner or later, the front end will need to go to A2003 or whatever,
>> which I asssume won't be too much of a problem.
>> Would there be any advantage converting to ADO, now, or when it goes to
>> A2003?
>> I can't see any justification at the moment.
>> Terry Bell
>|||Yes thanks - I posted to this newsgroup in error
Terry|||Hi
DAO and RDO are considered obsolete by Microsoft.
http://msdn.microsoft.com/data/mdac/techinfo/default.aspx?pull=/library/en-us/dnmdac/html/data_mdacroadmap.asp
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<dreadnought8@.hotmail.com> wrote in message
news:1119185731.468657.15010@.g49g2000cwa.googlegroups.com...
> Yes thanks - I posted to this newsgroup in error
> Terry
>

Any need to convert DAO to ADO?

I have recently completed upsizing a large Access 97 system from a Jet
backend to SQL Server 2000.
It uses linked tables and DAO exclusively.
It's working fine.
Sooner or later, the front end will need to go to A2003 or whatever,
which I asssume won't be too much of a problem.
Would there be any advantage converting to ADO, now, or when it goes to
A2003?
I can't see any justification at the moment.
Terry Bell
Hi
There probably isn't any significant reasons to do this if you are keeping
Access as the front end, although it should help reduce the impact of the
upgrade to 2003.
John
<dreadnought8@.hotmail.com> wrote in message
news:1119167899.670346.155260@.g47g2000cwa.googlegr oups.com...
>I have recently completed upsizing a large Access 97 system from a Jet
> backend to SQL Server 2000.
> It uses linked tables and DAO exclusively.
> It's working fine.
> Sooner or later, the front end will need to go to A2003 or whatever,
> which I asssume won't be too much of a problem.
> Would there be any advantage converting to ADO, now, or when it goes to
> A2003?
> I can't see any justification at the moment.
> Terry Bell
>
|||BTW
You may want to post to an access newsgroup as they are more likely to have
indepth experience of this sort of conversion.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uzQgLjKdFHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hi
> There probably isn't any significant reasons to do this if you are keeping
> Access as the front end, although it should help reduce the impact of the
> upgrade to 2003.
> John
> <dreadnought8@.hotmail.com> wrote in message
> news:1119167899.670346.155260@.g47g2000cwa.googlegr oups.com...
>
|||Yes thanks - I posted to this newsgroup in error
Terry
|||Hi
DAO and RDO are considered obsolete by Microsoft.
http://msdn.microsoft.com/data/mdac/...dacroadmap.asp
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<dreadnought8@.hotmail.com> wrote in message
news:1119185731.468657.15010@.g49g2000cwa.googlegro ups.com...
> Yes thanks - I posted to this newsgroup in error
> Terry
>

Any need to convert DAO to ADO?

I have recently completed upsizing a large Access 97 system from a Jet
backend to SQL Server 2000.
It uses linked tables and DAO exclusively.
It's working fine.
Sooner or later, the front end will need to go to A2003 or whatever,
which I asssume won't be too much of a problem.
Would there be any advantage converting to ADO, now, or when it goes to
A2003?
I can't see any justification at the moment.
Terry BellHi
There probably isn't any significant reasons to do this if you are keeping
Access as the front end, although it should help reduce the impact of the
upgrade to 2003.
John
<dreadnought8@.hotmail.com> wrote in message
news:1119167899.670346.155260@.g47g2000cwa.googlegroups.com...
>I have recently completed upsizing a large Access 97 system from a Jet
> backend to SQL Server 2000.
> It uses linked tables and DAO exclusively.
> It's working fine.
> Sooner or later, the front end will need to go to A2003 or whatever,
> which I asssume won't be too much of a problem.
> Would there be any advantage converting to ADO, now, or when it goes to
> A2003?
> I can't see any justification at the moment.
> Terry Bell
>|||BTW
You may want to post to an access newsgroup as they are more likely to have
indepth experience of this sort of conversion.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uzQgLjKdFHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hi
> There probably isn't any significant reasons to do this if you are keeping
> Access as the front end, although it should help reduce the impact of the
> upgrade to 2003.
> John
> <dreadnought8@.hotmail.com> wrote in message
> news:1119167899.670346.155260@.g47g2000cwa.googlegroups.com...
>|||Yes thanks - I posted to this newsgroup in error
Terry|||Hi
DAO and RDO are considered obsolete by Microsoft.
http://msdn.microsoft.com/data/mdac...mdacroadmap.asp
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<dreadnought8@.hotmail.com> wrote in message
news:1119185731.468657.15010@.g49g2000cwa.googlegroups.com...
> Yes thanks - I posted to this newsgroup in error
> Terry
>

Any Linux-based apps similar to MS SQL Enterprise w/ DTS ?

Hi all,

I bet this doesn't exist, but does anyone know of an MS SQL Enterprise
Manager-type application for Linux that would give me access to a DTS
stored on the server? I'm a web programmer by profession, and most of
my tools are Linux-based, but because I do so much indepth work in MS
SQL and DTS's, I've been dual-booting between Windows and Linux mainly
because there's no way I know of to access my MS SQL DTS's from Linux.

Any Linux solutions out there that will read and maybe even edit SQL
DTS's?

Thanks,

Alex.Don't know of any linux tools for sql server but here are two alternatives
that may be useful to you:
http://www.vmware.com/ - no need for dual booting if you can run both OSs at
the same time

http://www.microsoft.com/downloads/...&displaylang=en
SQL Server web data admin tool

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0404150752.6019c780@.posting.google.c om...
> Hi all,
> I bet this doesn't exist, but does anyone know of an MS SQL Enterprise
> Manager-type application for Linux that would give me access to a DTS
> stored on the server? I'm a web programmer by profession, and most of
> my tools are Linux-based, but because I do so much indepth work in MS
> SQL and DTS's, I've been dual-booting between Windows and Linux mainly
> because there's no way I know of to access my MS SQL DTS's from Linux.
> Any Linux solutions out there that will read and maybe even edit SQL
> DTS's?
> Thanks,
> Alex.

Any Gurus...?

I asked the following question in the 'MS Access' boards but I am wondering if there is a way to do it on our SQL server as well? Heres the original question:

"Dont ask why, we just need to do something odd...How can you use the system tables in access to do the following:

1. write a query on the fly for each table in the database (without knowing the table names ahead of time)

2. the query also needs to combine all the fields in a table into one single field (ie - queryfield:[field1]&[field2}&[etc...]), again without knowing what the field names are ahead of time

I have searched news groups and web rings all night. Please help!"You'll get the tables by searching the table Sysobjects for all objects whose xtype is "U". Write a SP that does a cursor loop through all records satisfying that search and, for each match, runs that query that you want to run on all tables.

To concatenate all fields, I guess you'll need to first investigate the columns' data types and convert non-string types to strings? And handle null values? And handles non-convertable types?
Start with the Syscolumns column...

Check first if there are any system SP's that could help you avoid having the code Select's directly against system tables.|||I asked the following question in the 'MS Access' boards but

Q1 I am wondering if there is a way to do it on our SQL server as well?

Heres the original question: "Dont ask why, we just need to do something odd...How can you use the system tables in access to do the following: 1. write a query on the fly for each table in the database (without knowing the table names ahead of time) 2. the query also needs to combine all the fields in a table into one single field (ie - queryfield:[field1]&[field2}&[etc...]), again without knowing what the field names are ahead of time I have searched news groups and web rings all night. Please help!"

A1 Yes, it is certainly possible (in Sql Server and in MS Access). The original #2. may entail work arounds (with many very long table names, especially if these include spaces and / or special characters)

The applicable special stored procedures (Sql Server 2000 sp_) would be sp_tables and sp_columns. One may also make use of [INFORMATION_SCHEMA] views to address your tasks.

-- Example useages of sp_tables and sp_columns:

Use Pubs
Go

Exec sp_tables
Exec sp_tables @.table_type = ['Table']
Exec sp_tables @.table_type = ['View']
Exec sp_tables @.table_type = ['System Table']

Exec sp_columns @.table_name = 'Authors'
--------

-- General List: Catalog Special Stored Procedures:
sp_column_privileges
sp_special_columns
sp_columns
sp_sproc_columns
sp_databases
sp_statistics
sp_fkeys
sp_stored_procedures
sp_pkeys
sp_table_privileges
sp_server_info
sp_tables
--------

-- INFORMATION_SCHEMA views (return metadata of DB objects):

CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINES
ROUTINE_COLUMNS
SCHEMATA
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS

----
Note: Selecting data from information schema views requires using an [INFORMATION_SCHEMA] qualified object name i.e.(in the position where one normally specifies nothing, or the dboo name as appropriate). For example:

SELECT *
FROM Master.[INFORMATION_SCHEMA].COLUMNS
----

-- THE FOLLOWING would fail (unless someone has created a user table / view named 'COLUMNS'):

SELECT *
FROM Master..COLUMNS

SELECT *
FROM Master.dbo.COLUMNS
--------|||Just an example... Improvement and tidying could be made I'm sure!

declare @.id int,
@.name varchar(255),
@.transaction varchar(8000),
@.queryField varchar(8000),
@.col_list varchar(8000),
@.tab_name varchar(255),
@.col_err real,
@.tab_err real

declare table_scan cursor for
select name, id
from sysobjects
where type = 'U'

open table_scan

fetch table_scan into @.tab_name, @.id
set @.tab_err = @.@.FETCH_STATUS
PRINT @.tab_err

while (@.tab_err = 0 )
begin
DECLARE column_scan CURSOR FOR
SELECT name
FROM syscolumns
WHERE ID = @.id

OPEN column_scan
FETCH column_scan into @.name

SELECT @.col_list = @.name
SELECT @.queryField = 'queryfield:['+@.name+']'

SELECT @.col_err = @.@.FETCH_STATUS

WHILE (@.col_err = 0)
begin
SELECT @.col_list = @.col_list +', ' + @.name
SELECT @.queryField = @.queryField + '&['+@.name+']'

FETCH column_scan into @.name
set @.col_err = @.@.FETCH_STATUS
end
SELECT @.transaction = 'SELECT '+ @.col_list + ' FROM ' + @.tab_name
PRINT '================================================= '
PRINT @.transaction
print @.QueryField
PRINT '================================================= '
EXEC(@.transaction)

close column_scan
deallocate column_scan
--
--Table completed
--
fetch table_scan into @.tab_name, @.id
set @.tab_err = @.@.FETCH_STATUS
end

close table_scan

deallocate table_scan

Any functions to replace NZ in SQL Server?

I'm moving some queries out of an Access front end and creating views out of
them in SQL Server 2005 express. In some of the numeric fields, I use nz
quite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.
Is there anything equivalent to this in SQL Server? Right now I'm using
CASE WHEN ... but it seems like an awful lot of script to write just to
replace null with a zero.

Any help would be greatly appreciated.

Thanks!use coalesce or isnull

declare @.v int
select coalesce(@.v,0),isnull(@.v,0)

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Thu, 20 Apr 2006 20:25:47 GMT, "Rico" <r c o l l e n s @. h e m m i n
g w a y . c o mREMOVE THIS PART IN CAPS> wrote:

>I'm moving some queries out of an Access front end and creating views out of
>them in SQL Server 2005 express. In some of the numeric fields, I use nz
>quite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.
>Is there anything equivalent to this in SQL Server? Right now I'm using
>CASE WHEN ... but it seems like an awful lot of script to write just to
>replace null with a zero.
>Any help would be greatly appreciated.
>Thanks!

Hi Rico,

Use COALESCE:

COALESCE (arg1, arg2, arg3, arg4, ...)

returns the first non-NULL of the supplied arguments. You need at least
two arguments, but you can add as many as you like.

--
Hugo Kornelis, SQL Server MVP|||Thanks Guys,

I wound up finding ISNULL before I had a chance to post back. (why do I
always find the solution right after I post).

Is there an argument for using Coalesce over IsNull?

Thanks!

"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:j7sf42hg4b78p8u1v5nj283av4kovqivur@.4ax.com...
> On Thu, 20 Apr 2006 20:25:47 GMT, "Rico" <r c o l l e n s @. h e m m i n
> g w a y . c o mREMOVE THIS PART IN CAPS> wrote:
>>I'm moving some queries out of an Access front end and creating views out
>>of
>>them in SQL Server 2005 express. In some of the numeric fields, I use nz
>>quite often, ( i.e. nz([MyField],0)) to return a zero if the field is
>>null.
>>Is there anything equivalent to this in SQL Server? Right now I'm using
>>CASE WHEN ... but it seems like an awful lot of script to write just to
>>replace null with a zero.
>>
>>Any help would be greatly appreciated.
>>
>>Thanks!
>>
> Hi Rico,
> Use COALESCE:
> COALESCE (arg1, arg2, arg3, arg4, ...)
> returns the first non-NULL of the supplied arguments. You need at least
> two arguments, but you can add as many as you like.
> --
> Hugo Kornelis, SQL Server MVP|||On Thu, 20 Apr 2006 20:58:14 GMT, "Rico" <r c o l l e n s @. h e m m i n
g w a y . c o mREMOVE THIS PART IN CAPS> wrote:

>Thanks Guys,
>I wound up finding ISNULL before I had a chance to post back. (why do I
>always find the solution right after I post).
>Is there an argument for using Coalesce over IsNull?

Hi Rico,

Three!

1. COALESCE is ANSI-standard and hence more portable. ISNULL works only
on SQL Server.

2. COALESCE takes more than two arguments. If you have to find the first
non-NULL of a set of six arguments, ISNULL has to be nested. Not so with
COALESCE.

3. Data conversion weirdness. The datatype of a COALESCE is the datatype
with highest precedence of all datatypes used in the COALESCE (same as
with any SQL expression). Not so for ISNULL - the datatype of ISNULL is
the same as the first argument. This is extremely non-standard and can
cause very nasty and hard-to-track-down bugs.

--
Hugo Kornelis, SQL Server MVP|||Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:
> I wound up finding ISNULL before I had a chance to post back. (why do I
> always find the solution right after I post).
> Is there an argument for using Coalesce over IsNull?

In theory, coalesce is what you always should use, because:

1) It's ANSI-compatible.
2) coalesce can accept list of several values, whereas isnull accepts
exactly two.

Unfortunately, there are contexts were isnull() is preferable, or the
only choice. The ones I'm thinking of are:
1) In definition of indexed views you may need to use isnull to make
the view indexable.
2) I've seen reports where using coalesce resulted in a poor query plan
whereas isnull did not. I should add that that was not really a plain-
vanilla query.

So despite these excpetions, I would recommend coalesce. Even if it's
more difficult to spell.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Null is not zero. Null is not a zero length string.

I believe that nulls were not designed to be placeholders for these
values.
We should be extremely careful when we convert nulls to values. Such
conversion could lead to error. Often it is persons without strong
grounding in mathematics and logic who make these conversions,
increasing the likelihood of such error. The best practice is likely to
be the exclusion of records with nulls in the columns we are processing
and to enter values in those where a value is appropriate. There may be
some cases where it's a good idea to substitute a zls for a null value,
but none comes to my mind at this time.

IMNSHO SQL would be more rigorous if it had no IsNull(Field,Value) or
corresponding Coalesce function.

[Yes, I've probably posted IsNull(Field,Value) solutions here; that was
then; this is now.]|||Excellent! Thanks!

"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:vttf42107btt07jbk21cvb6953kediiarp@.4ax.com...
> On Thu, 20 Apr 2006 20:58:14 GMT, "Rico" <r c o l l e n s @. h e m m i n
> g w a y . c o mREMOVE THIS PART IN CAPS> wrote:
>>Thanks Guys,
>>
>>I wound up finding ISNULL before I had a chance to post back. (why do I
>>always find the solution right after I post).
>>
>>Is there an argument for using Coalesce over IsNull?
> Hi Rico,
> Three!
> 1. COALESCE is ANSI-standard and hence more portable. ISNULL works only
> on SQL Server.
> 2. COALESCE takes more than two arguments. If you have to find the first
> non-NULL of a set of six arguments, ISNULL has to be nested. Not so with
> COALESCE.
> 3. Data conversion weirdness. The datatype of a COALESCE is the datatype
> with highest precedence of all datatypes used in the COALESCE (same as
> with any SQL expression). Not so for ISNULL - the datatype of ISNULL is
> the same as the first argument. This is extremely non-standard and can
> cause very nasty and hard-to-track-down bugs.
> --
> Hugo Kornelis, SQL Server MVP|||Read about IsNull Vs Coalesce
http://www.sqlservercentral.com/col...tweenisnull.asp

Madhivanan|||On 20 Apr 2006 15:57:53 -0700, Lyle Fairfield wrote:

>Null is not zero. Null is not a zero length string.
>I believe that nulls were not designed to be placeholders for these
>values.
(snip)

Hi Lyle,

Thus far, I agree with yoour post.

(snip)
> There may be
>some cases where it's a good idea to substitute a zls for a null value,
>but none comes to my mind at this time.

First, you should be awarer that COALESCE and ISNULL on SQL Server, or
Nz on Access, can not just be used to replace NULL with 0 or zero length
string - you can replace them with anything you like. Common uses are
COALESCE (SomeColumn, 'n/a') in a report. Or
COALESCE (UserSpecifiedColumn, DefaultValue) in any query or view.

>IMNSHO SQL would be more rigorous if it had no IsNull(Field,Value) or
>corresponding Coalesce function.

I disagree with this statement. As I've shown above, COALESCE and ISNULL
can be used in very useful ways. That they might also be abused by
people who fail to think their solutions through is sad, but no reason
to abolish them. That's like forbidding cars because someone might cause
an accident while drinking and driving.

Besides, since COALESCE is just a shorthand for a specific CASE
expression, removing COALESCE from the language would have no effect;
people would just use the equivalent CASE expression.

--
Hugo Kornelis, SQL Server MVP|||Lyle Fairfield wrote:
> We should be extremely careful when we convert nulls to values. Such
> conversion could lead to error. Often it is persons without strong
> grounding in mathematics and logic who make these conversions,
> increasing the likelihood of such error.

You think so? Nulls as formulated in SQL totally defy any standard
mathematics or logic. Any system that permits the predicate (x=x) to
evaluate to anything other than true isn't likely to win many votes
from persons with a strong grounding in mathematics. It is precisely
because nulls are so counter-intuitive that they lead to so many
mistakes in SQL. However, I do agree with your basic point that if you
regularly need to convert nulls like this it may indicate weakness in
your design or requirements.

--
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/...US,SQL.90).aspx
--|||I probably shouldn't open my mouth in the presence of some posters, but with
regard to converting nulls being bad design; I have a bunch of reports that
show loans and payments (just to make things simple). If I have no payment
record (a null) then I have zero payments applied to the loan. By
converting these null payment records to zero payments, is this considered
in theory bad design? Or is this an exception to that rule. Is there a
definition between what would be considered bad design and what is
considered an exception?

Not trying to raise a debate really, just asking for clarification.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145653630.430902.73680@.z34g2000cwc.googlegro ups.com...
> Lyle Fairfield wrote:
>> We should be extremely careful when we convert nulls to values. Such
>> conversion could lead to error. Often it is persons without strong
>> grounding in mathematics and logic who make these conversions,
>> increasing the likelihood of such error.
> You think so? Nulls as formulated in SQL totally defy any standard
> mathematics or logic. Any system that permits the predicate (x=x) to
> evaluate to anything other than true isn't likely to win many votes
> from persons with a strong grounding in mathematics. It is precisely
> because nulls are so counter-intuitive that they lead to so many
> mistakes in SQL. However, I do agree with your basic point that if you
> regularly need to convert nulls like this it may indicate weakness in
> your design or requirements.
> --
> 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/...US,SQL.90).aspx
> --|||Rico wrote:
> I probably shouldn't open my mouth in the presence of some posters, but with
> regard to converting nulls being bad design; I have a bunch of reports that
> show loans and payments (just to make things simple). If I have no payment
> record (a null) then I have zero payments applied to the loan. By
> converting these null payment records to zero payments, is this considered
> in theory bad design? Or is this an exception to that rule. Is there a
> definition between what would be considered bad design and what is
> considered an exception?
> Not trying to raise a debate really, just asking for clarification.

If you have no payment record then why do you have a null?

Nulls are a source of complexity and error. On the other hand, avoiding
them can lead to complexity of a different kind - often requiring the
creation of additional tables for example. Whether to use nulls at all
is a controversial topic about which a huge amount has been written and
argued over. In practice, SQL database systems tend to make it very
hard to avoid them altogether.

--
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/...US,SQL.90).aspx
--|||Rico (me@.you.com) writes:
> I probably shouldn't open my mouth in the presence of some posters, but
> with regard to converting nulls being bad design; I have a bunch of
> reports that show loans and payments (just to make things simple). If I
> have no payment record (a null) then I have zero payments applied to the
> loan. By converting these null payment records to zero payments, is
> this considered in theory bad design? Or is this an exception to that
> rule. Is there a definition between what would be considered bad design
> and what is considered an exception?

In practice there are many cases where NULL and 0 or the empty string
are more or less the same thing.

Of course, if we have a table:

CREATE TABLE loans (loanno char(11) NOT NULL,
...
no_of_payments int NULL,
...

A NULL in no_of_payments taken to the letter would mean "we don't
know how many payments that has not been done on this loan, if any
at all" or "this is a loan on which you do not make payments at all,
so it is not applicable".

But I don't believe for a second that this is how your table design looks
like. And with a more complex design, there could easily appear a NULL in
a query.

There are many cases were isnull or coalesce comes in handy. For some
computations, equating NULL with 0 makes sense. But coalesce can
also be used to get a value from multiple places. Assume, for instance,
that a customer can have a fixed discount, or he can be part of a
group that can have a common rebate. Assuming that an individual
discount overrides the group discount, that would be:

coalesce(Customers.discount, Groups.discount, 0)

The 0 at the end is really needed here, if we assume that a customer
may not belong to any group. That is, the Groups table comes in with
a left join, so it does not help if Groups.discount is not nullable.
And Customers.discount needs to be NULL, so we can have some logic
to get the group instead. It would not be good to have 0 to mean
"use group instead", because we may actually want to deprive the
customer of the group rebate.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||FROM BOL:
A value of NULL indicates that the value is unknown. A value of NULL is
different from an empty or zero value. No two null values are equal.
Comparisons between two null values, or between a NULL and any other
value, return unknown because the value of each NULL is unknown.

Null values generally indicate data that is unknown, not applicable, or
that the data will be added later. For example, a customer's middle
initial may not be known at the time the customer places an order.

Following is information about nulls:

To test for null values in a query, use IS NULL or IS NOT NULL in the
WHERE clause.

When query results are viewed in SQL Server Management Studio Code
editor, null values are shown as (null) in the result set.

Null values can be inserted into a column by explicitly stating NULL in
an INSERT or UPDATE statement, by leaving a column out of an INSERT
statement, or when adding a new column to an existing table by using
the ALTER TABLE statement.

Null values cannot be used for information that is required to
distinguish one row in a table from another row in a table, for
example, foreign or primary keys.

In program code, you can check for null values so that certain
calculations are performed only on rows with valid, or not NULL, data.
For example, a report can print the social security column only if
there is data that is not NULL in the column. Removing null values when
you are performing calculations can be important, because certain
calculations, such as an average, can be inaccurate if NULL columns are
included.

If it is likely that null values are stored in your data and you do not
want null values appearing in your data, you should create queries and
data-modification statements that either remove NULLs or transform them
into some other value.

Important:
To minimize maintenance and possible effects on existing queries or
reports, you should minimize the use of null values. Plan your queries
and data-modification statements so that null values have minimal
effect.

When null values are present in data, logical and comparison operators
can potentially return a third result of UNKNOWN instead of just TRUE
or FALSE. This need for three-valued logic is a source of many
application errors. These tables outline the effect of introducing null
comparisons.

---
I think that null should not be referred to as a value, in the same way
that celibacy should not be referred to as sex.

In addition, the statements:
"A value of NULL is different from an empty or zero value."
and
"you should create queries and data-modification statements that
either ... or transform them into some other value."
conflict.|||This is just crap!|||Lyle Fairfield (lylefairfield@.aim.com) writes:
> This is just crap!

At least that was a concise comment.

Nevertheless, exactly what you think is crap? How would you model
discounts that can be applied on several levels?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Answer: Access creates DB in MSDE in SQL Server Authentication mod

I've confirmed that MS Access will create an ADP project in MSDE only if the server is set to Mixed (SQL Server and Windows) authentication mode. Trials in Windows Authentication mode failed. This is still hard to believe, I know, but its a reality.
To all those who tried to help, my sincere thanks.
Lou Arnold
Ottawa, Canada
Lou,
Did you have to create users in the SQL database itself so that Domain
Users could access the MSDE, and if so, with what tool did you use?
"Lou Arnold" <Lou_Arnold@.nospam.com> wrote in message
news:EB2211E6-F5BD-4DD8-AC32-65B7B0DC0ECA@.microsoft.com...
> I've confirmed that MS Access will create an ADP project in MSDE only if
> the server is set to Mixed (SQL Server and Windows) authentication mode.
> Trials in Windows Authentication mode failed. This is still hard to
> believe, I know, but its a reality.
> To all those who tried to help, my sincere thanks.
> --
> Lou Arnold
> Ottawa, Canada

ANSII NULL AND QUOTED IDENTIFERS - Upgrade problems

I inherited a system that was started in Access and moved to SQL 2000. The business has grown and we are trying to replace our older systems with ASP.NET and Server 2005.

Currently, we are trying to make a new asp.net page for searching the database for records with matching dates or date ranges. There are several types of dates to search, so they are all optional. Set to default as null in the proc. For each date there is an operator field, such as equal or greater, etc. The proc only looks at the date if the operator is set to EQ" or "IN" and ignores the date if operator set to "NO"

The proc works fine when running under Management Studio, but fails coming through a SQLDataSource to a gridview. It works with integer and string filters, but fails when entering the same date ('07/20/2007') that works in the testing tool. All dates are actually stored as datetime, and they are set as DateTime Control Parameters in the SQLDataSource.

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:someConnectionString %>"

SelectCommand="spTESTSearch"SelectCommandType="StoredProcedure">

<SelectParameters>

<asp:ParameterDefaultValue="M"Name="TypeCode"Type="String"/>

<asp:ParameterDefaultValue="EQ"Name="FirstPubOp"Type="String"/>

<asp:ControlParameterControlID="FirstPubDateTextBox"DefaultValue=""Name="FirstPubDate"

PropertyName="Text"Type="DateTime"/>

<asp:ParameterDefaultValue="C"Name="UserName"Type="String"/>

<asp:ParameterDefaultValue="NO"Name="SearchTextOp"Type="String"/>

<asp:ParameterName="SearchText"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

The dates are selected properly in the testing tool, with code such as :

DateDiff(day, FirstPubDate, @.FirstPubDate)= 0

I think my problem is based on option settings for the databases themselves. The old database was set to Ansii Nulls and Quoted Identiers to OFF, and the new ones were defaulted to them being ON. I noticed that the tool also, sets those options on when creating new stored procedures.

Would this difference be causing the dates to be quoted and viewed as objects rather than strings? What are the dangers in changing those options on the database that still gets uploads from the old SQL 2000 database and some Mac-based systems?

I welcome any suggestions on how to get my new stuff running while not breaking my old production systems.

Thanks for the assist!

The options you mentioned should have no relevance to your current issue. Quoted identifiers is referring to whether a statement like:

SELECT "my_column" or SELECT [my_column] is the correct way to specify column/table names that have characters in them that could cause confusion, or are reserved words.

The ANSI NULLS parameter refers to a number of things like if the boolean operation (NULL=NULL) should return true, or NULL.

BTW, the default values on the stored procedure (as specified in the stored procedure) aren't used. They only come into play when you call a stored procedure without specifying a value for that parameter. The parameters you have defined in the SqlDatasource will always pass a value.

Personally, I would first hook up an event to the SqlDatasource_Selecting event. By checking the e.Command.Parameters collection in that event, you can see what it about to be passed to the stored procedure. Verify that passing the values (as they were in the selecting event) works in Management Studio.

If that does not lead you to the problem, then run Sql Profiler, and compare the difference between the queries that get submitted via the web application, and the query submitted by management studio.