Convert Access to SQL 2000 Desktop

I have used the Upsizing Wizard in Access 2003 to convert a database to SQL. When I open it using SQL 2000 Desktop edition, I am finding a problem with the views and stored procedures.
When I open a query in design view, the tables are displayed in the upper pane without the details of the individual fields. All I get is a tick box next to *(All Columns)
If I create a new table, the same thing happens. If I create a new database, then make a table within it, any queries behave normally, with all the fields displayed.
I would be grateful for any ideasIf you are using the Access query designer, then checking the boxes next to the field names in the diagram pane will make the fields appear in the query grid.|||My problem is that there are no field names in the diagram pane - just a single line at the top of the table box which says *(All Columns). There is a single check box to the left of it, so I can have all columns or no columns. I can't pick the ones I want.
This "All Columns" would normally be at the top of the list of field names.
(How do I insert an image into this reply?)|||Post the SQL from the SQL pane for the view in question.

Contradiction in SQL Server 2000 Licensing FAQ

Hello,
I have a 4 CPU server running SQL Server 2000 Standard Edition. I have
4 SQL Server 2000 Standard CPU Licenses for this server.
My server has been configured as case-sensitive. We have bought a
piece of third party software that requires the SQL server to be
case-insensitive. However, we do not want to risk changing our
existing installation as it would probably require a great deal of
regression testing.
One options appears to be to add a second instance of SQL server to
the same server, but install this instance as case-insensitive.
I am trying to find out whether our current licensing supports this.
From http://www.microsoft.com/sql/howtobuy/faq.asp
"A Processor license gives you the right to install any number of
copies of SQL Server 2000 on a single computer"
but further down:
"With SQL Server 2000 Enterprise Edition, you can install multiple
instances of SQL Server on the same computer without having additional
licenses. Although SQL Server 2000 Standard Edition will technically
support multiple instances, each instance requires a separate
license."
However http://www.microsoft.com/sql/howtobuy/multipleinstances.asp
states:
"Under the Per Processor licensing model, you may install and run any
number of copies (or "instances") of the SQL Server software on a
server, provided that the required number of Processor licenses has
been acquired."
Does anyone know which is currect?
Thanks,
RichardStandard edition will support upto 4 cpus and multiple
instances, but you need separate license for another
instance of sql server
You don't need to buy separte instance licenses if you
have bought Enterprise Editon per processor license.
>--Original Message--
>Hello,
>I have a 4 CPU server running SQL Server 2000 Standard
Edition. I have
>4 SQL Server 2000 Standard CPU Licenses for this server.
>My server has been configured as case-sensitive. We have
bought a
>piece of third party software that requires the SQL
server to be
>case-insensitive. However, we do not want to risk
changing our
>existing installation as it would probably require a
great deal of
>regression testing.
>One options appears to be to add a second instance of SQL
server to
>the same server, but install this instance as case-
insensitive.
>I am trying to find out whether our current licensing
supports this.
>From http://www.microsoft.com/sql/howtobuy/faq.asp
>"A Processor license gives you the right to install any
number of
>copies of SQL Server 2000 on a single computer"
>but further down:
>"With SQL Server 2000 Enterprise Edition, you can install
multiple
>instances of SQL Server on the same computer without
having additional
>licenses. Although SQL Server 2000 Standard Edition will
technically
>support multiple instances, each instance requires a
separate
>license."
>However
http://www.microsoft.com/sql/howtobuy/multipleinstances.asp
>states:
>"Under the Per Processor licensing model, you may install
and run any
>number of copies (or "instances") of the SQL Server
software on a
>server, provided that the required number of Processor
licenses has
>been acquired."
>Does anyone know which is currect?
>Thanks,
>Richard
>.
>|||> Standard edition will support upto 4 cpus and multiple
> instances, but you need separate license for another
> instance of sql server
This was changed about half a year ago, as far as I understand.
Richard, I'm fairly certain that you can have several on the same license. But you should really
check with MS.
Speak to MS about licensing:
You can call 1-800-426-9400 (select option 4), Monday through Friday, 6:00
A.M. to 6:00 P.M. (PST) to speak directly to a Microsoft licensing
specialist for licensing problem. Worldwide customers can use the Guide to
Worldwide Microsoft Licensing Sites
http://www.microsoft.com/licensing/index/worldwide.asp to find contact
information in their locations.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:02e601c39cc2$49861ba0$a301280a@.phx.gbl...
> Standard edition will support upto 4 cpus and multiple
> instances, but you need separate license for another
> instance of sql server
> You don't need to buy separte instance licenses if you
> have bought Enterprise Editon per processor license.
>
> >--Original Message--
> >Hello,
> >
> >I have a 4 CPU server running SQL Server 2000 Standard
> Edition. I have
> >4 SQL Server 2000 Standard CPU Licenses for this server.
> >
> >My server has been configured as case-sensitive. We have
> bought a
> >piece of third party software that requires the SQL
> server to be
> >case-insensitive. However, we do not want to risk
> changing our
> >existing installation as it would probably require a
> great deal of
> >regression testing.
> >
> >One options appears to be to add a second instance of SQL
> server to
> >the same server, but install this instance as case-
> insensitive.
> >
> >I am trying to find out whether our current licensing
> supports this.
> >
> >From http://www.microsoft.com/sql/howtobuy/faq.asp
> >
> >"A Processor license gives you the right to install any
> number of
> >copies of SQL Server 2000 on a single computer"
> >
> >but further down:
> >
> >"With SQL Server 2000 Enterprise Edition, you can install
> multiple
> >instances of SQL Server on the same computer without
> having additional
> >licenses. Although SQL Server 2000 Standard Edition will
> technically
> >support multiple instances, each instance requires a
> separate
> >license."
> >
> >However
> http://www.microsoft.com/sql/howtobuy/multipleinstances.asp
> >states:
> >
> >"Under the Per Processor licensing model, you may install
> and run any
> >number of copies (or "instances") of the SQL Server
> software on a
> >server, provided that the required number of Processor
> licenses has
> >been acquired."
> >
> >Does anyone know which is currect?
> >
> >Thanks,
> >
> >Richard
> >.
> >

Continued DTS support with what SQL Server Version/Edition

We currently have access to another department's SQL server. However they are taking that server offline within a few months, and we will need to migrate our data and DTS packages to a new solution before then. We have about 30 databases and 20 somewhat simple DTS packages, but only a few users need access. Funding is very tight.

SQL 2005's support of DTS is somewhat of a mystery to me. Docs state with 2000 Enterprise client tools installed + Express edition, that I can run DTS packages on Express edition. But the docs do not state the procedure for doing so, and I cannot register the Express server in the Enterprise manager(states must be using 2005 version of Enterprise manager). It seems I can Design a DTS package that resides on the 2000 server and specify a connection to the Express server, but the DTS package would not run, stating always that the table was in use, and I haven't explored it any further. May have just been a quirk.

I'm trying to conquer this in two pieces:

Cheapest solution for graphical design of DTS packages, or some techniology similar(I think I understand that this is called SSIS in 2005?). This would be needed by 1-4 users.

SQL 2005 Developer Edition for each user, only about $50 per user.

MSDE for each user, but can this house DTS packages and be connected to by enterprise manager?

Cheapest solution for data access and ability to run DTS packages by 2-10 users.

If SQL Express Edition is used to house data, can the DTS packages be distributed in some way that allows the users to run them? I.E. exported as VB code or some sort of file that can be loaded by an Access 2002 or VB.NET frontend.

Can all users have Developer Edition or Desktop edition and run the DTS package from it, but have the DTS package connect to the Express Edition server where the data is housed? Or would this be a violation of what the Developer Edition is intended to be used for?

Any other suggested scenarios are welcome.

Are we allowed to continue using the 2000 Enterprise manager after we no longer have access to a licensed SQL 2000 Enterprise server?

The feature comparison for the different 2005 editions doesn't mention DTS, but I know it works for some editions if the add on components are installed. I'm just not sure which versions support this.

1. For development, SQL 2005 dev edition includes sql server 2005 database engine. No msde is required. The server will persist DTS 2000 packages. You cannot connect via 2000 tools, but you can via included 2005 tools. For editing DTS 2000 packages, you need the DTS Designer download, located on msdn as part of the sql 2005 feature pack.

2. The DTS 2000 object model is free. It can be redistributed from the 2000 install disk, or is also available as part of the backward compatibility redist in the feature pack. Once installed on a machine, DTS packages may be run from the command line or from the agent, loading packages resident on 2000 or 2005 servers, or on the file system.

You don't have a need for 2000 enterprise manager here, as your 2005 dev edition will suffice for development work. Howvever, if you want to save your production packages on sql server you will need to attend to licensing for that server. I think msde or express will work, but am not sure. As an alternative, you can save your packages on the file system. There they can be edited by 2000 or 2005 tools.

Context Switches Greater 10,000

I have a server that has SQL Server 2000 Enterprise
Edition with SP3A and Windows 2000 Advanced Server as the
operating system.
This is a new system and I checked my context switches
they average 9600 Context Switches /sec over the was 3
weeks. During the busy part of the day they average
14,000 Context Switches / sec.
Should I enable the fiber-based schedule in SQL Server by
setting the lightweight pooling configuration option to 1?
Please help me with this task.
Thanks,
MikeThat's most likely not the answer. You need to determine why you get rates
such as those. It's quite possible you have other issues that youcan address
to cut this down. See if these help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
--
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:cd0301c43943$9824b0f0$a101280a@.phx.gbl...
> I have a server that has SQL Server 2000 Enterprise
> Edition with SP3A and Windows 2000 Advanced Server as the
> operating system.
> This is a new system and I checked my context switches
> they average 9600 Context Switches /sec over the was 3
> weeks. During the busy part of the day they average
> 14,000 Context Switches / sec.
> Should I enable the fiber-based schedule in SQL Server by
> setting the lightweight pooling configuration option to 1?
> Please help me with this task.
> Thanks,
> Mike
>

Contains 2 Column and / or FTS

Thanks for any help!
SQL 2000 Standard Edition with SP3. The title and document column are both
Full text indexed. The problem I'm trying to solve is how to get the below
stored procedure to search BOTH columns for the criteria entered.
Search Example:
"pin*" or "Yello*" Returns 2 rows as desired.
"pin*" and "yello*" Returns no results which is not desired.
However "Pin*" and "Gree*" does return results which is also desired.
I'm trying to build a procedure which will pull matched words from BOTH
columns for both the "AND" and "OR" operators. Maybe it's the way the FTS
work in Microsoft, but currently when entering the "pin*" and "yello*"
search, it looks for both those terms in the SAME colum, and will not join
them so to speak.
A FreeText search would always return me the desired results, except it
does not seem to do partial term searches or wildcards, unless I'm missing
something.
I've found and seen quite a few bits of code on the internet such as this
http://www.experts-exchange.com/Data...20705253.html, but they don't seem to quite fit my needs.
Thanks a million!!
CREATE TABLE [dbo].[Document] (
[DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (100) ,
[Submitter] [varchar] (100) NOT NULL ,
[RevisedBy] [varchar] (100) NOT NULL ,
[Document] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into document(Title, submitter, Revisedby, document) values ('Pink
Green',
'John Henry', 'Steven Smith', 'Orange')
insert into document(Title, submitter, Revisedby, document) values ('Silver
Blue',
'Kelly Owen', 'John Henry', 'Yellow')
GO
ALTER PROCEDURE SearchDoc2
(
@.Word varchar (300) = Null,
@.SubmitterID varchar (100) = NULL,
@.systemID varchar (100) = NULL
)
as
SELECT Title,
SubmitterID,
RevisedBy
FROM tblDocument
where (Contains(*, @.word) or @.Word IS NULL)
and
(systemID = @.SystemID or @.SystemID IS Null) and
((SubmitterID = @.SubmitterID or @.SubmitterID IS NULL) or
(RevisedBy = @.SubmitterID or @.SubmitterID IS NULL) )
Jeff,
Have you considered using CONTAINSTABLE vs. CONTAINS? If not, please review:
SQL Server FTS across multiple tables or columns
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
Below are modified code examples from the above blog entry to demonstrate
how to handle both the AND or OR operators. Note, you and use dynamic SQL
code in the WHERE clause to handle both conditions in one stored proc. Also,
keep in mind that with the use of two CONTAINSTABLE clauses, this may not
perform well against large (>1 million rows) FT-enabled tables as each
CONTAINSTABLE clause is a full "round-trip" to the FT Catalog.
use Northwind
SELECT e.LastName
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and -- Note, AND operator.
B.[KEY] = e.EmployeeID
-- vs.
use Northwind
SELECT distinct e.LastName
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID or -- Note, OR operator.
B.[KEY] = e.EmployeeID
For the OR operator, you will need to add the DISTINCT operator to prevent
dups in your results.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:F5A5A066-BE17-43CC-83E3-E65E6270AC99@.microsoft.com...
> Thanks for any help!
> SQL 2000 Standard Edition with SP3. The title and document column are
> both
> Full text indexed. The problem I'm trying to solve is how to get the
> below
> stored procedure to search BOTH columns for the criteria entered.
> Search Example:
> "pin*" or "Yello*" Returns 2 rows as desired.
> "pin*" and "yello*" Returns no results which is not desired.
> However "Pin*" and "Gree*" does return results which is also desired.
> I'm trying to build a procedure which will pull matched words from BOTH
> columns for both the "AND" and "OR" operators. Maybe it's the way the FTS
> work in Microsoft, but currently when entering the "pin*" and "yello*"
> search, it looks for both those terms in the SAME colum, and will not join
> them so to speak.
> A FreeText search would always return me the desired results, except it
> does not seem to do partial term searches or wildcards, unless I'm missing
> something.
> I've found and seen quite a few bits of code on the internet such as this
> http://www.experts-exchange.com/Data...20705253.html,
> but they don't seem to quite fit my needs.
> Thanks a million!!
> CREATE TABLE [dbo].[Document] (
> [DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
> [Title] [varchar] (100) ,
> [Submitter] [varchar] (100) NOT NULL ,
> [RevisedBy] [varchar] (100) NOT NULL ,
> [Document] [text] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> insert into document(Title, submitter, Revisedby, document) values ('Pink
> Green',
> 'John Henry', 'Steven Smith', 'Orange')
> insert into document(Title, submitter, Revisedby, document) values
> ('Silver
> Blue',
> 'Kelly Owen', 'John Henry', 'Yellow')
> GO
>
> ALTER PROCEDURE SearchDoc2
> (
> @.Word varchar (300) = Null,
> @.SubmitterID varchar (100) = NULL,
> @.systemID varchar (100) = NULL
> )
> as
>
> SELECT Title,
> SubmitterID,
> RevisedBy
>
> FROM tblDocument
> where (Contains(*, @.word) or @.Word IS NULL)
> and
> (systemID = @.SystemID or @.SystemID IS Null) and
> ((SubmitterID = @.SubmitterID or @.SubmitterID IS NULL) or
> (RevisedBy = @.SubmitterID or @.SubmitterID IS NULL) )
>
>
|||Thanks for the reference John.
I read it over well and tried the examples which I've somewhat tried before.
Aparently when using the [and] operator with contains[table], it looks for
both "words" to exist in the same column, and not scross columns.
I think perhaps the FreeTextable will suit our needs, it's just a shame that
it does not support wildcards yet.
Thanks,
Jeff
"John Kane" wrote:

> Jeff,
> Have you considered using CONTAINSTABLE vs. CONTAINS? If not, please review:
> SQL Server FTS across multiple tables or columns
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
> Below are modified code examples from the above blog entry to demonstrate
> how to handle both the AND or OR operators. Note, you and use dynamic SQL
> code in the WHERE clause to handle both conditions in one stored proc. Also,
> keep in mind that with the use of two CONTAINSTABLE clauses, this may not
> perform well against large (>1 million rows) FT-enabled tables as each
> CONTAINSTABLE clause is a full "round-trip" to the FT Catalog.
> use Northwind
> SELECT e.LastName
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID and -- Note, AND operator.
> B.[KEY] = e.EmployeeID
> -- vs.
> use Northwind
> SELECT distinct e.LastName
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID or -- Note, OR operator.
> B.[KEY] = e.EmployeeID
> For the OR operator, you will need to add the DISTINCT operator to prevent
> dups in your results.
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:F5A5A066-BE17-43CC-83E3-E65E6270AC99@.microsoft.com...
>
>
|||Jeff wrote on Mon, 19 Sep 2005 11:19:06 -0700:

> Thanks for the reference John.
> I read it over well and tried the examples which I've somewhat tried
> before.
> Aparently when using the [and] operator with contains[table], it looks for
> both "words" to exist in the same column, and not scross columns.
> I think perhaps the FreeTextable will suit our needs, it's just a shame
> that it does not support wildcards yet.
You can achieve your result using Contains, but it requires more processing:
select * from mytable where contains(*,'"pin*"') and contains(*,'"yellow*"')
This will find all rows with pin* in the FTS index, and all rows with
yello*, and then combine them together to find all rows that have both terms
in either column.
However, from a performance point of view this may not be satisfactory as
there is an FTS search for each word rather than all at once. In my own
system I maintain an extra column that is a contenation of the 6 columns
that I use in my searches - so if I want a search just on book titles I
would use Contains(title,@.words) but if I wanted the words to appear in any
columns then I'd use Contains(keywords,@.words). It does mean that when data
is changed the keywords field needs to be rebuilt, but all data is processed
from files via an in-house application so this is done automatically.
Additional storage is required for the extra column, but I'd rather use up a
bit more of my disk space than have slow FTS results (disk space is after
all relatively cheap compared to the perceived performance of a web site
search by customers).
Dan