Hello,
A User currently having a problem with an Access 2000 application that uses
Linked tables to connect to an SQL 2005 database. The user receives the
following message:
Connection failed:
SQLState: ‘01000’
SQL Server Error: 11004
[Microsoft ][ODBC SQL Server Driver][TCP/IP Sockets]
ConnectionOpen(gethostbyname()()).
Connection failed:
SQLState: ‘08001’
SQL Server Error: 11
[Microsoft ][ODBC SQL Server Driver][TCP/IP Sockets] General net
work error.
Check your network documentation.
The application was working for the user but it stopped working after a help
desk person had done some things to her machine to get another application
working. Anybody have an idea on what may have changed to cause this to
happen?
The Users workstation is Windows 2000 SP4. I can also ping and telnet to the
SQL server from that machine.It can be caused by different things but I've seen MDAC
versions be the cause of such problems before. Make sure the
PC has the latest MDAC version. You can download MDAC
versions and also download component checker to check the
MDAC version from:
http://msdn.microsoft.com/data/ref/mdac/downloads/
You would also want to check the event logs on the PC for
any network related issues.
-Sue
On Wed, 26 Jul 2006 08:46:02 -0700, Bill Klein
<BillKlein@.discussions.microsoft.com> wrote:
>Hello,
>A User currently having a problem with an Access 2000 application that uses
>Linked tables to connect to an SQL 2005 database. The user receives the
>following message:
>Connection failed:
>SQLState: 01000
>SQL Server Error: 11004
>[Microsoft ][ODBC SQL Server Driver][TCP/IP Sockets]
>ConnectionOpen(gethostbyname()()).
>Connection failed:
>SQLState: 08001
>SQL Server Error: 11
>[Microsoft ][ODBC SQL Server Driver][TCP/IP Sockets] General ne
twork error.
>Check your network documentation.
>The application was working for the user but it stopped working after a hel
p
>desk person had done some things to her machine to get another application
>working. Anybody have an idea on what may have changed to cause this to
>happen?
>The Users workstation is Windows 2000 SP4. I can also ping and telnet to th
e
>SQL server from that machine.|||Thanks Sue, the computer had version 2.53 i've upgraded it to 2.8 sp1 and
everything seems to work fine now.
"Sue Hoegemeier" wrote:
> It can be caused by different things but I've seen MDAC
> versions be the cause of such problems before. Make sure the
> PC has the latest MDAC version. You can download MDAC
> versions and also download component checker to check the
> MDAC version from:
> http://msdn.microsoft.com/data/ref/mdac/downloads/
> You would also want to check the event logs on the PC for
> any network related issues.
> -Sue
> On Wed, 26 Jul 2006 08:46:02 -0700, Bill Klein
> <BillKlein@.discussions.microsoft.com> wrote:
>
>sql
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Tuesday, March 27, 2012
General Database/Query and Form Design question
Currently working on an existing system written using an Access 2002 project
(.adp) and SQL Server 2000 and need to add some ehancements.
The system is a leasing system where a customer leases one or more assets
for a defined term (eg. 24, 36, 48 months...). Each lease may also be
associated with a fixed, or variable monthly repayment regime.
For example a customer wants to lease a Boat for 24 months for the first 12
months (period 1 - 12) they pay $50 per month, for period 13 - 18 they pay
$35 per month, and for the last 6 months (period 19 - 24) they pay $25 per
month.
The proposed table design is as follows:
tblLease
LeaseId int (identity) PK
CustomerId FK
TermId FK
...
tblLeaseAsset (1:M relationship to tblLease)
AssetId int PK
LeaseId int PK/FK to tblLease
AssetDescription
...
tblLeaseAssetRate (Intersection table - 1:M relationship to tblLeaseAsset,
1:M relationship to tblLeaseTerm)
AssetId int PK/FK
TermPeriodId int PK/FK
Payment decimal (19,4)
...
tblLeasePeriodTerm (1:M relationship to tblLease)
TermPeriodId int (identity) PK
LeaseId int FK to tblLease
FromTermPeriod small int
ToTermPeriod small int
...
Whilst the users are happy to enter the initial lease and period/term
information as a Parent/Main form and Child/Subform combination. They would
like to be able to enter the Asset and payment information together as a
single Child/Subform:
Period Period Period
Asset Description 1 - 12 13 - 18 19 - ...
A Boat $50 $35 $25
The problem is that this requires a pivot table/cross tabulation type view
of the data and these types of queries are not generally updatable.
Does anyone have any ideas how I might achieve the objective either in terms
of database, query or form design so that users can insert, update, delete
and view records?
Your assistance apreciated
Guy HortonWell, for each "asset", you got
AssetName AssetCost WherePurchased
Boat $15,000 WalMart
Car $5,000 MacDonalds
etc.
Now, just put another sub-form to the "right" of a above where you can enter
"many" values for each of the above.
If your cursor is in Boat, then you can enter:
Period Amount
1 - 12 $50
13 - 18 $35
etc.
I can think of "many" cases where you got a detail line, and need "many" for
that details. consider the QuickBooks when you write a single check, and a
split amount, you need to "split" out the funds to "many" values. So, the
solution is to make two side by side sub-forms.
Take a look at the following screen shots, and especially the last one where
I have a "classic" cheque "distribution" screen (for each check/person on
the left, I can enter "many" split values for that particlar amount
(donation in this example) on the right side...
http://www.members.shaw.ca/AlbertKa...ticles/Grid.htm
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.members.shaw.ca/AlbertKallal|||I'd strongly recommend Albert's way, but if you're absolutely forced to
doing it on the same line, you can try a few different things that I can
think of:
1. Play with Access' PivotTable features. I've never found them all that
useful, and very klunky to use, but it may get you where you're trying to
go. Don't ask me for more detailed info on how to do that, though, I
generally avoid PivotTables like the plague.
2. Implement a temporary table that goes across as you'd like it to, then
as each record is read/updated, transfer the values to/from the "real" table
in the OnCurrent and Before/AfterUpdate events.
3. Use an embedded control of some kind (Hierarchical FlexGrid?) to display
the data instead.
Good luck,
Rob
"Guy Horton" <guy.horton@.nospam.bigpond.com> wrote in message
news:%23RueVVwwFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Currently working on an existing system written using an Access 2002
> project (.adp) and SQL Server 2000 and need to add some ehancements.
> The system is a leasing system where a customer leases one or more assets
> for a defined term (eg. 24, 36, 48 months...). Each lease may also be
> associated with a fixed, or variable monthly repayment regime.
> For example a customer wants to lease a Boat for 24 months for the first
> 12 months (period 1 - 12) they pay $50 per month, for period 13 - 18 they
> pay $35 per month, and for the last 6 months (period 19 - 24) they pay $25
> per month.
> The proposed table design is as follows:
> tblLease
> LeaseId int (identity) PK
> CustomerId FK
> TermId FK
> ...
> tblLeaseAsset (1:M relationship to tblLease)
> AssetId int PK
> LeaseId int PK/FK to tblLease
> AssetDescription
> ...
> tblLeaseAssetRate (Intersection table - 1:M relationship to tblLeaseAsset,
> 1:M relationship to tblLeaseTerm)
> AssetId int PK/FK
> TermPeriodId int PK/FK
> Payment decimal (19,4)
> ...
> tblLeasePeriodTerm (1:M relationship to tblLease)
> TermPeriodId int (identity) PK
> LeaseId int FK to tblLease
> FromTermPeriod small int
> ToTermPeriod small int
> ...
> Whilst the users are happy to enter the initial lease and period/term
> information as a Parent/Main form and Child/Subform combination. They
> would like to be able to enter the Asset and payment information together
> as a single Child/Subform:
> Period Period Period
> Asset Description 1 - 12 13 - 18 19 - ...
> A Boat $50 $35 $25
> The problem is that this requires a pivot table/cross tabulation type view
> of the data and these types of queries are not generally updatable.
> Does anyone have any ideas how I might achieve the objective either in
> terms of database, query or form design so that users can insert, update,
> delete and view records?
> Your assistance apreciated
> Guy Horton
>|||Albert,
Thank you for your excellent response. I reviewed your article and screen
shots and have to say they look very professional.
I briefly considered side by side subforms and agree with you that this is a
very workable option, and probably the option I will go with. Although, it
doesn't allow the users to view all lease rates for all the currently
visible leased assets, and they think of periods as running across as
opposed to down the form.
Your thoughts appreciated.
Best Regards,
Guy
"Albert D.Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:ukKGPmwwFHA.460@.TK2MSFTNGP15.phx.gbl...
> Well, for each "asset", you got
>
> AssetName AssetCost WherePurchased
> Boat $15,000 WalMart
> Car $5,000 MacDonalds
> etc.
> Now, just put another sub-form to the "right" of a above where you can
> enter "many" values for each of the above.
> If your cursor is in Boat, then you can enter:
> Period Amount
> 1 - 12 $50
> 13 - 18 $35
> etc.
> I can think of "many" cases where you got a detail line, and need "many"
> for that details. consider the QuickBooks when you write a single check,
> and a split amount, you need to "split" out the funds to "many" values.
> So, the solution is to make two side by side sub-forms.
> Take a look at the following screen shots, and especially the last one
> where I have a "classic" cheque "distribution" screen (for each
> check/person on the left, I can enter "many" split values for that
> particlar amount (donation in this example) on the right side...
> http://www.members.shaw.ca/AlbertKa...ticles/Grid.htm
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.members.shaw.ca/AlbertKallal
>|||Robert,
Thank you for your response. I agree that Albert's solution is the probably
the most sensible way to go, and that PivotTable features are klunky to use.
Your thoughts appreciated
Guy
"Robert Morley" <rmorley@.magma.ca.no.freakin.spam> wrote in message
news:eYrGDpxwFHA.3756@.tk2msftngp13.phx.gbl...
> I'd strongly recommend Albert's way, but if you're absolutely forced to
> doing it on the same line, you can try a few different things that I can
> think of:
> 1. Play with Access' PivotTable features. I've never found them all that
> useful, and very klunky to use, but it may get you where you're trying to
> go. Don't ask me for more detailed info on how to do that, though, I
> generally avoid PivotTables like the plague.
> 2. Implement a temporary table that goes across as you'd like it to, then
> as each record is read/updated, transfer the values to/from the "real"
> table in the OnCurrent and Before/AfterUpdate events.
> 3. Use an embedded control of some kind (Hierarchical FlexGrid?) to
> display the data instead.
>
> Good luck,
> Rob
> "Guy Horton" <guy.horton@.nospam.bigpond.com> wrote in message
> news:%23RueVVwwFHA.2064@.TK2MSFTNGP09.phx.gbl...
>
(.adp) and SQL Server 2000 and need to add some ehancements.
The system is a leasing system where a customer leases one or more assets
for a defined term (eg. 24, 36, 48 months...). Each lease may also be
associated with a fixed, or variable monthly repayment regime.
For example a customer wants to lease a Boat for 24 months for the first 12
months (period 1 - 12) they pay $50 per month, for period 13 - 18 they pay
$35 per month, and for the last 6 months (period 19 - 24) they pay $25 per
month.
The proposed table design is as follows:
tblLease
LeaseId int (identity) PK
CustomerId FK
TermId FK
...
tblLeaseAsset (1:M relationship to tblLease)
AssetId int PK
LeaseId int PK/FK to tblLease
AssetDescription
...
tblLeaseAssetRate (Intersection table - 1:M relationship to tblLeaseAsset,
1:M relationship to tblLeaseTerm)
AssetId int PK/FK
TermPeriodId int PK/FK
Payment decimal (19,4)
...
tblLeasePeriodTerm (1:M relationship to tblLease)
TermPeriodId int (identity) PK
LeaseId int FK to tblLease
FromTermPeriod small int
ToTermPeriod small int
...
Whilst the users are happy to enter the initial lease and period/term
information as a Parent/Main form and Child/Subform combination. They would
like to be able to enter the Asset and payment information together as a
single Child/Subform:
Period Period Period
Asset Description 1 - 12 13 - 18 19 - ...
A Boat $50 $35 $25
The problem is that this requires a pivot table/cross tabulation type view
of the data and these types of queries are not generally updatable.
Does anyone have any ideas how I might achieve the objective either in terms
of database, query or form design so that users can insert, update, delete
and view records?
Your assistance apreciated
Guy HortonWell, for each "asset", you got
AssetName AssetCost WherePurchased
Boat $15,000 WalMart
Car $5,000 MacDonalds
etc.
Now, just put another sub-form to the "right" of a above where you can enter
"many" values for each of the above.
If your cursor is in Boat, then you can enter:
Period Amount
1 - 12 $50
13 - 18 $35
etc.
I can think of "many" cases where you got a detail line, and need "many" for
that details. consider the QuickBooks when you write a single check, and a
split amount, you need to "split" out the funds to "many" values. So, the
solution is to make two side by side sub-forms.
Take a look at the following screen shots, and especially the last one where
I have a "classic" cheque "distribution" screen (for each check/person on
the left, I can enter "many" split values for that particlar amount
(donation in this example) on the right side...
http://www.members.shaw.ca/AlbertKa...ticles/Grid.htm
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.members.shaw.ca/AlbertKallal|||I'd strongly recommend Albert's way, but if you're absolutely forced to
doing it on the same line, you can try a few different things that I can
think of:
1. Play with Access' PivotTable features. I've never found them all that
useful, and very klunky to use, but it may get you where you're trying to
go. Don't ask me for more detailed info on how to do that, though, I
generally avoid PivotTables like the plague.
2. Implement a temporary table that goes across as you'd like it to, then
as each record is read/updated, transfer the values to/from the "real" table
in the OnCurrent and Before/AfterUpdate events.
3. Use an embedded control of some kind (Hierarchical FlexGrid?) to display
the data instead.
Good luck,
Rob
"Guy Horton" <guy.horton@.nospam.bigpond.com> wrote in message
news:%23RueVVwwFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Currently working on an existing system written using an Access 2002
> project (.adp) and SQL Server 2000 and need to add some ehancements.
> The system is a leasing system where a customer leases one or more assets
> for a defined term (eg. 24, 36, 48 months...). Each lease may also be
> associated with a fixed, or variable monthly repayment regime.
> For example a customer wants to lease a Boat for 24 months for the first
> 12 months (period 1 - 12) they pay $50 per month, for period 13 - 18 they
> pay $35 per month, and for the last 6 months (period 19 - 24) they pay $25
> per month.
> The proposed table design is as follows:
> tblLease
> LeaseId int (identity) PK
> CustomerId FK
> TermId FK
> ...
> tblLeaseAsset (1:M relationship to tblLease)
> AssetId int PK
> LeaseId int PK/FK to tblLease
> AssetDescription
> ...
> tblLeaseAssetRate (Intersection table - 1:M relationship to tblLeaseAsset,
> 1:M relationship to tblLeaseTerm)
> AssetId int PK/FK
> TermPeriodId int PK/FK
> Payment decimal (19,4)
> ...
> tblLeasePeriodTerm (1:M relationship to tblLease)
> TermPeriodId int (identity) PK
> LeaseId int FK to tblLease
> FromTermPeriod small int
> ToTermPeriod small int
> ...
> Whilst the users are happy to enter the initial lease and period/term
> information as a Parent/Main form and Child/Subform combination. They
> would like to be able to enter the Asset and payment information together
> as a single Child/Subform:
> Period Period Period
> Asset Description 1 - 12 13 - 18 19 - ...
> A Boat $50 $35 $25
> The problem is that this requires a pivot table/cross tabulation type view
> of the data and these types of queries are not generally updatable.
> Does anyone have any ideas how I might achieve the objective either in
> terms of database, query or form design so that users can insert, update,
> delete and view records?
> Your assistance apreciated
> Guy Horton
>|||Albert,
Thank you for your excellent response. I reviewed your article and screen
shots and have to say they look very professional.
I briefly considered side by side subforms and agree with you that this is a
very workable option, and probably the option I will go with. Although, it
doesn't allow the users to view all lease rates for all the currently
visible leased assets, and they think of periods as running across as
opposed to down the form.
Your thoughts appreciated.
Best Regards,
Guy
"Albert D.Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:ukKGPmwwFHA.460@.TK2MSFTNGP15.phx.gbl...
> Well, for each "asset", you got
>
> AssetName AssetCost WherePurchased
> Boat $15,000 WalMart
> Car $5,000 MacDonalds
> etc.
> Now, just put another sub-form to the "right" of a above where you can
> enter "many" values for each of the above.
> If your cursor is in Boat, then you can enter:
> Period Amount
> 1 - 12 $50
> 13 - 18 $35
> etc.
> I can think of "many" cases where you got a detail line, and need "many"
> for that details. consider the QuickBooks when you write a single check,
> and a split amount, you need to "split" out the funds to "many" values.
> So, the solution is to make two side by side sub-forms.
> Take a look at the following screen shots, and especially the last one
> where I have a "classic" cheque "distribution" screen (for each
> check/person on the left, I can enter "many" split values for that
> particlar amount (donation in this example) on the right side...
> http://www.members.shaw.ca/AlbertKa...ticles/Grid.htm
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.members.shaw.ca/AlbertKallal
>|||Robert,
Thank you for your response. I agree that Albert's solution is the probably
the most sensible way to go, and that PivotTable features are klunky to use.
Your thoughts appreciated
Guy
"Robert Morley" <rmorley@.magma.ca.no.freakin.spam> wrote in message
news:eYrGDpxwFHA.3756@.tk2msftngp13.phx.gbl...
> I'd strongly recommend Albert's way, but if you're absolutely forced to
> doing it on the same line, you can try a few different things that I can
> think of:
> 1. Play with Access' PivotTable features. I've never found them all that
> useful, and very klunky to use, but it may get you where you're trying to
> go. Don't ask me for more detailed info on how to do that, though, I
> generally avoid PivotTables like the plague.
> 2. Implement a temporary table that goes across as you'd like it to, then
> as each record is read/updated, transfer the values to/from the "real"
> table in the OnCurrent and Before/AfterUpdate events.
> 3. Use an embedded control of some kind (Hierarchical FlexGrid?) to
> display the data instead.
>
> Good luck,
> Rob
> "Guy Horton" <guy.horton@.nospam.bigpond.com> wrote in message
> news:%23RueVVwwFHA.2064@.TK2MSFTNGP09.phx.gbl...
>
Monday, March 26, 2012
General advice needed regarding MS Access, MS SQL Server, MySQL/PostgreSQL
I am working on two versions of an application, one of which will be a
windows forms application (which will need to be redistributable) and
the other will be a web application.
Hi there
Hi Sam,
The one database I am using is an MS Access database that has been
placed in the public domain by the USDA. It has about 80 MB of
nutrition data. I use it to allow a user to enter a recipe and obtain
an analysis of the nutrition in the prodct of the recipe, either per
serving or per 100 grams, and I support storing the recipes entered by
the user. The schema for both the USDA's nutrition database and my
recipe database is very simple. I suppose I could use XML, but I am
not sure what that buys me. The recipe database will initially be
small (actually it will be empty unless I create a few recipes and
store them as samples of what can be done). The remainder of the
application is smple. It supports creating a weeklong meal plan,
assessing the meal plans entered for how well it meets the nutritional
requirements for each member of the family (there is a window that
allows the user to enter these requirements for each member of the
family), and maintain a health diary, including what has actually been
eaten, any of the user's family's ailments and medications/remedies
used to deal with them. So, if Dad has a heart condition, Mom has
diabetes, and junior has colitis, each of their special nutritional
needs can be satisfied without Dad ;-) having to prepare three
different meals. Additionally, they can assess how well their diet and
medications or remedies serve their respective needs.
I am running the 64 bit version of Windows XP Pro, and it has Jet;
either that or the professional edition of MS Visual Studio installed
it. This I know because I have already used it within a test program
that looks at the USDA data. While my application uses this to analyse
foods and recipes, my end user will never need to look at the raw
nutritional data.
Thanks
Yes, I would have utilities or procedures that could read data in
various formats, but would store and deploy all my data in one standard
database format(such as SQL Server 2005 Express), if possible, to
simplify application development and maintenance.
No, not by a long shot. Different built-in functions (such as IIF() and
CStr() in Access vs CASE statement and CAST() in SQL Server), different
wild cards (* in Access vs % in SQL Server - and completely different
regular expressions for the LIKE statement), different data types, etc.
windows forms application (which will need to be redistributable) and
the other will be a web application.
I have MS Visual Studio 2005 (along with the developer's edition of MS
SQL Server), but not MS Access. I also have MySQL, PostgreSQL, Sun's
application server, Tomcat and Apache web server. I am working on
Windows XP Pro, and have installed the .NET 3 SDK and all relevant
related products I could find (e.g. 2 extensions packages for Visual
Studio).
I have one MS Access database, to which my users should have read only
access. I have, and have used, a tool for importing MS Access
databases into MySQL. I expect that SQL Server has a similar utility
hidden somewhere (where I haven't yet looked, though I HAVE been
looking - obviously in the wrong places). I have located a similar
utility for importing MS Access databases into PostgreSQL. I have not
yet decided which servers to use for the web version, but that is
another story, for which I may raise another thread in due course (but
I welcome suggestions which may reduce the effort required given
required effort for the windows forms app).
My problem is for the windows form aplication (intended for use by a
single family). I expect to use ADO.NET. The question is, should I
import the Access database into MS SQL, and redistribute it, along with
MS SQL Server Express (or is that necessary), or distribute it just as
an Access database and use the jet engine to access it. A related
question is, "Does ADO.NET support creating new databases for a given
engine?" Imagine a recipe database. It is easy enough to create a SQL
script that creates all the required tables, indices, foreign keys,
&c., but can I submit that SQL script to an ADO.NET object, along with
a file name, and have it create, e.g., an Access database with the
supplied name. Or do I have to create a database file with nothing in
it other than the schema?
I have more questions, but they'll have to wait.
Thanks
Ted
On 13 Nov, 20:40, "Ted" <r.ted.by...@.rogers.comwrote:
Quote:
Originally Posted by
I am working on two versions of an application, one of which will be a
windows forms application (which will need to be redistributable) and
the other will be a web application.
Hi there
I've come across something similar before in the days of VB6 but not
sure how well ADO.NET can deal with it, might require alot of coding.
My recommendation would be to have a common business layer which is
shared by the winforms and web applications. You can slap the two
different UIs on top which should be fairly simple. On the data side,
you can create some interfaces which will allow you to communicate with
your data using the same methods and create data access code for Access
and your other choice of server
Shout if you have any questions
Sam|||Hi Sam,
My first question is this: "If you were in my place, and wanted to
simplify deployment, would you use some of the ADO.NET classes to copy
the MS Access database to SQL Server, and then deploy SQL Server 2005
Express with your application, or use the Jet engine for both the
existing Access database and the new recipe database, or leave the
Access database as it is and create a SQL Server Express database for
the new database?"
I ask this first because a) I don't have Access so working with an
Access database is a PITA except within my Visual Studio 2005
application projects, b) IIRC the Jet engine is included with all
recent versions of Windows (at least the ones I'll support), and c)
based on my reading, I can deploy the SQL Server 2005 Express with my
application (or is this necessary - am I mistaken in assuming the SQL
Server 2005 Express is not included in the latest versions of Windows)
A second question is this: "Is the dialect of SQL used by Access the
same as that used by SQL Server 2005?" In other words, can I create a
DDL SQL script in SQL Server 2005's Management Studio that will create
my recipe database and then use ADO.NET 2 or ADO.NET 3 to submit it to
the Jet engine, along with a file name ending in mdb and have the
application properly create a NEW Access database? I ask because, with
my current suite of tools, it is trivially easy to create my new
database in SQL Server (and in a form entirely supported by the
capabilities of SQL Server Express - this db doesn't need the
capabilities in the other editions of SQL Server), but I am concerned
about how to deploy it or to make a distribution that will install
everything my application requires on a new machine (or a client's
machine). When I bought Visual Studio v6 oh so many eons ago, it came
with a utility for building distribution images that could be placed on
floppies of CDs, but I can't find the counterpart for Visual Studio
2005.
Thanks
Ted.|||Hi Ted
I dont know enough about your situation to make a suggestion yet. A
couple of questions from me:
Why do you need to use Access if you're going to install SQL Server
Express?
Is this a client requirement?
Is there alot of information? Could you use XML?
Not sure Jet is actually included with XP, think you have to install
it.
With regards to your second question, I'm afraid I dont know. I do
remember Access having a subset of commands, not sure about the latest
versions
Sam
Ted wrote:
Quote:
Originally Posted by
Hi Sam,
>
My first question is this: "If you were in my place, and wanted to
simplify deployment, would you use some of the ADO.NET classes to copy
the MS Access database to SQL Server, and then deploy SQL Server 2005
Express with your application, or use the Jet engine for both the
existing Access database and the new recipe database, or leave the
Access database as it is and create a SQL Server Express database for
the new database?"
>
I ask this first because a) I don't have Access so working with an
Access database is a PITA except within my Visual Studio 2005
application projects, b) IIRC the Jet engine is included with all
recent versions of Windows (at least the ones I'll support), and c)
based on my reading, I can deploy the SQL Server 2005 Express with my
application (or is this necessary - am I mistaken in assuming the SQL
Server 2005 Express is not included in the latest versions of Windows)
>
A second question is this: "Is the dialect of SQL used by Access the
same as that used by SQL Server 2005?" In other words, can I create a
DDL SQL script in SQL Server 2005's Management Studio that will create
my recipe database and then use ADO.NET 2 or ADO.NET 3 to submit it to
the Jet engine, along with a file name ending in mdb and have the
application properly create a NEW Access database? I ask because, with
my current suite of tools, it is trivially easy to create my new
database in SQL Server (and in a form entirely supported by the
capabilities of SQL Server Express - this db doesn't need the
capabilities in the other editions of SQL Server), but I am concerned
about how to deploy it or to make a distribution that will install
everything my application requires on a new machine (or a client's
machine). When I bought Visual Studio v6 oh so many eons ago, it came
with a utility for building distribution images that could be placed on
floppies of CDs, but I can't find the counterpart for Visual Studio
2005.
>
Thanks
>
Ted.
Quote:
Originally Posted by
Hi Ted
>
Hi Sam,
Quote:
Originally Posted by
I dont know enough about your situation to make a suggestion yet. A
couple of questions from me:
Why do you need to use Access if you're going to install SQL Server
Express?
Is this a client requirement?
Is there alot of information? Could you use XML?
>
The one database I am using is an MS Access database that has been
placed in the public domain by the USDA. It has about 80 MB of
nutrition data. I use it to allow a user to enter a recipe and obtain
an analysis of the nutrition in the prodct of the recipe, either per
serving or per 100 grams, and I support storing the recipes entered by
the user. The schema for both the USDA's nutrition database and my
recipe database is very simple. I suppose I could use XML, but I am
not sure what that buys me. The recipe database will initially be
small (actually it will be empty unless I create a few recipes and
store them as samples of what can be done). The remainder of the
application is smple. It supports creating a weeklong meal plan,
assessing the meal plans entered for how well it meets the nutritional
requirements for each member of the family (there is a window that
allows the user to enter these requirements for each member of the
family), and maintain a health diary, including what has actually been
eaten, any of the user's family's ailments and medications/remedies
used to deal with them. So, if Dad has a heart condition, Mom has
diabetes, and junior has colitis, each of their special nutritional
needs can be satisfied without Dad ;-) having to prepare three
different meals. Additionally, they can assess how well their diet and
medications or remedies serve their respective needs.
The idea of the web application is to extend this to create a global
recipe database, and opportunities for anyone who knows how to cook
earn a little money by contributing their favourite recipes to the
database and supporting people paying a pittance each time they wish to
use someone else's recipe. Of course, the option will be available for
a recipe's author to place his recipes in the public domain. This
would empower all users to try foods they may never have seen before.
I could, for example, try a desert made from lychees and longans (I'm
not sure I have the right spelling for these asian fruits) and 1) know
how to prepare it and 2) know what impact it will have on the
nutritional aspects of that week's meal plan. I don't know about you,
but I see a lot of fresh produce in the supermarkets these days that I
don't know anything about, so I don't buy them. If I had a resource of
the sort I'm trying to create, I could try them in safety.
Here you have the rationale for two versions, one accessable on the web
and the other distributable on CD and usable without access to the web.
As I see it, I either use Access databases for both the USDA data and
mine, or I use Jet to use the USDA data and SQL Server Express for my
recipe database, or I find a way to import the USDA data into SQL
Server Express and use SQL Server Express to access both the USDA data
and mine. Dealing with the web application is fairly straight forward
since I'd be running any server I'd need. But I want to make creation
of the distribution on CDs, or an image that can be downloaded from a
website, as simple as possible.
Quote:
Originally Posted by
Not sure Jet is actually included with XP, think you have to install
it.
>
I am running the 64 bit version of Windows XP Pro, and it has Jet;
either that or the professional edition of MS Visual Studio installed
it. This I know because I have already used it within a test program
that looks at the USDA data. While my application uses this to analyse
foods and recipes, my end user will never need to look at the raw
nutritional data.
Quote:
Originally Posted by
With regards to your second question, I'm afraid I dont know. I do
remember Access having a subset of commands, not sure about the latest
versions
>
Thanks
Ted|||Ted wrote:
Quote:
Originally Posted by
Hi Sam,
>
My first question is this: "If you were in my place, and wanted to
simplify deployment, would you use some of the ADO.NET classes to copy
the MS Access database to SQL Server, and then deploy SQL Server 2005
Express with your application, or use the Jet engine for both the
existing Access database and the new recipe database, or leave the
Access database as it is and create a SQL Server Express database for
the new database?"
Yes, I would have utilities or procedures that could read data in
various formats, but would store and deploy all my data in one standard
database format(such as SQL Server 2005 Express), if possible, to
simplify application development and maintenance.
Quote:
Originally Posted by
A second question is this: "Is the dialect of SQL used by Access the
same as that used by SQL Server 2005?"
No, not by a long shot. Different built-in functions (such as IIF() and
CStr() in Access vs CASE statement and CAST() in SQL Server), different
wild cards (* in Access vs % in SQL Server - and completely different
regular expressions for the LIKE statement), different data types, etc.
Some overviews of the differences:
http://sqlserver2000.databases.aspf...sql-server.html
http://www.mssqlcity.com/Articles/C...r_vs_access.htmsql
General Advice - Timesheet application
So I have been tasked with converting a pilot timesheet application (written in Access) into an all singing all dancing web app. I'm planning on using SQL Server to provide some scale but wondered if anyone had any advice about processing time calcs.
I can of course use the smalldatetime datatype to store all the start at, finish at and elapsed hours times but wonderd if there was any comparative advantage to using numerics instead and converting to/from HH:MM representation.
Maybe I need to finally figure out how to create and use custom data types?
There's lots of math to do on these things once the data is captured for reporting and such and smalldattime looks like a fairly big waste of space for something that really only deals in hours.
Anyone doen things like this before? Any advice on what not to do?
many thanks
Steve1) Use datetime
2) If necessary, denormalize your datetime field converting them in several int fields. This method sometimes is used in DW environment (with a dimension time)
I can of course use the smalldatetime datatype to store all the start at, finish at and elapsed hours times but wonderd if there was any comparative advantage to using numerics instead and converting to/from HH:MM representation.
Maybe I need to finally figure out how to create and use custom data types?
There's lots of math to do on these things once the data is captured for reporting and such and smalldattime looks like a fairly big waste of space for something that really only deals in hours.
Anyone doen things like this before? Any advice on what not to do?
many thanks
Steve1) Use datetime
2) If necessary, denormalize your datetime field converting them in several int fields. This method sometimes is used in DW environment (with a dimension time)
General Access Project info
Hi all, we currently have an access databse with linked odbc connections to an SQL server database.
Performance wise I don't think this is great.
Anyone have any general information about doing an upgrade to a micrsoft project database, and what are the pros and cons of access projects compared to Access databases with linked tables.
About 30 -40 users currently hammer the access front end during the day.Access Data Projects are MUCH better than Access MDB databases with linked tables. Off the top of my head I can't think of any advantage to using an MDB with linked tables instead of going with an ADP file.|||OK, one disadvantage: You cannot store ANY tables locally in an Access ADP file, so any setup or configuration data cannot be stored on the user's machine (at least in the database file).
Performance wise I don't think this is great.
Anyone have any general information about doing an upgrade to a micrsoft project database, and what are the pros and cons of access projects compared to Access databases with linked tables.
About 30 -40 users currently hammer the access front end during the day.Access Data Projects are MUCH better than Access MDB databases with linked tables. Off the top of my head I can't think of any advantage to using an MDB with linked tables instead of going with an ADP file.|||OK, one disadvantage: You cannot store ANY tables locally in an Access ADP file, so any setup or configuration data cannot be stored on the user's machine (at least in the database file).
gatting at my data!
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
' why can't I access the database connected as Jim '
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
' why can't I access the data connected as Jim '
Jim BuntonHi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table wi
th name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW
5.12732@.fe3.news.blueyonder.co.uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
' why can't I access the database connected as Jim '
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
' why can't I access the data connected as Jim '
Jim Bunton|||Hi Hari - thanks for your clear response to my posting
enterprise manager reports that I have created a tabale 'Companies' whose ow
ner is Jim
sqlQueryAnalyser reports that SolutionsNet includes the table Jim.Companies
- I an assuming that this means there is a table called companies whose owne
r is Jim.
When connected as Jim(SqlQueryAnalyser) both
select * from companies
or
select * from jim.companies
return the error:-
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
After using dbo to dropt the table
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50)NOT NULL,
. . . .
Nothing has changed! same results as before
Drop the table
connect as Jim and run:-
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname
. . . .
The command(s) completed successfully.
Run SqlEnterprise manager - reports the table Companies owner Jim
as 'Jim' I still can't access the table
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
But I can as sa/dbo
BUT if I create a Windows Authentification user [Study/Jim Bunton] and c
onnect then I can access the table!!! - Select user_name informs me that I'm
dbo [I can still access the table whether or not Select . . permission
s are set tick or cross.
I'm totally mystified!
NOW? - Might it be because I am running SQL Server Personal edition'
[I am not running win 2k server - just win 2k]
NB
[The irony is that on an .asp page I have totally failed to get a Truste
d Connection (windows authentification] to work for Northwind and resorted t
o using the datase authentification!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:ONwZyb2yFHA.
2348@.TK2MSFTNGP15.phx.gbl...
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table wi
th name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW
5.12732@.fe3.news.blueyonder.co.uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
' why can't I access the database connected as Jim '
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
' why can't I access the data connected as Jim '
Jim Bunton
Problem accessing tables in SQL Query Analyser:-
' why can't I access the database connected as Jim '
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
' why can't I access the data connected as Jim '
Jim BuntonHi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table wi
th name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW
5.12732@.fe3.news.blueyonder.co.uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
' why can't I access the database connected as Jim '
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
' why can't I access the data connected as Jim '
Jim Bunton|||Hi Hari - thanks for your clear response to my posting
enterprise manager reports that I have created a tabale 'Companies' whose ow
ner is Jim
sqlQueryAnalyser reports that SolutionsNet includes the table Jim.Companies
- I an assuming that this means there is a table called companies whose owne
r is Jim.
When connected as Jim(SqlQueryAnalyser) both
select * from companies
or
select * from jim.companies
return the error:-
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
After using dbo to dropt the table
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50)NOT NULL,
. . . .
Nothing has changed! same results as before
Drop the table
connect as Jim and run:-
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname
. . . .
The command(s) completed successfully.
Run SqlEnterprise manager - reports the table Companies owner Jim
as 'Jim' I still can't access the table
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
But I can as sa/dbo
BUT if I create a Windows Authentification user [Study/Jim Bunton] and c
onnect then I can access the table!!! - Select user_name informs me that I'm
dbo [I can still access the table whether or not Select . . permission
s are set tick or cross.
I'm totally mystified!
NOW? - Might it be because I am running SQL Server Personal edition'
[I am not running win 2k server - just win 2k]
NB
[The irony is that on an .asp page I have totally failed to get a Truste
d Connection (windows authentification] to work for Northwind and resorted t
o using the datase authentification!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:ONwZyb2yFHA.
2348@.TK2MSFTNGP15.phx.gbl...
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table wi
th name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW
5.12732@.fe3.news.blueyonder.co.uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
' why can't I access the database connected as Jim '
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
' why can't I access the data connected as Jim '
Jim Bunton
gatting at my data!
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table with name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW5.12732@.fe3.news.blueyonder.co. uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
|||Hi Hari - thanks for your clear response to my posting
enterprise manager reports that I have created a tabale 'Companies' whose owner is Jim
sqlQueryAnalyser reports that SolutionsNet includes the table Jim.Companies - I an assuming that this means there is a table called companies whose owner is Jim.
When connected as Jim(SqlQueryAnalyser) both
select * from companies
or
select * from jim.companies
return the error:-
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
After using dbo to dropt the table
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50)NOT NULL,
.. . . .
Nothing has changed! same results as before
Drop the table
connect as Jim and run:-
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname
. . . .
The command(s) completed successfully.
Run SqlEnterprise manager - reports the table Companies owner Jim
as 'Jim' I still can't access the table
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
But I can as sa/dbo
BUT if I create a Windows Authentification user [Study/Jim Bunton] and connect then I can access the table!!! - Select user_name informs me that I'm dbo [I can still access the table whether or not Select . . permissions are set tick or cross.
I'm totally mystified!
NOW? - Might it be because I am running SQL Server Personal edition?
[I am not running win 2k server - just win 2k]
NB
[The irony is that on an .asp page I have totally failed to get a Trusted Connection (windows authentification] to work for Northwind and resorted to using the datase authentification!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:ONwZyb2yFHA.2348@.TK2MSFTNGP15.phx.gbl...
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table with name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW5.12732@.fe3.news.blueyonder.co. uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table with name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW5.12732@.fe3.news.blueyonder.co. uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
|||Hi Hari - thanks for your clear response to my posting
enterprise manager reports that I have created a tabale 'Companies' whose owner is Jim
sqlQueryAnalyser reports that SolutionsNet includes the table Jim.Companies - I an assuming that this means there is a table called companies whose owner is Jim.
When connected as Jim(SqlQueryAnalyser) both
select * from companies
or
select * from jim.companies
return the error:-
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
After using dbo to dropt the table
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50)NOT NULL,
.. . . .
Nothing has changed! same results as before
Drop the table
connect as Jim and run:-
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname
. . . .
The command(s) completed successfully.
Run SqlEnterprise manager - reports the table Companies owner Jim
as 'Jim' I still can't access the table
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
But I can as sa/dbo
BUT if I create a Windows Authentification user [Study/Jim Bunton] and connect then I can access the table!!! - Select user_name informs me that I'm dbo [I can still access the table whether or not Select . . permissions are set tick or cross.
I'm totally mystified!
NOW? - Might it be because I am running SQL Server Personal edition?
[I am not running win 2k server - just win 2k]
NB
[The irony is that on an .asp page I have totally failed to get a Trusted Connection (windows authentification] to work for Northwind and resorted to using the datase authentification!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:ONwZyb2yFHA.2348@.TK2MSFTNGP15.phx.gbl...
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table with name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW5.12732@.fe3.news.blueyonder.co. uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
gateway dropping SQL Connection
Hi guys,
I need some serious help from any of you geniuses here. I have a
problem with an Access 2000 adp connection. I have users on that site
going through a gateway to communicate to our server and we are having
major connection problems. It seems that something on the network or
the gateway keeps dropping our connection to the sql 2000 server.
Is there any setting or even anything on the gateway hardware itself
that would be responsible for doing this? I would need to be able to
instruct the Network tech on what to do since I don't have any admin
rights to do it myself and the network tech might not know what to do.
Thanks in advance
bump
Marvinq wrote:
> Hi guys,
> I need some serious help from any of you geniuses here. I have a
> problem with an Access 2000 adp connection. I have users on that site
> going through a gateway to communicate to our server and we are having
> major connection problems. It seems that something on the network or
> the gateway keeps dropping our connection to the sql 2000 server.
> Is there any setting or even anything on the gateway hardware itself
> that would be responsible for doing this? I would need to be able to
> instruct the Network tech on what to do since I don't have any admin
> rights to do it myself and the network tech might not know what to do.
> Thanks in advance
sql
I need some serious help from any of you geniuses here. I have a
problem with an Access 2000 adp connection. I have users on that site
going through a gateway to communicate to our server and we are having
major connection problems. It seems that something on the network or
the gateway keeps dropping our connection to the sql 2000 server.
Is there any setting or even anything on the gateway hardware itself
that would be responsible for doing this? I would need to be able to
instruct the Network tech on what to do since I don't have any admin
rights to do it myself and the network tech might not know what to do.
Thanks in advance
bump
Marvinq wrote:
> Hi guys,
> I need some serious help from any of you geniuses here. I have a
> problem with an Access 2000 adp connection. I have users on that site
> going through a gateway to communicate to our server and we are having
> major connection problems. It seems that something on the network or
> the gateway keeps dropping our connection to the sql 2000 server.
> Is there any setting or even anything on the gateway hardware itself
> that would be responsible for doing this? I would need to be able to
> instruct the Network tech on what to do since I don't have any admin
> rights to do it myself and the network tech might not know what to do.
> Thanks in advance
sql
gateway dropping SQL Connection
Hi guys,
I need some serious help from any of you geniuses here. I have a
problem with an Access 2000 adp connection. I have users on that site
going through a gateway to communicate to our server and we are having
major connection problems. It seems that something on the network or
the gateway keeps dropping our connection to the sql 2000 server.
Is there any setting or even anything on the gateway hardware itself
that would be responsible for doing this? I would need to be able to
instruct the Network tech on what to do since I don't have any admin
rights to do it myself and the network tech might not know what to do.
Thanks in advancebump
Marvinq wrote:
> Hi guys,
> I need some serious help from any of you geniuses here. I have a
> problem with an Access 2000 adp connection. I have users on that site
> going through a gateway to communicate to our server and we are having
> major connection problems. It seems that something on the network or
> the gateway keeps dropping our connection to the sql 2000 server.
> Is there any setting or even anything on the gateway hardware itself
> that would be responsible for doing this? I would need to be able to
> instruct the Network tech on what to do since I don't have any admin
> rights to do it myself and the network tech might not know what to do.
> Thanks in advance
I need some serious help from any of you geniuses here. I have a
problem with an Access 2000 adp connection. I have users on that site
going through a gateway to communicate to our server and we are having
major connection problems. It seems that something on the network or
the gateway keeps dropping our connection to the sql 2000 server.
Is there any setting or even anything on the gateway hardware itself
that would be responsible for doing this? I would need to be able to
instruct the Network tech on what to do since I don't have any admin
rights to do it myself and the network tech might not know what to do.
Thanks in advancebump
Marvinq wrote:
> Hi guys,
> I need some serious help from any of you geniuses here. I have a
> problem with an Access 2000 adp connection. I have users on that site
> going through a gateway to communicate to our server and we are having
> major connection problems. It seems that something on the network or
> the gateway keeps dropping our connection to the sql 2000 server.
> Is there any setting or even anything on the gateway hardware itself
> that would be responsible for doing this? I would need to be able to
> instruct the Network tech on what to do since I don't have any admin
> rights to do it myself and the network tech might not know what to do.
> Thanks in advance
gateway dropping SQL Connection
Hi guys,
I need some serious help from any of you geniuses here. I have a
problem with an Access 2000 adp connection. I have users on that site
going through a gateway to communicate to our server and we are having
major connection problems. It seems that something on the network or
the gateway keeps dropping our connection to the sql 2000 server.
Is there any setting or even anything on the gateway hardware itself
that would be responsible for doing this? I would need to be able to
instruct the Network tech on what to do since I don't have any admin
rights to do it myself and the network tech might not know what to do.
Thanks in advancebump
Marvinq wrote:
> Hi guys,
> I need some serious help from any of you geniuses here. I have a
> problem with an Access 2000 adp connection. I have users on that site
> going through a gateway to communicate to our server and we are having
> major connection problems. It seems that something on the network or
> the gateway keeps dropping our connection to the sql 2000 server.
> Is there any setting or even anything on the gateway hardware itself
> that would be responsible for doing this? I would need to be able to
> instruct the Network tech on what to do since I don't have any admin
> rights to do it myself and the network tech might not know what to do.
> Thanks in advance
I need some serious help from any of you geniuses here. I have a
problem with an Access 2000 adp connection. I have users on that site
going through a gateway to communicate to our server and we are having
major connection problems. It seems that something on the network or
the gateway keeps dropping our connection to the sql 2000 server.
Is there any setting or even anything on the gateway hardware itself
that would be responsible for doing this? I would need to be able to
instruct the Network tech on what to do since I don't have any admin
rights to do it myself and the network tech might not know what to do.
Thanks in advancebump
Marvinq wrote:
> Hi guys,
> I need some serious help from any of you geniuses here. I have a
> problem with an Access 2000 adp connection. I have users on that site
> going through a gateway to communicate to our server and we are having
> major connection problems. It seems that something on the network or
> the gateway keeps dropping our connection to the sql 2000 server.
> Is there any setting or even anything on the gateway hardware itself
> that would be responsible for doing this? I would need to be able to
> instruct the Network tech on what to do since I don't have any admin
> rights to do it myself and the network tech might not know what to do.
> Thanks in advance
Gap filling in a time series
Hello,
I am new to SQL Server and learning lots very quickly! I am experienced at building databases in Access and using VBA in Access and Excel.
I have a time series of 1440 records that may have some gaps in it. I need to check the time series for gaps and then fill these or reject the time series.
The criteria for accepting and rejecting is a user defined number of time steps from 1 to 10. For example, if the user sets the maximum gap as 5 time steps and a gap has 5 or less then I simply want to lineraly interpolate betwen the two timesteps bounding the gap. If the gap is 6 time steps then I will reject the timeseries.
I have searched the BOL and MSDN for SQL Server and think there must be a solution using the PredictTimeSeries in DMX, but not quite sure if I can do this. I may be better off simply passing through the time series as a recordset and processing as I would have done in Access...(I am reluctant to do this as I have of the order 100 * 5 * 365 time series and growng by 100 each day and fear it will take quite some time...)
Can anyone help me by pointing me in the right direction please?
Unless there is a way of using PredictTimeSeries on its own, I think the solution is:
Identify if a record is the a valid one or part of a gap (ie missing values).
Identify the longest gap and reject or process data on this value.
Identify if a record preceedes or succeeds a gap.
For each gap fill it using a linear interpolation.
Thanks,
Alan.
Hello,
Thank you for reading the problem above. I have moved on one step and decided that I need to store the data in a 2-d matrix with time as one dimension and space as another. I will then have to interpolate in both time and space. Also we think that we can work with a subset of just over half of the data so we are going to read the data in to an array and process it that way.
I am still interested in knowing if the DMX functions could be used to fill multiple variable length gaps in a table. Any links would be appreciated.
Thanks,
Alan.
|||I don't think this would be a good application for the time series algorithm. You would have to create a new TS model for each series leading up to each gap and then do a prediction after creating the model. Given the data, the time series may not be able to fill the gap - since the algorithm stops predicting when it determines the predictions are unstable.
We are working on changes to the time series algorithm that may make it more suitable for such problems, but I can't share the details as of yet.
Gap filling in a time series
Hello,
I am new to SQL Server and learning lots very quickly! I am experienced at building databases in Access and using VBA in Access and Excel.
I have a time series of 1440 records that may have some gaps in it. I need to check the time series for gaps and then fill these or reject the time series.
The criteria for accepting and rejecting is a user defined number of time steps from 1 to 10. For example, if the user sets the maximum gap as 5 time steps and a gap has 5 or less then I simply want to lineraly interpolate betwen the two timesteps bounding the gap. If the gap is 6 time steps then I will reject the timeseries.
I have searched the BOL and MSDN for SQL Server and think there must be a solution using the PredictTimeSeries in DMX, but not quite sure if I can do this. I may be better off simply passing through the time series as a recordset and processing as I would have done in Access...(I am reluctant to do this as I have of the order 100 * 5 * 365 time series and growng by 100 each day and fear it will take quite some time...)
Can anyone help me by pointing me in the right direction please?
Unless there is a way of using PredictTimeSeries on its own, I think the solution is:
- Identify if a record is the a valid one or part of a gap (ie missing values). Identify the longest gap and reject or process data on this value. Identify if a record preceedes or succeeds a gap. For each gap fill it using a linear interpolation.
Thanks,
Alan.
Hello,
Thank you for reading the problem above. I have moved on one step and decided that I need to store the data in a 2-d matrix with time as one dimension and space as another. I will then have to interpolate in both time and space. Also we think that we can work with a subset of just over half of the data so we are going to read the data in to an array and process it that way.
I am still interested in knowing if the DMX functions could be used to fill multiple variable length gaps in a table. Any links would be appreciated.
Thanks,
Alan.
|||I don't think this would be a good application for the time series algorithm. You would have to create a new TS model for each series leading up to each gap and then do a prediction after creating the model. Given the data, the time series may not be able to fill the gap - since the algorithm stops predicting when it determines the predictions are unstable.
We are working on changes to the time series algorithm that may make it more suitable for such problems, but I can't share the details as of yet.
Friday, March 23, 2012
Gaining complete exclusive access to a given table
Hi Group
While I'm fully aware that there are transations and that stuff like
this should be left to the server administration tools etc. I'm still
facing the problem of writing a middle ware where the existing,
unchangeabel upper layer application want's to get exclusive access
over a complete table and I have to emulate this behaviour in my
module. I figure I could select the complete table in a transaction to
do so, but I have a feeling that this might be pretty slow? Or is such
an apraoch sufficently fast? What better alternatives do I have?
TIA
MarkusWhat exactly are you trying to achieve?
Lock a table inside a transaction? Use locking hints or serializable
isolation level.
We can't give you an accurate answer to a vague question.
ML
http://milambda.blogspot.com/|||On Thu, 27 Apr 2006 15:47:12 +0200, Markus Zingg wrote:
>Hi Group
>While I'm fully aware that there are transations and that stuff like
>this should be left to the server administration tools etc. I'm still
>facing the problem of writing a middle ware where the existing,
>unchangeabel upper layer application want's to get exclusive access
>over a complete table and I have to emulate this behaviour in my
>module. I figure I could select the complete table in a transaction to
>do so, but I have a feeling that this might be pretty slow? Or is such
>an apraoch sufficently fast? What better alternatives do I have?
Hi Markus,
Check out the TABLOCK and HOLDLOCK update hints. They might be what
you're after.
(I assume that you *are* aware that implementing this instantly kills
any chance of ever scaling your app, are yoou?)
Hugo Kornelis, SQL Server MVPsql
While I'm fully aware that there are transations and that stuff like
this should be left to the server administration tools etc. I'm still
facing the problem of writing a middle ware where the existing,
unchangeabel upper layer application want's to get exclusive access
over a complete table and I have to emulate this behaviour in my
module. I figure I could select the complete table in a transaction to
do so, but I have a feeling that this might be pretty slow? Or is such
an apraoch sufficently fast? What better alternatives do I have?
TIA
MarkusWhat exactly are you trying to achieve?
Lock a table inside a transaction? Use locking hints or serializable
isolation level.
We can't give you an accurate answer to a vague question.
ML
http://milambda.blogspot.com/|||On Thu, 27 Apr 2006 15:47:12 +0200, Markus Zingg wrote:
>Hi Group
>While I'm fully aware that there are transations and that stuff like
>this should be left to the server administration tools etc. I'm still
>facing the problem of writing a middle ware where the existing,
>unchangeabel upper layer application want's to get exclusive access
>over a complete table and I have to emulate this behaviour in my
>module. I figure I could select the complete table in a transaction to
>do so, but I have a feeling that this might be pretty slow? Or is such
>an apraoch sufficently fast? What better alternatives do I have?
Hi Markus,
Check out the TABLOCK and HOLDLOCK update hints. They might be what
you're after.
(I assume that you *are* aware that implementing this instantly kills
any chance of ever scaling your app, are yoou?)
Hugo Kornelis, SQL Server MVPsql
Fyi
Hi everyone I have discovered half of my problems were due to Access 2000 apparently Access 2000 was created before SQL Server 2000 so when you try to execute a Stored Procedure through a command button and all you get are error messages its due to Access not understanding the commands. Alot of the problems I was having was due to Access 2000 now that I have Access XP I'm WALKIN ON SUNSHINE WEEELLLLLL!!! SQL Tells it to sit up, Beg, Fetch and XP just does it no questions asked...WOOHOOO
Just a little FYIand don't it feel good?
Just a little FYIand don't it feel good?
Monday, March 19, 2012
Funny network behaviour
Hi,
In order to establish a security enhanced SQL server setup, I tried
to switch off network access by disabling all networking protocols,
so that the server can only be reached through a pipe. Nevertheless
the server was visible in the network and could be accessed from
all clients. Does anybody know what is going on here?
GeorgGeorg Kreyerhoff wrote:
> Hi,
> In order to establish a security enhanced SQL server setup, I tried
> to switch off network access by disabling all networking protocols,
> so that the server can only be reached through a pipe. Nevertheless
> the server was visible in the network and could be accessed from
> all clients. Does anybody know what is going on here?
>
some more information on what I found out:
Even if tcp-ip and all other networking protocols are disabled in the
SQL-server configuration (only named pipes were enabled), Clients and Server
seem to be able to communicate via the Netbios-ports (Netbios-service
was still enabled on the machine). Do named pipes work over Netbios?
Or what is going on?
Georg
Friday, February 24, 2012
Full-Text Search is not enabled for the current database
We are having problems with Full-Text searching...
Recently we installed a SQL application that needs to access full-text
searching.
It gave the following error:
"The crawl seed <MSSQL75://SQLServer/0539c240> in project <SQLServer
SQL0001400006> cannot be accessed. Error: 8007052e - Logon failure: unknown
user name or bad password."
Upgrading from SQL2kSP3a to SQL2kSP4 didn't change our situation.
Changing to system account following instructions in KB277549 also didn't
fix the problem.
We decided to reinstall the MSSearch service following commands in KB827449.
We received the following error:
"Full-Text Search is not enabled for the current database. Use
sp_fulltext_database to enable full-text search for the database."
Then we ran EXEC sp_fulltext_database 'enable'.
This didn't resolve the problem as we received error message:
"Microsoft search service cannot be administered under present user account."
Current Status recap:
* MSSearch service is started
* In EM > Support Services > Full Text Search is running
* In EM > R-click on Full-Text Catalogs > Rebuild All Catalogs produces
error:
"Microsoft search service cannot be administered under present user account."
* In EM > R-click on catalog within Full-Text Catalog > Rebuild Catalog
produces error:
"Full-Text Search is not enabled for the current database. Use
sp_fulltext_database to enable full-text search for the database."
Not sure if this is user account related or bad install of MSSearch.
Before reloading SQL or contacting MS Support, any advice is appreciated.
Thanks!
D Krage,
As you have reviewed KB 277549 (Q277549) PRB: Unable to Build Full-Text
Catalog After You Modify MSSQLServer Logon Account Through [NT4.0) Control
Panel [or Win2K Component Services] at
http://support.microsoft.com/default...;EN-US;277549, did you do
all of the "Steps to Reproduce Behavior", including stopping & re-starting
the MSSearch & MSSQLServer?
The error you're getting (Logon failure: unknown user name or bad password)
is typical of the above. Additionally, have you or anyone else removed or
altered the BUILTIN\Administrators login? If so, can you re-add it or alter
it back to its original values, including the master database and with
sysadmin rights?
You may also want to review KB article 317746 "PRB: SQL Server Full-Text
Search Does Not Populate Catalogs" at
http://support.microsoft.com/default...b;en-us;317746
However, since you've moved ahead with re-installing the MSSearch components
via the procedures in KB 827449, the above most likely no longer applies. At
this time, the following is the primary error: "Microsoft search service
cannot be administered under present user account." and usually, this error
indicates a missing or altered BUILTIN\Administrators login, but you might
also want to checkout the steps at
http://www.doughughes.net/index.cfm/...nk/entryId-126 as others have
had success with this method in correcting this error.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"d krage" <d krage@.discussions.microsoft.com> wrote in message
news:4C845DC5-B0D7-4F45-A0FC-FB3DCF5F8427@.microsoft.com...
> We are having problems with Full-Text searching...
> Recently we installed a SQL application that needs to access full-text
> searching.
> It gave the following error:
> "The crawl seed <MSSQL75://SQLServer/0539c240> in project <SQLServer
> SQL0001400006> cannot be accessed. Error: 8007052e - Logon failure:
> unknown
> user name or bad password."
> Upgrading from SQL2kSP3a to SQL2kSP4 didn't change our situation.
> Changing to system account following instructions in KB277549 also didn't
> fix the problem.
> We decided to reinstall the MSSearch service following commands in
> KB827449.
> We received the following error:
> "Full-Text Search is not enabled for the current database. Use
> sp_fulltext_database to enable full-text search for the database."
> Then we ran EXEC sp_fulltext_database 'enable'.
> This didn't resolve the problem as we received error message:
> "Microsoft search service cannot be administered under present user
> account."
> Current Status recap:
> * MSSearch service is started
> * In EM > Support Services > Full Text Search is running
> * In EM > R-click on Full-Text Catalogs > Rebuild All Catalogs produces
> error:
> "Microsoft search service cannot be administered under present user
> account."
> * In EM > R-click on catalog within Full-Text Catalog > Rebuild Catalog
> produces error:
> "Full-Text Search is not enabled for the current database. Use
> sp_fulltext_database to enable full-text search for the database."
> Not sure if this is user account related or bad install of MSSearch.
> Before reloading SQL or contacting MS Support, any advice is appreciated.
> Thanks!
|||Thanks for your reply John. As it turns out the error indicated a problem
with user rights, however, the problem turns out to be a bad install of
MSSearch.
Here is what corrected the problem...from Google groups.
http://groups.google.com/group/micro...b7c7692a0e2ff1
According to Google search results, there should be a
HKLM\Software\Microsoft\Search\1.0\Applications\SQ L Server key. This did not
exist, but existed on two of our other servers. The article said to go to
regedit under HKLM\Software\Microsoft\MSSQLServer\Tracking and rename the key
{E07FDDA7-5A21-11d2-9DAD-00C04F79D434}. I renamed the key by putting an X in
front of the name. This makes the SQL Server think that the Search service is
not installed with SQL Server.
After this, shut down all affected services and ran the SQL 2000 install
from CD and did a Custom to modify the installation. All components were
already checked except the Search, so checked the search. Did not uncheck
other components or this would uninstall the other components.
After doing this and rebooting, catalog creation appeared to work. Added
item to database and number of items in catalog increased. Reapplied Service
pack 4 to update the SQL search files. Rebooted. Our SQL app search function
is now working.
Hope this can help someone else out there.
d Krage
"John Kane" wrote:
> D Krage,
> As you have reviewed KB 277549 (Q277549) PRB: Unable to Build Full-Text
> Catalog After You Modify MSSQLServer Logon Account Through [NT4.0) Control
> Panel [or Win2K Component Services] at
> http://support.microsoft.com/default...;EN-US;277549, did you do
> all of the "Steps to Reproduce Behavior", including stopping & re-starting
> the MSSearch & MSSQLServer?
> The error you're getting (Logon failure: unknown user name or bad password)
> is typical of the above. Additionally, have you or anyone else removed or
> altered the BUILTIN\Administrators login? If so, can you re-add it or alter
> it back to its original values, including the master database and with
> sysadmin rights?
> You may also want to review KB article 317746 "PRB: SQL Server Full-Text
> Search Does Not Populate Catalogs" at
> http://support.microsoft.com/default...b;en-us;317746
> However, since you've moved ahead with re-installing the MSSearch components
> via the procedures in KB 827449, the above most likely no longer applies. At
> this time, the following is the primary error: "Microsoft search service
> cannot be administered under present user account." and usually, this error
> indicates a missing or altered BUILTIN\Administrators login, but you might
> also want to checkout the steps at
> http://www.doughughes.net/index.cfm/...nk/entryId-126 as others have
> had success with this method in correcting this error.
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "d krage" <d krage@.discussions.microsoft.com> wrote in message
> news:4C845DC5-B0D7-4F45-A0FC-FB3DCF5F8427@.microsoft.com...
>
>
|||You're welcome, d Krage,
After reviewing the Google groups link & thread as that was an old one from
back in 2001 as I was the person who helped Chris with "The solution that
John found to my problem was this:" back when I was fulltime at Microsoft...
I'm now a bit older and wiser, but it still is good to be remembered!
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"d krage" <dkrage@.discussions.microsoft.com> wrote in message
news:8F7D817F-B35C-4689-97E4-C79B711842B8@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply John. As it turns out the error indicated a problem
> with user rights, however, the problem turns out to be a bad install of
> MSSearch.
> Here is what corrected the problem...from Google groups.
> http://groups.google.com/group/micro...b7c7692a0e2ff1
> According to Google search results, there should be a
> HKLM\Software\Microsoft\Search\1.0\Applications\SQ L Server key. This did
> not
> exist, but existed on two of our other servers. The article said to go to
> regedit under HKLM\Software\Microsoft\MSSQLServer\Tracking and rename the
> key
> {E07FDDA7-5A21-11d2-9DAD-00C04F79D434}. I renamed the key by putting an X
> in
> front of the name. This makes the SQL Server think that the Search service
> is
> not installed with SQL Server.
> After this, shut down all affected services and ran the SQL 2000 install
> from CD and did a Custom to modify the installation. All components were
> already checked except the Search, so checked the search. Did not uncheck
> other components or this would uninstall the other components.
> After doing this and rebooting, catalog creation appeared to work. Added
> item to database and number of items in catalog increased. Reapplied
> Service
> pack 4 to update the SQL search files. Rebooted. Our SQL app search
> function
> is now working.
> Hope this can help someone else out there.
> d Krage
> "John Kane" wrote:
Recently we installed a SQL application that needs to access full-text
searching.
It gave the following error:
"The crawl seed <MSSQL75://SQLServer/0539c240> in project <SQLServer
SQL0001400006> cannot be accessed. Error: 8007052e - Logon failure: unknown
user name or bad password."
Upgrading from SQL2kSP3a to SQL2kSP4 didn't change our situation.
Changing to system account following instructions in KB277549 also didn't
fix the problem.
We decided to reinstall the MSSearch service following commands in KB827449.
We received the following error:
"Full-Text Search is not enabled for the current database. Use
sp_fulltext_database to enable full-text search for the database."
Then we ran EXEC sp_fulltext_database 'enable'.
This didn't resolve the problem as we received error message:
"Microsoft search service cannot be administered under present user account."
Current Status recap:
* MSSearch service is started
* In EM > Support Services > Full Text Search is running
* In EM > R-click on Full-Text Catalogs > Rebuild All Catalogs produces
error:
"Microsoft search service cannot be administered under present user account."
* In EM > R-click on catalog within Full-Text Catalog > Rebuild Catalog
produces error:
"Full-Text Search is not enabled for the current database. Use
sp_fulltext_database to enable full-text search for the database."
Not sure if this is user account related or bad install of MSSearch.
Before reloading SQL or contacting MS Support, any advice is appreciated.
Thanks!
D Krage,
As you have reviewed KB 277549 (Q277549) PRB: Unable to Build Full-Text
Catalog After You Modify MSSQLServer Logon Account Through [NT4.0) Control
Panel [or Win2K Component Services] at
http://support.microsoft.com/default...;EN-US;277549, did you do
all of the "Steps to Reproduce Behavior", including stopping & re-starting
the MSSearch & MSSQLServer?
The error you're getting (Logon failure: unknown user name or bad password)
is typical of the above. Additionally, have you or anyone else removed or
altered the BUILTIN\Administrators login? If so, can you re-add it or alter
it back to its original values, including the master database and with
sysadmin rights?
You may also want to review KB article 317746 "PRB: SQL Server Full-Text
Search Does Not Populate Catalogs" at
http://support.microsoft.com/default...b;en-us;317746
However, since you've moved ahead with re-installing the MSSearch components
via the procedures in KB 827449, the above most likely no longer applies. At
this time, the following is the primary error: "Microsoft search service
cannot be administered under present user account." and usually, this error
indicates a missing or altered BUILTIN\Administrators login, but you might
also want to checkout the steps at
http://www.doughughes.net/index.cfm/...nk/entryId-126 as others have
had success with this method in correcting this error.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"d krage" <d krage@.discussions.microsoft.com> wrote in message
news:4C845DC5-B0D7-4F45-A0FC-FB3DCF5F8427@.microsoft.com...
> We are having problems with Full-Text searching...
> Recently we installed a SQL application that needs to access full-text
> searching.
> It gave the following error:
> "The crawl seed <MSSQL75://SQLServer/0539c240> in project <SQLServer
> SQL0001400006> cannot be accessed. Error: 8007052e - Logon failure:
> unknown
> user name or bad password."
> Upgrading from SQL2kSP3a to SQL2kSP4 didn't change our situation.
> Changing to system account following instructions in KB277549 also didn't
> fix the problem.
> We decided to reinstall the MSSearch service following commands in
> KB827449.
> We received the following error:
> "Full-Text Search is not enabled for the current database. Use
> sp_fulltext_database to enable full-text search for the database."
> Then we ran EXEC sp_fulltext_database 'enable'.
> This didn't resolve the problem as we received error message:
> "Microsoft search service cannot be administered under present user
> account."
> Current Status recap:
> * MSSearch service is started
> * In EM > Support Services > Full Text Search is running
> * In EM > R-click on Full-Text Catalogs > Rebuild All Catalogs produces
> error:
> "Microsoft search service cannot be administered under present user
> account."
> * In EM > R-click on catalog within Full-Text Catalog > Rebuild Catalog
> produces error:
> "Full-Text Search is not enabled for the current database. Use
> sp_fulltext_database to enable full-text search for the database."
> Not sure if this is user account related or bad install of MSSearch.
> Before reloading SQL or contacting MS Support, any advice is appreciated.
> Thanks!
|||Thanks for your reply John. As it turns out the error indicated a problem
with user rights, however, the problem turns out to be a bad install of
MSSearch.
Here is what corrected the problem...from Google groups.
http://groups.google.com/group/micro...b7c7692a0e2ff1
According to Google search results, there should be a
HKLM\Software\Microsoft\Search\1.0\Applications\SQ L Server key. This did not
exist, but existed on two of our other servers. The article said to go to
regedit under HKLM\Software\Microsoft\MSSQLServer\Tracking and rename the key
{E07FDDA7-5A21-11d2-9DAD-00C04F79D434}. I renamed the key by putting an X in
front of the name. This makes the SQL Server think that the Search service is
not installed with SQL Server.
After this, shut down all affected services and ran the SQL 2000 install
from CD and did a Custom to modify the installation. All components were
already checked except the Search, so checked the search. Did not uncheck
other components or this would uninstall the other components.
After doing this and rebooting, catalog creation appeared to work. Added
item to database and number of items in catalog increased. Reapplied Service
pack 4 to update the SQL search files. Rebooted. Our SQL app search function
is now working.
Hope this can help someone else out there.
d Krage
"John Kane" wrote:
> D Krage,
> As you have reviewed KB 277549 (Q277549) PRB: Unable to Build Full-Text
> Catalog After You Modify MSSQLServer Logon Account Through [NT4.0) Control
> Panel [or Win2K Component Services] at
> http://support.microsoft.com/default...;EN-US;277549, did you do
> all of the "Steps to Reproduce Behavior", including stopping & re-starting
> the MSSearch & MSSQLServer?
> The error you're getting (Logon failure: unknown user name or bad password)
> is typical of the above. Additionally, have you or anyone else removed or
> altered the BUILTIN\Administrators login? If so, can you re-add it or alter
> it back to its original values, including the master database and with
> sysadmin rights?
> You may also want to review KB article 317746 "PRB: SQL Server Full-Text
> Search Does Not Populate Catalogs" at
> http://support.microsoft.com/default...b;en-us;317746
> However, since you've moved ahead with re-installing the MSSearch components
> via the procedures in KB 827449, the above most likely no longer applies. At
> this time, the following is the primary error: "Microsoft search service
> cannot be administered under present user account." and usually, this error
> indicates a missing or altered BUILTIN\Administrators login, but you might
> also want to checkout the steps at
> http://www.doughughes.net/index.cfm/...nk/entryId-126 as others have
> had success with this method in correcting this error.
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "d krage" <d krage@.discussions.microsoft.com> wrote in message
> news:4C845DC5-B0D7-4F45-A0FC-FB3DCF5F8427@.microsoft.com...
>
>
|||You're welcome, d Krage,
After reviewing the Google groups link & thread as that was an old one from
back in 2001 as I was the person who helped Chris with "The solution that
John found to my problem was this:" back when I was fulltime at Microsoft...
I'm now a bit older and wiser, but it still is good to be remembered!
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"d krage" <dkrage@.discussions.microsoft.com> wrote in message
news:8F7D817F-B35C-4689-97E4-C79B711842B8@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply John. As it turns out the error indicated a problem
> with user rights, however, the problem turns out to be a bad install of
> MSSearch.
> Here is what corrected the problem...from Google groups.
> http://groups.google.com/group/micro...b7c7692a0e2ff1
> According to Google search results, there should be a
> HKLM\Software\Microsoft\Search\1.0\Applications\SQ L Server key. This did
> not
> exist, but existed on two of our other servers. The article said to go to
> regedit under HKLM\Software\Microsoft\MSSQLServer\Tracking and rename the
> key
> {E07FDDA7-5A21-11d2-9DAD-00C04F79D434}. I renamed the key by putting an X
> in
> front of the name. This makes the SQL Server think that the Search service
> is
> not installed with SQL Server.
> After this, shut down all affected services and ran the SQL 2000 install
> from CD and did a Custom to modify the installation. All components were
> already checked except the Search, so checked the search. Did not uncheck
> other components or this would uninstall the other components.
> After doing this and rebooting, catalog creation appeared to work. Added
> item to database and number of items in catalog increased. Reapplied
> Service
> pack 4 to update the SQL search files. Rebooted. Our SQL app search
> function
> is now working.
> Hope this can help someone else out there.
> d Krage
> "John Kane" wrote:
Sunday, February 19, 2012
Full-Text Results to MS Access
Greetings!
I have a requirement to set up several full-text indexes on our SQL Server
2000 database. My problem/question is how to get the results to show up in a
form or datasheet in our MS Access XP front end? I'm planning to be able to
allow the user to enter search criteria via an Access form, and get the
results back by populating a subform with the returned data. But since I'm
fairly new to using SQL Server, I'm not sure how to set up the recordset in a
form (or report) in Access. Also, I was thinking that the "best" way to deal
with this is to pass the user's search criteria to a stored procedure in SQL
Server. Is this the best way to do it?
Thanks very much for your help!
PaulJS
PaulJS,
The problem should be no different than getting any normal (non-FTS) SQL
Server backend database ad hoc or stored procedure query results returned to
your MS Access XP front end. For example, using the pubs database and the
authors table, you can execute:
select * from authors where au_lname = 'white'
Once, you've FT-enable the authors table in the pubs database, and run a
Full Population via the FT Indexing Wizard, you can issue the following
query:
select * from authors where contains(au_lname, 'white')
As for the best way to deal with the users search criteria (and to avoid the
infamous "ignore word" error), you may want to parse out the noise words
first, and/or enclose the search criteria within the appropriate quotes. For
more details on this, see KB article 246800 (Q246800) "INF: Correctly
Parsing Quotation Marks in FTS Queries" at
http://support.microsoft.com//defaul...;EN-US;246800. You can
find more SQL Server 2000 Full-Text Search Resources and Links at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"PaulJS" <PaulJS@.discussions.microsoft.com> wrote in message
news:C42FDBE2-6120-44FB-8553-CE36A977315C@.microsoft.com...
> Greetings!
> I have a requirement to set up several full-text indexes on our SQL Server
> 2000 database. My problem/question is how to get the results to show up
in a
> form or datasheet in our MS Access XP front end? I'm planning to be able
to
> allow the user to enter search criteria via an Access form, and get the
> results back by populating a subform with the returned data. But since
I'm
> fairly new to using SQL Server, I'm not sure how to set up the recordset
in a
> form (or report) in Access. Also, I was thinking that the "best" way to
deal
> with this is to pass the user's search criteria to a stored procedure in
SQL
> Server. Is this the best way to do it?
> Thanks very much for your help!
> --
> PaulJS
|||John:
Thanks for the info! After reading your response, I think my problem is
more of an elemental one: Being new to SQL Server and ADO, how can I bind the
results of my full-text query to, say, an Access form or datasheet (datagrid)
with the results? (My database was started in pre-ADO days so the Access
front end uses DAO on recordset operations.) Although I'm using ADO more and
more, I use it mostly to perform some operation on data and return a result
like a true/false flag, a value, etc., but if the result is a recordset, I
don't know how to bind (display) those records to the form. Access makes it
easy to use an Access query to be bound to the form as the form's record
source. My problem (or weakness), is not knowing how to get recordset data
results back from a stored procedure and display those results in a form. I
want to be able to let users enter search criteria, then get a list of
results, and let the user select one of those records to do some other
operation. For instance, a user wants to search a table named "tblProject"
based on some full-text search criteria. The criteria is sent to a stored
procedure, and then the Project records matching the criteria are displayed
in an Access datagrid or form; the user selects one of the records, then does
some operation based on the selected Project record.
Did that make sense? If not, let me know.
Thanks again!
"John Kane" wrote:
> PaulJS,
> The problem should be no different than getting any normal (non-FTS) SQL
> Server backend database ad hoc or stored procedure query results returned to
> your MS Access XP front end. For example, using the pubs database and the
> authors table, you can execute:
> select * from authors where au_lname = 'white'
> Once, you've FT-enable the authors table in the pubs database, and run a
> Full Population via the FT Indexing Wizard, you can issue the following
> query:
> select * from authors where contains(au_lname, 'white')
> As for the best way to deal with the users search criteria (and to avoid the
> infamous "ignore word" error), you may want to parse out the noise words
> first, and/or enclose the search criteria within the appropriate quotes. For
> more details on this, see KB article 246800 (Q246800) "INF: Correctly
> Parsing Quotation Marks in FTS Queries" at
> http://support.microsoft.com//defaul...;EN-US;246800. You can
> find more SQL Server 2000 Full-Text Search Resources and Links at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "PaulJS" <PaulJS@.discussions.microsoft.com> wrote in message
> news:C42FDBE2-6120-44FB-8553-CE36A977315C@.microsoft.com...
> in a
> to
> I'm
> in a
> deal
> SQL
>
>
I have a requirement to set up several full-text indexes on our SQL Server
2000 database. My problem/question is how to get the results to show up in a
form or datasheet in our MS Access XP front end? I'm planning to be able to
allow the user to enter search criteria via an Access form, and get the
results back by populating a subform with the returned data. But since I'm
fairly new to using SQL Server, I'm not sure how to set up the recordset in a
form (or report) in Access. Also, I was thinking that the "best" way to deal
with this is to pass the user's search criteria to a stored procedure in SQL
Server. Is this the best way to do it?
Thanks very much for your help!
PaulJS
PaulJS,
The problem should be no different than getting any normal (non-FTS) SQL
Server backend database ad hoc or stored procedure query results returned to
your MS Access XP front end. For example, using the pubs database and the
authors table, you can execute:
select * from authors where au_lname = 'white'
Once, you've FT-enable the authors table in the pubs database, and run a
Full Population via the FT Indexing Wizard, you can issue the following
query:
select * from authors where contains(au_lname, 'white')
As for the best way to deal with the users search criteria (and to avoid the
infamous "ignore word" error), you may want to parse out the noise words
first, and/or enclose the search criteria within the appropriate quotes. For
more details on this, see KB article 246800 (Q246800) "INF: Correctly
Parsing Quotation Marks in FTS Queries" at
http://support.microsoft.com//defaul...;EN-US;246800. You can
find more SQL Server 2000 Full-Text Search Resources and Links at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"PaulJS" <PaulJS@.discussions.microsoft.com> wrote in message
news:C42FDBE2-6120-44FB-8553-CE36A977315C@.microsoft.com...
> Greetings!
> I have a requirement to set up several full-text indexes on our SQL Server
> 2000 database. My problem/question is how to get the results to show up
in a
> form or datasheet in our MS Access XP front end? I'm planning to be able
to
> allow the user to enter search criteria via an Access form, and get the
> results back by populating a subform with the returned data. But since
I'm
> fairly new to using SQL Server, I'm not sure how to set up the recordset
in a
> form (or report) in Access. Also, I was thinking that the "best" way to
deal
> with this is to pass the user's search criteria to a stored procedure in
SQL
> Server. Is this the best way to do it?
> Thanks very much for your help!
> --
> PaulJS
|||John:
Thanks for the info! After reading your response, I think my problem is
more of an elemental one: Being new to SQL Server and ADO, how can I bind the
results of my full-text query to, say, an Access form or datasheet (datagrid)
with the results? (My database was started in pre-ADO days so the Access
front end uses DAO on recordset operations.) Although I'm using ADO more and
more, I use it mostly to perform some operation on data and return a result
like a true/false flag, a value, etc., but if the result is a recordset, I
don't know how to bind (display) those records to the form. Access makes it
easy to use an Access query to be bound to the form as the form's record
source. My problem (or weakness), is not knowing how to get recordset data
results back from a stored procedure and display those results in a form. I
want to be able to let users enter search criteria, then get a list of
results, and let the user select one of those records to do some other
operation. For instance, a user wants to search a table named "tblProject"
based on some full-text search criteria. The criteria is sent to a stored
procedure, and then the Project records matching the criteria are displayed
in an Access datagrid or form; the user selects one of the records, then does
some operation based on the selected Project record.
Did that make sense? If not, let me know.
Thanks again!
"John Kane" wrote:
> PaulJS,
> The problem should be no different than getting any normal (non-FTS) SQL
> Server backend database ad hoc or stored procedure query results returned to
> your MS Access XP front end. For example, using the pubs database and the
> authors table, you can execute:
> select * from authors where au_lname = 'white'
> Once, you've FT-enable the authors table in the pubs database, and run a
> Full Population via the FT Indexing Wizard, you can issue the following
> query:
> select * from authors where contains(au_lname, 'white')
> As for the best way to deal with the users search criteria (and to avoid the
> infamous "ignore word" error), you may want to parse out the noise words
> first, and/or enclose the search criteria within the appropriate quotes. For
> more details on this, see KB article 246800 (Q246800) "INF: Correctly
> Parsing Quotation Marks in FTS Queries" at
> http://support.microsoft.com//defaul...;EN-US;246800. You can
> find more SQL Server 2000 Full-Text Search Resources and Links at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "PaulJS" <PaulJS@.discussions.microsoft.com> wrote in message
> news:C42FDBE2-6120-44FB-8553-CE36A977315C@.microsoft.com...
> in a
> to
> I'm
> in a
> deal
> SQL
>
>
Subscribe to:
Posts (Atom)