MDAC2.71 Service Pack 1 Refresh: mdac_typ.exe ??

Hi all,
I run MDAC Component Check on my SQL Server 2000. None
of the versions of MDAC passed the tests. The closest one
is 2.7 SP1 (2.71.9030.9) with only One error on
msjtes40.dll file
msjtes40.dll,Error,Field 'FileVersion' mismatch:
Expect '4.00.2927.8' Got '4.00.5914.0',Microsoft Jet
Expression
Does this mean that I have to install 2.71 Service
Pack 1 Refresh: mdac_typ.exe ?
What files contain in this mdac_typ.exe? Would
installing this file help on Memory Leak?
Thank you sooooo much!!!
P.S.: I think I install 2.7 SP1 via Windows Update. I
didn't apply SQL Sever 2000 SP3 at all.
AnnieThat file almost always checks different, so don't worry.
normally if the version number is higher compared to the closest match it is
OK.
This normally happens because you installed some software with an newer DLL.
( I am assuming msjtes40.dll is installed by MS Office, since I always see
it being different at work.)
Tim S
"Annie" <piyomeme@.yahoo.com> wrote in message
news:05da01c34d8b$f0e63f00$a101280a@.phx.gbl...
> Hi all,
> I run MDAC Component Check on my SQL Server 2000. None
> of the versions of MDAC passed the tests. The closest one
> is 2.7 SP1 (2.71.9030.9) with only One error on
> msjtes40.dll file
> msjtes40.dll,Error,Field 'FileVersion' mismatch:
> Expect '4.00.2927.8' Got '4.00.5914.0',Microsoft Jet
> Expression
> Does this mean that I have to install 2.71 Service
> Pack 1 Refresh: mdac_typ.exe ?
> What files contain in this mdac_typ.exe? Would
> installing this file help on Memory Leak?
> Thank you sooooo much!!!
> P.S.: I think I install 2.7 SP1 via Windows Update. I
> didn't apply SQL Sever 2000 SP3 at all.
> Annie
>|||Thank you, Tim.
I don't have MS Office installed on Server, but it could
be something else.
That was helpful. Thank you~
Annie
>--Original Message--
>That file almost always checks different, so don't worry.
>normally if the version number is higher compared to the
closest match it is
>OK.
>This normally happens because you installed some software
with an newer DLL.
>( I am assuming msjtes40.dll is installed by MS Office,
since I always see
>it being different at work.)
>Tim S
>
>"Annie" <piyomeme@.yahoo.com> wrote in message
>news:05da01c34d8b$f0e63f00$a101280a@.phx.gbl...
>> Hi all,
>> I run MDAC Component Check on my SQL Server 2000.
None
>> of the versions of MDAC passed the tests. The closest
one
>> is 2.7 SP1 (2.71.9030.9) with only One error on
>> msjtes40.dll file
>> msjtes40.dll,Error,Field 'FileVersion' mismatch:
>> Expect '4.00.2927.8' Got '4.00.5914.0',Microsoft Jet
>> Expression
>> Does this mean that I have to install 2.71 Service
>> Pack 1 Refresh: mdac_typ.exe ?
>> What files contain in this mdac_typ.exe? Would
>> installing this file help on Memory Leak?
>> Thank you sooooo much!!!
>> P.S.: I think I install 2.7 SP1 via Windows Update. I
>> didn't apply SQL Sever 2000 SP3 at all.
>> Annie
>
>.
>

MDAC Upgrade

Please advice.
I have a WIN 2K SP3 ADV Server that runs SQL 2K with MDAC
2.6 SP1.
I would like to upgrade this to MDAC 2.7. Can I simply
run the MDAC 2.7 update or what is the Microsoft way to
do this.
Help Appreciated.SQL Server 2000 Service Pack 3a comes with MDAC 2.7 Service Pack 1a, so the
best way is to install the SQL Server Service Pack.
--
Jacco Schalkwijk
SQL Server MVP
"Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
news:e9c601c3f096$369ceb40$a501280a@.phx.gbl...
> Please advice.
> I have a WIN 2K SP3 ADV Server that runs SQL 2K with MDAC
> 2.6 SP1.
> I would like to upgrade this to MDAC 2.7. Can I simply
> run the MDAC 2.7 update or what is the Microsoft way to
> do this.
> Help Appreciated.|||MDAC 2.8 is also availabe for download from MS site.
Regards
Chip
>--Original Message--
>SQL Server 2000 Service Pack 3a comes with MDAC 2.7
Service Pack 1a, so the
>best way is to install the SQL Server Service Pack.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Olutimi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:e9c601c3f096$369ceb40$a501280a@.phx.gbl...
>> Please advice.
>> I have a WIN 2K SP3 ADV Server that runs SQL 2K with
MDAC
>> 2.6 SP1.
>> I would like to upgrade this to MDAC 2.7. Can I simply
>> run the MDAC 2.7 update or what is the Microsoft way to
>> do this.
>> Help Appreciated.
>
>.
>|||Thanks for your reply.
My configuration is WIN 2k SP3 plus SQL 2K Client tools
with MDAC 2.6 SP1. What is the best course of action to
upgrade to MDAC 2.7
Help Appreciated.
>--Original Message--
>SQL Server 2000 Service Pack 3a comes with MDAC 2.7
Service Pack 1a, so the
>best way is to install the SQL Server Service Pack.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Olutimi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:e9c601c3f096$369ceb40$a501280a@.phx.gbl...
>> Please advice.
>> I have a WIN 2K SP3 ADV Server that runs SQL 2K with
MDAC
>> 2.6 SP1.
>> I would like to upgrade this to MDAC 2.7. Can I simply
>> run the MDAC 2.7 update or what is the Microsoft way to
>> do this.
>> Help Appreciated.
>
>.
>|||Olutimi,
We run Win 2K SP4 and SQL2000 SP2 +. Remember to make sure that the server
MDAC is at least the same level as the client or higher. MDAC can introduce
changes that cause things like DTS, run from the client, to fail.
We upgraded the server to MDAC 2.7SP1A and then the clients. A simple
download and then run the DASETUP.
HTH
Chris Wood
Alberta Department of Energy
CANADA
"Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
news:e17301c3f0b9$9ec87860$a301280a@.phx.gbl...
> Thanks for your reply.
> My configuration is WIN 2k SP3 plus SQL 2K Client tools
> with MDAC 2.6 SP1. What is the best course of action to
> upgrade to MDAC 2.7
> Help Appreciated.
>
> >--Original Message--
> >SQL Server 2000 Service Pack 3a comes with MDAC 2.7
> Service Pack 1a, so the
> >best way is to install the SQL Server Service Pack.
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"Olutimi" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:e9c601c3f096$369ceb40$a501280a@.phx.gbl...
> >> Please advice.
> >>
> >> I have a WIN 2K SP3 ADV Server that runs SQL 2K with
> MDAC
> >> 2.6 SP1.
> >> I would like to upgrade this to MDAC 2.7. Can I simply
> >> run the MDAC 2.7 update or what is the Microsoft way to
> >> do this.
> >>
> >> Help Appreciated.
> >
> >
> >.
> >

MDAC Component Checker

I am on a win 2003 server and have run the component
checker and found 2 errors. The files obdcbcp.dll and
sqlsrv32.dll have mismatch errors. There errors say
expecting version 2000.85.1022.0; but the problem is that
I have 2000.85.1025.0
How can I fix this?
Thanks in advance,
Stewart
It sounds like you have installed MS04-003 to thsi system. The sqlsrv32.dll
and odbcbcp.dll from that patch are version 2000.85.1025.0.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Stewart,
I have exactly same problem, what did you do to fix it?
Thanks
Regards
Kasba

MDAC Component Checker

I am on a win 2003 server and have run the component
checker and found 2 errors. The files obdcbcp.dll and
sqlsrv32.dll have mismatch errors. There errors say
expecting version 2000.85.1022.0; but the problem is that
I have 2000.85.1025.0
How can I fix this?
Thanks in advance,
StewartIt sounds like you have installed MS04-003 to thsi system. The sqlsrv32.dll
and odbcbcp.dll from that patch are version 2000.85.1025.0.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Stewart,
I have exactly same problem, what did you do to fix it?
Thanks
Regards
Kasba
vkasba
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message744871.html

MDAC 2.8

Hi,
Recently I run Windows Update on my Windows 2000 Server with MS SQL Server
on the same machine. It update the MDAC to 2.8 version.
It broke my store procedure which work fine with MDAC 2.6 sp2.
With MDAC 2.6 it only take 1 minutes to run; however, with MDAC 2.8 it take
all day and lock up the database. Not only that, the CPU utilization is 100%
the whole day.
Please help ! Thank you in advance.
Here is the sql statement:
Create Procedure dbo.spSbcAudit_AddressMismatch
As
Select
A.ListedPhone As SbcPhone,
A.ListingInstructions As SbcListingInstructions,
A.OmitCode As SbcOmitCode,
A.LastName As SbcLastName,
A.FirstName As SbcFirstName,
A.TitleOfAddress As SbcTitleOfAddress,
A.TitleOfLineage As SbcTitleOfLineage,
A.Number As SbcNumber,
A.Directional As SbcDirectional,
A.StreetName As SbcStreetName,
A.Locality As SbcCity,
A.ListType As SbcListType,
A.RecordType As SbcRecordType,
A.InputFileName As SbcInputFileName,
Ltrim(Str(Month(A.DateFromClec)))+'/'+Ltrim(Str(Day(A.DateFromClec)))+'/'+Lt
rim(Str(Year(A.DateFromClec))) As SbcDateFromClec,
-- Icoms fields
B.AccountNumber As IcomsAccountNumber,
B.InstallDate As IcomsInstallDate,
B.TelephoneNumber As IcomsPhone,
B.TelephoneStatus As IcomsListingStatus,
B.RecordType As IcomsRecordType,
B.ListType As IcomsListType,
B.ListingIdCode As IcomsListingIdCode,
B.ListingInstructions As IcomsListingInstructions,
B.LastName As IcomsLastName,
B.FirstName As IcomsFirstName,
B.TitleOfLineage As IcomsTitleOfLineage,
B.SpecialFilingName As IcomsSpecialFilingName,
B.Title As IcomsTitle,
B.Apartment As IcomsApartmentNumber,
B.Directional As IcomsDirectional,
B.AddressNumber As IcomsNumber,
B.StreetName As IcomsStreetName,
B.City As IcomsCity,
Substring(B.ZipCode,1,5) As IcomsZipCode,
B.ClassOfService As IcomsClassOfService
Into dbo.tblSbcAuditResult_AddressMismatch
From dbo.tblPde7661 A
Inner Join dbo.tblIcomsData B On A.ListedPhone = B.TelephoneNumber
Where
A.ListType = B.ListType And
(Upper(Rtrim(Ltrim(A.StreetName))) <> Upper(Rtrim(Ltrim(B.StreetName))))
And
B.TelephoneStatus In (
Select vchListingStatus
From dbo.tblAuditListingStatus
Where bitIsInclude = 1) And
B.RateCenterId <> 9 And
-- caption only, Icoms does not support this
A.ListedPhone Not In (Select ListedPhone From dbo.tblPde7661 Where
Reference='Customer Service') And
-- Install date must be before the SBC download date
B.InstallDate <= (select intInstallDate from tblAuditInstallDate)
Order By
B.ClassOfService,
B.ListType,
A.ListingInstructions,
A.OmitCode,
B.ListingInstructionswell, latest mdac 2.8 includes security fixes and perheps
some not needed enhancements. You can go to Microsoft site
and search for mdac 2.6 service pack 2 and download it
from there. Or you can use following link to download it:
http://www.microsoft.com/downloads/details.aspx?
familyid=8e5f816c-4918-4250-b8bd-
3794582c9089&languageid=f49e8428-7071-4979-8a67-
3cffcb0c2524&displaylang=en
hth.
>--Original Message--
>Hi,
>Recently I run Windows Update on my Windows 2000 Server
with MS SQL Server
>on the same machine. It update the MDAC to 2.8 version.
>It broke my store procedure which work fine with MDAC 2.6
sp2.
>With MDAC 2.6 it only take 1 minutes to run; however,
with MDAC 2.8 it take
>all day and lock up the database. Not only that, the CPU
utilization is 100%
>the whole day.
>Please help ! Thank you in advance.
>Here is the sql statement:
>Create Procedure dbo.spSbcAudit_AddressMismatch
>As
>Select
> A.ListedPhone As SbcPhone,
> A.ListingInstructions As SbcListingInstructions,
> A.OmitCode As SbcOmitCode,
> A.LastName As SbcLastName,
> A.FirstName As SbcFirstName,
> A.TitleOfAddress As SbcTitleOfAddress,
> A.TitleOfLineage As SbcTitleOfLineage,
> A.Number As SbcNumber,
> A.Directional As SbcDirectional,
> A.StreetName As SbcStreetName,
> A.Locality As SbcCity,
> A.ListType As SbcListType,
> A.RecordType As SbcRecordType,
> A.InputFileName As SbcInputFileName,
>Ltrim(Str(Month(A.DateFromClec)))+'/'+Ltrim(Str(Day
(A.DateFromClec)))+'/'+Lt
>rim(Str(Year(A.DateFromClec))) As SbcDateFromClec,
> -- Icoms fields
> B.AccountNumber As IcomsAccountNumber,
> B.InstallDate As IcomsInstallDate,
> B.TelephoneNumber As IcomsPhone,
> B.TelephoneStatus As IcomsListingStatus,
> B.RecordType As IcomsRecordType,
> B.ListType As IcomsListType,
> B.ListingIdCode As IcomsListingIdCode,
> B.ListingInstructions As IcomsListingInstructions,
> B.LastName As IcomsLastName,
> B.FirstName As IcomsFirstName,
> B.TitleOfLineage As IcomsTitleOfLineage,
> B.SpecialFilingName As IcomsSpecialFilingName,
> B.Title As IcomsTitle,
> B.Apartment As IcomsApartmentNumber,
> B.Directional As IcomsDirectional,
> B.AddressNumber As IcomsNumber,
> B.StreetName As IcomsStreetName,
> B.City As IcomsCity,
> Substring(B.ZipCode,1,5) As IcomsZipCode,
> B.ClassOfService As IcomsClassOfService
>Into dbo.tblSbcAuditResult_AddressMismatch
>From dbo.tblPde7661 A
>Inner Join dbo.tblIcomsData B On A.ListedPhone =B.TelephoneNumber
>Where
> A.ListType = B.ListType And
> (Upper(Rtrim(Ltrim(A.StreetName))) <> Upper(Rtrim(Ltrim
(B.StreetName))))
>And
> B.TelephoneStatus In (
> Select vchListingStatus
> From dbo.tblAuditListingStatus
> Where bitIsInclude = 1) And
> B.RateCenterId <> 9 And
> -- caption only, Icoms does not support this
> A.ListedPhone Not In (Select ListedPhone From
dbo.tblPde7661 Where
>Reference='Customer Service') And
> -- Install date must be before the SBC download date
> B.InstallDate <= (select intInstallDate from
tblAuditInstallDate)
>Order By
> B.ClassOfService,
> B.ListType,
> A.ListingInstructions,
> A.OmitCode,
> B.ListingInstructions
>
>.
>

MBSA "Identity"

My Windows login is a domain administrator. I run MBSA and ask it to scan only my computer. I have removed Builtin\administrators from the sysadmin role in my SQL Server 2000 installation. The login is still there. MBSA is still able to get into my SQL Server and report that there is a guest user in Northwind and Pubs. It does not, however, report on the logins with weak passwords, which it did when Builtin\Administrators was a member of sysadmins. This leads me to believe that it does use the BuiltIn\Adminstrators login to access the SQL Server. Is this correct?

Thanks,
Sharon

I've been looking for an answer to your question. I'll post back when I'll get one.

Thanks

Laurentiu

|||

MBSA connects with the user credentials and the analysis it can do will depend on the user rights. If you connect to the server thorugh the Builtin\Administrators group login and not some dedicated login, and if you removed this login from the sysadmin group, then MBSA will perform the checks as a non-privileged principal.

Thanks

Laurentiu

|||

Thanks. That's what I determined from my testing and I am glad to have it confirmed.

Sharon

maximum row size exceeds the maximum number of bytes per row (8060

Hi,
I have created a table (say Table1) with few columns, with one of the
columns (say column1) having the data type as Varchar(8000).
Now I run the below query -
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
if not exists (select * from dbo.syscolumns
where id = object_id(N'[dbo].[Table1]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1
and name = 'Column1')
ALTER TABLE dbo.Table1 ADD
Column1 varchar(500) NULL
GO
COMMIT
Now that the column Column1 already exists, the add column statement won't
be executed. But still I get the warning -
"Warning: The table 'Table1' has been created but its maximum row size
(8579) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes."
My question is, now that the column already exists, the statement itself
won't be executed. Then why do I still get this warning?That has to do with the sequence of query processing and when the warning is generated. Obviously
the warning (the fact that this table will have a row size for which you can exceed the limit in
your data) is generated in a stage which is earlier than when the statements are actually executed.
In other words, the If statement haven't been executed at this stage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"swap@.@.@." <swap@.discussions.microsoft.com> wrote in message
news:73AC8FFC-4041-4B22-AB25-4CCDB94598FC@.microsoft.com...
> Hi,
> I have created a table (say Table1) with few columns, with one of the
> columns (say column1) having the data type as Varchar(8000).
> Now I run the below query -
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> if not exists (select * from dbo.syscolumns
> where id = object_id(N'[dbo].[Table1]')
> and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and name = 'Column1')
> ALTER TABLE dbo.Table1 ADD
> Column1 varchar(500) NULL
> GO
> COMMIT
> Now that the column Column1 already exists, the add column statement won't
> be executed. But still I get the warning -
> "Warning: The table 'Table1' has been created but its maximum row size
> (8579) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes."
> My question is, now that the column already exists, the statement itself
> won't be executed. Then why do I still get this warning?
>

Maximum number of databases used for each query in 2005?

In MS Sql Server 2000, if you run a stored procedure that query's more
than 8 databases, you get this error gets raised:

Maximum number of databases used for each query has been exceeded. The
maximum allowed is 8.

In 2005, does this maximum number still exist? if so, is it still 8, or
has the number been increased?

Thanks,
MikeHoly Moly, what kind of queries are you writing!?|||Hi, I have the same problem with 2000. I am about to upgrade to 2005 and I am very concerned about this because our database requirements are rapidly growing.

Thanks,
benn

Quote:

Originally Posted by Michael.Suarez@.gmail.com

In MS Sql Server 2000, if you run a stored procedure that query's more
than 8 databases, you get this error gets raised:

Maximum number of databases used for each query has been exceeded. The
maximum allowed is 8.

In 2005, does this maximum number still exist? if so, is it still 8, or
has the number been increased?

Thanks,
Mike

Maximum length sql statement that can be run in SQL CE

Hello
Can anyone tell me what the maximum length sql statement SQL CE can run is?
Thank you
Gráinne

Please ask in SQL Mobile forum. Thanks.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=152&SiteID=1

|||moving thread to correct forum.|||Thanks for moving the posting Mark.
To expand the question:
I have a table that has a column storing an SQL statement. The sql is set by the customer and I want to ensure that the column size is no bigger than the maximum SQL string that can be executed by SQL CE.
If there is no absolute upper limit, then is there a rule of thumb for the maximum number of characters in a SQL CE statement?
Thanks again
Gráinne
|||

There is no maximum limit on the number of characters that can be part of a SQL statement in SQL Mobile. Please review the SQL Mobile Books Online under the index heading 'maximum database object sizes' for more information.

Darren

Maximum allowed value for integer in Analysis Services

Is there a maximum number up to which Analysis Services will do a SUM up to.

The reason I ask is that I get a negative number when I run the following MDX ([Mesaure] is aggregated with SUM)

SELECT [Measures].[Measure] ON AXIS(0)
FROM [Cube]

However each row in my fact table has a positive (and quite large value) for its Measure.

Thanks in advance

Stephen

Yes. There a maximun value of integer, I believe its just over 2 billion or so. When you first built your cube, AS defaulted your measure to the datatype in your source. You will have to change the datatype to a bigint, then you won't see those negative values.

Van Dieu

|||Sweet. Bigint it is. Don't know why it never occured to me to change the datatype of the Measure in Analysis Services.

Oh well, the simple things.

Maximum # of columns for FullText engine ?

Hi ,

I am trying to run a fulltext query and I get the following error message:

"Too many full-text columns or the full-text query is too complex to be executed"

Does anybody know if there is a limitation in the number of columns? Or what can cause this error?

Here is the sql:

SELECT DISTINCT FT_TBL.CapId, FT_TBL.Title, FT_TBL.PubMedId, FT_TBL.IssueYear, Rank

FROM ClinicalLiteratureTbl AS FT_TBL,

CONTAINSTABLE(ClinicalLiteratureTbl, *, '("body mass index" OR "BMI" OR "Quetelet`s Index" OR "Quetelet Index" OR "Quetelets Index") AND ("myocardial infarction" OR "myocardial infarct" OR "MI" OR "myocardium infarct" OR "myocardium infarction" OR "cardiac infarction" OR "myocardial necrosis" OR "coronary attack" OR "myocardium necrosis" OR "myocardial infarction syndrome" OR "myocardial necrosis syndrome" OR "heart attack" OR "coronary thrombosis" OR "AMI" OR "post-AMI" OR "post AMI" OR "post infarction" OR "post-infarction")') AS KEY_TBL WHERE FT_TBL.ArticleID = KEY_TBL.[KEY] AND FT_TBL.RaterGroupId IN (1,2,3,4) ORDER BY IssueYear DESC

Thanks

gigel,

Fulltext works with 16 columns max on indexes. Because this, your query return error.

|||

Hi,

create a new field, call it keywords, copy all relevant data in this field. Now you have just one field instead of 17.

You might consider also to eliminate the "NoiseWords" in the new Keywords-field. Keep the original data including noise in their current fields.

NoiseWords are kept in a table in your SQL-Server ProgramFiles

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseENG.txt

browse this forum for more about noise

Thomas

Maximum # of columns for FullText engine ?

Hi ,

I am trying to run a fulltext query and I get the following error message:

"Too many full-text columns or the full-text query is too complex to be executed"

Does anybody know if there is a limitation in the number of columns? Or what can cause this error?

Here is the sql:

SELECT DISTINCT FT_TBL.CapId, FT_TBL.Title, FT_TBL.PubMedId, FT_TBL.IssueYear, Rank

FROM ClinicalLiteratureTbl AS FT_TBL,

CONTAINSTABLE(ClinicalLiteratureTbl, *, '("body mass index" OR "BMI" OR "Quetelet`s Index" OR "Quetelet Index" OR "Quetelets Index") AND ("myocardial infarction" OR "myocardial infarct" OR "MI" OR "myocardium infarct" OR "myocardium infarction" OR "cardiac infarction" OR "myocardial necrosis" OR "coronary attack" OR "myocardium necrosis" OR "myocardial infarction syndrome" OR "myocardial necrosis syndrome" OR "heart attack" OR "coronary thrombosis" OR "AMI" OR "post-AMI" OR "post AMI" OR "post infarction" OR "post-infarction")') AS KEY_TBL WHERE FT_TBL.ArticleID = KEY_TBL.[KEY] AND FT_TBL.RaterGroupId IN (1,2,3,4) ORDER BY IssueYear DESC

Thanks

gigel,

Fulltext works with 16 columns max on indexes. Because this, your query return error.

|||

Hi,

create a new field, call it keywords, copy all relevant data in this field. Now you have just one field instead of 17.

You might consider also to eliminate the "NoiseWords" in the new Keywords-field. Keep the original data including noise in their current fields.

NoiseWords are kept in a table in your SQL-Server ProgramFiles

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseENG.txt

browse this forum for more about noise

Thomas

Maximum # of columns for FullText engine ?

Hi ,

I am trying to run a fulltext query and I get the following error message:

"Too many full-text columns or the full-text query is too complex to be executed"

Does anybody know if there is a limitation in the number of columns? Or what can cause this error?

Here is the sql:

SELECT DISTINCT FT_TBL.CapId, FT_TBL.Title, FT_TBL.PubMedId, FT_TBL.IssueYear, Rank

FROM ClinicalLiteratureTbl AS FT_TBL,

CONTAINSTABLE(ClinicalLiteratureTbl, *, '("body mass index" OR "BMI" OR "Quetelet`s Index" OR "Quetelet Index" OR "Quetelets Index") AND ("myocardial infarction" OR "myocardial infarct" OR "MI" OR "myocardium infarct" OR "myocardium infarction" OR "cardiac infarction" OR "myocardial necrosis" OR "coronary attack" OR "myocardium necrosis" OR "myocardial infarction syndrome" OR "myocardial necrosis syndrome" OR "heart attack" OR "coronary thrombosis" OR "AMI" OR "post-AMI" OR "post AMI" OR "post infarction" OR "post-infarction")') AS KEY_TBL WHERE FT_TBL.ArticleID = KEY_TBL.[KEY] AND FT_TBL.RaterGroupId IN (1,2,3,4) ORDER BY IssueYear DESC

Thanks

gigel,

Fulltext works with 16 columns max on indexes. Because this, your query return error.

|||

Hi,

create a new field, call it keywords, copy all relevant data in this field. Now you have just one field instead of 17.

You might consider also to eliminate the "NoiseWords" in the new Keywords-field. Keep the original data including noise in their current fields.

NoiseWords are kept in a table in your SQL-Server ProgramFiles

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseENG.txt

browse this forum for more about noise

Thomas

MAXFILE Size won't Change

SQL Server 2005 64 Bit Enterprise Edition.

When I run the the following command I get command completed successfully.

ALTER DATABASE Apollo_Replication
MODIFY FILE (NAME = Apollo_Replication_log,
MAXSIZE = 'UNLIMITED')

However

when I look at the database properties the file growth is still

restricted to 2 GB. I even tried to edit the property through

SSMS. Same results...It looks like it saves ok but when I go back

in... no change.
The only thing unique about the db is it is the subscriber from an Oracle publisher.
Has anyone seen this little feature before?Are you sure it is 2GB and not 2TB? The architectural limit for each log file is 2TB.|||oops it is 2 TB. But why can't I change the properties to unlimited file growth? My current size is 31 GB. I am trying to shrink the file back down to a reasonable size and change the properties.|||

UNLIMITED is never really unlimited. For log files it is 2TB. For data files it is actually 16TB. Its just a reporting issue on what the UI or report considers to be "unlimited."

maxdop 1 doesn't seem to work inside batch

I have several dynamically-generated queries that I would like to run with
the option(maxdop 1). However, if I put the queries with the option in eithe
r
Exec or sp_executesql, the option is ignored and the query runs with
parallelism (I've seen the degrees of parallelism and execution plan for thi
s
in profiler). Is there any way to do this without having to configure the
server setting?
These queries actually run inside a stored procedure, so if there is a
maxdop setting for the entire stored procedure and all the batches it
executes, that would be even better.Can you post the actual sp? The hint should affect everything in the
statement butnot the entire sp.
Andrew J. Kelly SQL MVP
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:FDD1E453-77BF-45DF-A73C-6F39D9EDB6FC@.microsoft.com...
>I have several dynamically-generated queries that I would like to run with
> the option(maxdop 1). However, if I put the queries with the option in
> either
> Exec or sp_executesql, the option is ignored and the query runs with
> parallelism (I've seen the degrees of parallelism and execution plan for
> this
> in profiler). Is there any way to do this without having to configure the
> server setting?
> These queries actually run inside a stored procedure, so if there is a
> maxdop setting for the entire stored procedure and all the batches it
> executes, that would be even better.|||tBackground: this query is fetching data from views (vStatsX). Each view is
the union of a simple SELECT from two similar tables. The query with the
maxdop option is executed WITH the option enabled in query analyzer(QA). If
I
enclose the query in an EXECUTE or sp_ExecuteSQL in QA the option is ignored
.
As I am calling this query in the sp using sp_ExecuteSQL, I'm running into
the same problem where the option is ignored.
Here is the generated query, from the profiler SP:StmtStarting:
SELECT @.Completed=@.Completed +IsNull(Count(Sup.SessionID),0),
@.Duration=@.Duration+IsNull(sum(Sup.Seconds),0)
FROM vStats4 Sup LEFT JOIN vStats1 S1 on Sup.SessionID=S1.SuperSessionID
LEFT JOIN vStats2 S2 on Sup.SessionID=S2.SuperSessionID
LEFT JOIN vStats3 S3 on Sup.SessionID=S3.SuperSessionID
WHERE Sup.date between @.dateStart and @.dateEnd and Sup.Escalated=0 and
Sup.IsAbandoned=0
AND IsNull(S1.Escalated,0)=0 AND IsNull(S2.Escalated,0)=0 AND
IsNull(S3.Escalated,0)=0
AND (Sup.Problems>0 or Sup.Questions>0 or Sup.AMSSEarches>0 OR S1.Problems>0
or S1.Questions>0 or S1.AMSSearches>0
OR S2.Problems>0 or S2.Questions>0 or S2.AMSSearches>0 OR
S3.Problems>0 or S3.Questions>0 or S3.AMSSearches>0)
OPTION(MAXDOP 1)
Here's the Profiler Output following the SP:stmtStarting:
Degree of Parallelism (7.0 Insert)
Execution Tree
--
Compute Scalar(DEFINE:([Expr1027]=[@.Completed]+i
snull([Expr1025], 0),
[Expr1028]=[@.duration]+isnull([Expr1026]
, 0)))
|--Compute Scalar(DEFINE:([Expr1025]=Convert([globa
lagg1030]),
[Expr1026]=If ([globalagg1032]=0) then NULL else [globalagg1034]))
|--Stream Aggregate(DEFINE:([globalagg1030]=SUM([p
artialagg1029]),
[globalagg1032]=SUM([partialagg1031]), [globalagg1034]=SUM([partialagg1033])
))
|--Concatenation
|--Compute Scalar(DEFINE:([partialagg1029]=[partial
agg1029]))
| |--Stream Aggregate(DEFINE:([partialagg1029]=Count
(*),
[partialagg1033]=SUM([Stats1].[Seconds])))
| |--Clustered Index
Scan(OBJECT:([noHoldCust18].[dbo].[Stats1].[MSCCSPK_20041015040614432]),
WHERE:((((([Stats1].[SuperSessionID]=NULL AND [Stats1].[Date]>=[@.dateStart])
AND [Stats1].[Date]<=[@.dateEnd]) AND (([Stats1].[Problems]>0 OR
[Stats1].[Questions]>0) OR [Stats1].[AMSSearches]>0)) AND
Convert([Stats1].[Escalated])=0) AND Convert([Stats1].[IsAbandoned])=0))
|--Compute Scalar(DEFINE:([partialagg1029]=[partial
agg1029]))
|--Stream Aggregate(DEFINE:([partialagg1029]=Count
(*),
[partialagg1033]=SUM([Stats1Archive].[Seconds])))
|--Clustered Index
Scan(OBJECT:([noHoldCust18].[dbo].[Stats1Archive].[MSCCSPK_20041015040714695]),
WHERE:((((([Stats1Archive].[SuperSessionID]=NULL AND
[Stats1Archive].[Date]>=[@.dateStart]) AND [Stats1Archive].[Date]<=[@.dateEnd])
AND (([Stats1Archive].[Problems]>0 OR [Stats1Archive].[Questions]>0) OR
[Stats1Archive].[AMSSearches]>0)) AND Convert([Stats1Archive].[Escalated])=0)
AND Convert([Stats1Archive].[IsAbandoned])=0))|||Here is how I am executing the query from within the sp:
exec sp_Executesql @.strSQL,N'@.Completed int OUTPUT,@.duration int output,
@.DateStart datetime, @.DateEnd datetime',
@.Completed output,@.duration output,@.DateStart,@.DateEnd|||I think I need more sleep...
It looks like the option IS being used in the batch calls. I reviewed the
execution plan generated when I purposely omit the option in my query and it
is different than the execution plan generated when I include the option.
This execution plan (without the option) explicitly mentions parallelism
whereas the other one does not. I'm obviously not an expert in this area but
I hope I'm on the right track now.
ompute Scalar(DEFINE:([Expr1102]=[@.Completed]+i
snull([Expr1100], 0),
[Expr1103]=[@.duration]+isnull([Expr1101]
, 0)))
|--Compute Scalar(DEFINE:([Expr1100]=Convert([globa
lagg1105]),
[Expr1101]=If ([globalagg1107]=0) then NULL else [globalagg1109]))
|--Stream Aggregate(DEFINE:([globalagg1105]=SUM([p
artialagg1104]),
[globalagg1107]=SUM([partialagg1104]), [globalagg1109]=SUM([partialagg1108])
))
|--Parallelism(Gather Streams)
|--Compute Scalar(DEFINE:([partialagg1104]=[partial
agg1104]))
etc...|||Yes I saw no indication of parallelism in the other plan. Just a note that
your where clause is almost forcing index scans. You might want to see if
you can't optimize that query.
Andrew J. Kelly SQL MVP
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:5F333045-B692-415F-9DFC-2B21FABBA6EB@.microsoft.com...
>I think I need more sleep...
> It looks like the option IS being used in the batch calls. I reviewed the
> execution plan generated when I purposely omit the option in my query and
> it
> is different than the execution plan generated when I include the option.
> This execution plan (without the option) explicitly mentions parallelism
> whereas the other one does not. I'm obviously not an expert in this area
> but
> I hope I'm on the right track now.
> ompute Scalar(DEFINE:([Expr1102]=[@.Completed]+i
snull([Expr1100], 0),
> [Expr1103]=[@.duration]+isnull([Expr1101]
, 0)))
> |--Compute Scalar(DEFINE:([Expr1100]=Convert([globa
lagg1105]),
> [Expr1101]=If ([globalagg1107]=0) then NULL else [globalagg1109]))
> |--Stream Aggregate(DEFINE:([globalagg1105]=SUM([p
artialagg1104]),
> [globalagg1107]=SUM([partialagg1104]),
> [globalagg1109]=SUM([partialagg1108])))
> |--Parallelism(Gather Streams)
> |--Compute
> Scalar(DEFINE:([partialagg1104]=[partial
agg1104]))
> etc...
>

MAX_LENGTH Field does not match the value displayed?

Hi,

I wonder if anyone can help. I have installed the adventureWorksDW and when I run this query

select * from sys.all_columns where object_id = 85575343 and column_id = 2

The max_length filed value is 50 but if I view the column in explorer view the max_length is 25 I.e. ProductAlternateKey(nvarchar(25) ,null)

I don;'t know if I am looking at the correct table value?

Hi METAJOB,

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying.

tosc

|||

AdventureWorks is created using unicode (nchar, nvarchar, ntext).

Each 'single' character requires TWO bytes for storage. Therefore a nvarchar(25) field will require 50 bytes for storage. Max_length reports the actual storage size in bytes.

Max Worker Threads and User Connections

Hi

we run SQL 2005 Enterprise on W2003 Enterprise 32 bit on 8 dual core Xeon 7020 with 32Gb RAM.

The max worker thread settings is 0 now. But 3d party consultancy which monitors our servers on the POP is suggesting to make this figure to be 1200. They said - there are 1000 connections at peak time. I am going to check if it is correct...

But my question is - is it proper thing to do to set worker thread number that high? (BOL says - MS doesnt recommend more than 1024 on 32 bit systems)

Is it that useful to match number of connections with number of threads?

Thank you

P.S. that consultancy let us down several times. I dont trust then at all.. I need a real reason to change such a thing

Well, I probably wouldn't recommend it generally speaking.

First off, just because there are 1000 connections at a given time, definately doesn't imply that there are 1000 concurrent processing requests...connections are frequently idle in many types of systems processing 'think' time by end users, or just sitting idle in a connection pool (.NET or otherwise). If you are using connection pooling, I'm sure you'll find that many of your connections are idle a fair amount of the time...

In a 32-bit system, on startup Sql server will reserve enough memory to load it's own binaries in, then some to handle 8kb and greater memory requests (typically 128mb by default, but can be modified using a startup switch), then enough memory to be able to allocate stacks for the total number of threads possible to use, which is determined by your 'max worker thread' setting.

Each thread stack is uses about .5mb of memory, which means using a setting of 1200 will result in a reserved space of about 600mb for just stack allocation on a 32bit box. Bear in mind that on a 32bit box of this size (assuming you aren't using a /3gb or /USERVA switch...if you are you ain't going to get to use most of your physical memory), the maximum VAS for a user-mode process like Sql server is 2gb, so stack space is going to use over a 1/4 of that space alone.

By default, in Sql 2005 on a 32bit box, Sql Server will dynamically handle threading using a simple algorithm where with 4 procs or less, 256 max threads will be used, then an additional 8 threads per CPU...in the case of a 16-way machine (yours is an 8-way dual core, 16 schedulers), Sql will use a max thread dynamic setting of 352 on that machine. If you bump that to 1200, your allocating about 850 more than the dyamic configuration would.

Given what I mentioned earlier about the fact that many connections are typically idle a fair amount of time, connections will pool the thread use at the OS level, and typically the dynamic default configuration is hightly recommend.

I'd ask the consultants to provide you extensive documentation on why they think it would be beneficial...typically, recommending something like this would take some extensive testing, and intimate knowledge of a system...

HTH,

|||

Thank you Chad,

I have same ideas but just needed some authoritative information to argue with the consulters.

I really hate 'solutions' made from thin air. I refused to do any changes and requested concrete measures have been made before they decided on the advice. I think they have none.

Might be there is a chance they are right (that advice was given to resolve issues I addressed in my previous topic: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=436762&SiteID=1). Fortunately these accidents havent repeated since I went through the application and put some NOLOCKs into critical places..