Need help choosing front end for SQL Server

I've been working on an Access 97 database that's pretty much reached
it's limit in terms of performance and reliability. Although it supports
relatively few users (5-10 concurrent) it contains a lot of data (around
30 tables, some with several million records) plus a lot of forms and
reports.
The company has recently purchased an MS SQL server and it's my job to
migrate the back-end as well as re-write the front-end (the front-end
needs re-writing anyway).
My question is do we keep Access as the front end (we'd probably upgrade
to 2003) or would we get better performance using a different solution
(VB.Net for example) ?
If Access, would it be best use MDB's or ADP's?
The main consideration is that we need to perform some complicated
queries on large datasets, which ideally should happen on the SQL server
(I'm currently learning about stored procedures and views etc).
Thanks,
PSB
I see no reason why you can't continue to use access as the front end.
If you have the budgets, and time to re-write the application, then I guess
you
can choose anything you like.
However, if you wish to keep the application, and not re-write, and not have
to re-train the users, then why not continue to use access as the front end?

> My question is do we keep Access as the front end (we'd probably upgrade
> to 2003)
This is going to be up to you. I see no reason why not to continue to use
ms-access. I mean, you got to write the application in something.

> or would we get better performance using a different solution
For the number of users you have, I see no reason not to use ms-access.

> (VB.Net for example) ?
It is a great platform. Do you have the developers...or perhaps you will
spend the time and learn .net? (you will need a few months of time). I mean,
how long did it take the people to learn ms-access? (it took me more then a
year to be become productive in ms-access). And, the sample applies to any
platform you will adopt.

> If Access, would it be best use MDB's or ADP's?
It is by far and away best to keep it as a MDB (and, of course, you used a
split database..and always distributed a mde to each user anyway..right?).
I would only choose a ADP for a brand new from scratch product (and even
then...likely I would not use a adp). So, for a existing application, it
makes NO
sense to use a ADP..as you can't use any of your DAO code.

> The main consideration is that we need to perform some complicated
> queries on large datasets, which ideally should happen on the SQL server
> (I'm currently learning about stored procedures and views etc).
Yes, for processing intensive things, you certainly want that to run on the
server, but you still need to choose a programming environment that lets you
write code, and create the application (and sql server does not do that!!).
If you got the time,a nd resources, and have the developer(s) to re-write,
then you can do such. It really comes down to budgets, and what benefits
you get by re-writing the application.
The skill level of the developer will make or break the project regardless
of the "conversion" issues.
I am a better hockey coach when Wayne Gretzky is on the team.
Hence the #1 consideration is at what level the developer is at. There are
certainly more levels then just "trained" or "not trained". Generally there
are a "lot" of skill levels, but the following breakdown is sufficient. **
Stage 1 Innocent (never heard of the product)
Stage 2 Aware (Has read an article about X)
Stage 3 Apprentice (has attended a three-day seminar)
Stage 4 Practitioner (ready to use X on a real project)
Stage 5 Journeyman (uses X naturally and automatically in his job)
Stage 6 Master (has internalized X, knows when to break the rules)
Stage 7 Expert (writes books, gives lectures, looks for ways to extend x)
One should NEVER attempt a project with a team consisting with Stage 3 or
lower people. This is a sure fire formula for failure. The team can consist
of stage 4's, but they should have at least access to Stage 5, or 6.
So, if your developers are skilled in the new platform, then that is
certainly a possible here to re-write.
However, I see no reason why you can't continue to use ms-access as the
front end to sql server. I mean, if your developers don't know how to use
sql server, then you can get a garbage and slow application if it is written
in VB, vb.net, or ms-access. The performance here is not going to be the
fact that you used VB or ms-access, but in fact that your developers know
how to utilize sql server.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.members.shaw.ca/AlbertKallal
|||For that number of users, Access should be able to handle if designed
properly (Seperated FE and BE and BE file size is not too big, say close 2G
limit). However, now you have already bought SQL Server, and as you stated
"the front-end needs re-writing anyway", so, there is doubt of moving to SQL
Server.
As for front end, to use Access, or not to use it, lisenceing Access itself
would not be a big problem here. That is, if you use Access FE, all users
must have it installed. for $300 of each, the cost does not weigh too much
in the entire situation. And for your situation (fairly small number of
user, and I'd imagine they are in the same office, so deploy/update your app
wouldn't be a serious issue), exactly what type of FE is not that important.
I do not know your skill level, but programming a non-Access front end (Win
App ot wep app) is a lot more different from Access programming and requires
quite different skill and knowledge. Whether such en expertise is available
or not would make quite big difference on development cost.
If you go with Access, probably ADP is the option (But you must use
Access2K or later, not Access97). And many experienced Access developers
claim they can produce Access solution a lot faster than other type of FE.
"PsychicStickleBrick" <sp@.m.me> wrote in message
news:421a3178$0$75435$ed2619ec@.ptn-nntp-reader01.plus.net...
> I've been working on an Access 97 database that's pretty much reached
> it's limit in terms of performance and reliability. Although it supports
> relatively few users (5-10 concurrent) it contains a lot of data (around
> 30 tables, some with several million records) plus a lot of forms and
> reports.
> The company has recently purchased an MS SQL server and it's my job to
> migrate the back-end as well as re-write the front-end (the front-end
> needs re-writing anyway).
> My question is do we keep Access as the front end (we'd probably upgrade
> to 2003) or would we get better performance using a different solution
> (VB.Net for example) ?
> If Access, would it be best use MDB's or ADP's?
> The main consideration is that we need to perform some complicated
> queries on large datasets, which ideally should happen on the SQL server
> (I'm currently learning about stored procedures and views etc).
> Thanks,
> PSB
>
>
|||Now there's the hundred thousand dollar question. The answer is it depends.
Moving the back-end to Sql Server is a good idea. And you can do this with
very little re-writing of the front end.
As for the front-end, it depends on how much of it needs re-written. If it's
just a little, then I'd probably stay with Access. You would get a little
performance hit, but not much if the queries ran server side.
If it's a lot of re-write, then I would go with VB.NET (or ASP.NET if you
want to make it a web app). You get a lot more functionality with these.
One word of note. Running a VB.NET app requires the Microsoft Framework also
be installed. This is a bit resource heavy and will run slowly on older
machines.
Good Luck.
Tom Collins
"PsychicStickleBrick" <sp@.m.me> wrote in message
news:421a3178$0$75435$ed2619ec@.ptn-nntp-reader01.plus.net...
> I've been working on an Access 97 database that's pretty much reached
> it's limit in terms of performance and reliability. Although it supports
> relatively few users (5-10 concurrent) it contains a lot of data (around
> 30 tables, some with several million records) plus a lot of forms and
> reports.
> The company has recently purchased an MS SQL server and it's my job to
> migrate the back-end as well as re-write the front-end (the front-end
> needs re-writing anyway).
> My question is do we keep Access as the front end (we'd probably upgrade
> to 2003) or would we get better performance using a different solution
> (VB.Net for example) ?
> If Access, would it be best use MDB's or ADP's?
> The main consideration is that we need to perform some complicated
> queries on large datasets, which ideally should happen on the SQL server
> (I'm currently learning about stored procedures and views etc).
> Thanks,
> PSB
>
>
|||"PsychicStickleBrick" <sp@.m.me> wrote in message
news:421a3178$0$75435$ed2619ec@.ptn-nntp-reader01.plus.net
> I've been working on an Access 97 database that's pretty much reached
> it's limit in terms of performance and reliability. Although it
> supports relatively few users (5-10 concurrent) it contains a lot of
> data (around 30 tables, some with several million records) plus a lot
> of forms and reports.
> The company has recently purchased an MS SQL server and it's my job to
> migrate the back-end as well as re-write the front-end (the front-end
> needs re-writing anyway).
> My question is do we keep Access as the front end (we'd probably
> upgrade to 2003) or would we get better performance using a different
> solution (VB.Net for example) ?
> If Access, would it be best use MDB's or ADP's?
> The main consideration is that we need to perform some complicated
> queries on large datasets, which ideally should happen on the SQL
> server (I'm currently learning about stored procedures and views etc).
> Thanks,
> PSB
My immediate reaction would be to stay with Access as your front-end,
and use an MDB for it. Some aspects of the application may need to be
rewritten, but in my experience that will probably be much quicker than
rewriting the whole thing in another devleopment environment. Moving
complicated queries to the server, though, is likely to take a fair
amount of work, and you have to budget for that. If the queries don't
use VBA functions or the First() or Last() functions, I don't think
you'll have too many problems, but if they do you'll have to figure out
how to accomplish the same function using SQL Server features and T-SQL.
Incidentally, one thing to watch out for -- and I know because it's
bitten me a few times -- is that your queries and application logic may
currently make assumptions about the order in which records are returned
in the absence of an ORDER BY clause. For example, in Jet SQL the GROUP
BY clause implicitly orders the records, but in T-SQL it doesn't. I
mention this only as something to keep in mind as you transfer queries
to SQL Server.
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|||Dirk Goldgar wrote:
> in Jet SQL the GROUP
> BY clause implicitly orders the records, but in T-SQL it
> doesn't.
Jet implicitly orders using what criteria?
Jamie.
|||onedaywhen wrote:
> Dirk Goldgar wrote:
> Jet implicitly orders using what criteria?
> Jamie.
Alphabetical/Numercal IOW "GroupBy SomeField" implicitly adds an "ORDER BY
SomeField" in Access/Jet.
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
|||"onedaywhen" <jamiecollins@.xsmail.com> wrote in message
news:1109076635.869509.10730@.g14g2000cwa.googlegro ups.com...
> Dirk Goldgar wrote:
> Jet implicitly orders using what criteria?
As I recall Jet orders the result set by the table's PK.
Steve
|||When you use GROUP BY in the absence of an ORDER BY clause Jet will sort by
the columns used in the GROUP BY clause, from left to right. For example,
this query ...
SELECT tblTest.TestText, tblTest.TestNumber, tblTest.ID
FROM tblTest
GROUP BY tblTest.TestText, tblTest.TestNumber, tblTest.ID;
... is equivalent to this query ...
SELECT tblTest.TestText, tblTest.TestNumber, tblTest.ID
FROM tblTest
GROUP BY tblTest.TestText, tblTest.TestNumber, tblTest.ID
ORDER BY tblTest.TestText, tblTest.TestNumber;
... however in this query ...
SELECT tblTest.TestText, tblTest.TestNumber, tblTest.ID
FROM tblTest
GROUP BY tblTest.TestText, tblTest.TestNumber, tblTest.ID
ORDER BY tblTest.ID;
... the explicit ORDER BY clause over-rides the implicit ordering of the
GROUP BY clause.
Brendan Reynolds (MVP)
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:Oco9FqOGFHA.1392@.tk2msftngp13.phx.gbl...
> "onedaywhen" <jamiecollins@.xsmail.com> wrote in message
> news:1109076635.869509.10730@.g14g2000cwa.googlegro ups.com...
> As I recall Jet orders the result set by the table's PK.
> Steve
>
|||Brendan Reynolds wrote:
> When you use GROUP BY in the absence of an ORDER BY clause Jet will
sort by
> the columns used in the GROUP BY clause, from left to right.
Thanks everyone. I got round to testing and I concur the following:
- the implicit ORDER BY is by columns left to right, ascending;
- the process occurs in Jet rather than MS Access;
- it is unaffected by clustered index, primary key, date/time entered
order etc.
Thanks again,
Jamie.

Need help 2

Need Help 2
I am currently converting an access DB to MS SQL.
I am currently trying to figure out how to code this
I need the count of all rows in colum TERM that is > 1 when its grouped by the COLUM NAME
Anyone got any Ideas,I do not know the specifics of MS SQL, however a general approach to your problem might be

select column, count(*)
from table
where term > 1
group by student;

Need Help 2

I am currently converting an access DB to MS SQL.
I am currently trying to figure out how to code this
I need the count of all rows in colum TERM that is > 1 when its grouped by the COLUM NAME
Anyone got any Ideas,i'm not sure wheter i get it right.

hope that it was the thing that you ask for.

Select TERM,Count(<table key>) as Cnt from <Table \name>
Wher TERM > 1
Group by TERM.

If I didn't get it right then just forget about it.
:D|||select count(*) from tbl
where term in
(
select term from tbl group by name having count(*) > 1
)

need help : SQL Server and Clear Case

Environment

1)We are using VBA,MS Access 2000/2003 and SQL Server 2000 in our
project.

2)We need to use Rational clear case as the SCM tool.

Our Work

Our work primarily falls in developing SQL Server stored procedures,
functions and few VisualBasicForAccess(VBA) code.

Technical Help sought

1) Does SQL Server 2000 provide any inbuilt capabilities of Software
Configuration Management(SCM)?Not that I am aware of other than
extending it with other DM management tools like BMC,A&G,VERITAS etc.

2)If not, Is there any plug-in available in SQL Server environment that
can be installed on SQL Server so that any code developed [Stored
procedures/functions etc] can be managed in Clearcase.[I am looking for
Eclipse plugin's kind of plugins which would enable you to check in and
check out from Eclipse after the initial configuration is done on
Eclipse]?

3)If plugins are not available then the option we have is, manual and
external check in and check out the code bits in to Clearcase.Can some
one point me to concise and relevant user manual on this.The MSSQL 2000 client tools have no direct SCM integration (the 2005
ones do), although QA allows you to run batch files with parameters
such as the currently open file, so you could develop some basic
scripts yourself. Visual Studio might be an option if you're already
using it, but personally I just check out a file (from VSS) and start
editing it in QA. I have no idea how ClearCase works, so I can't really
comment on that tool specifically.

Simon|||(db2sysc@.gmail.com) writes:
> 2)If not, Is there any plug-in available in SQL Server environment that
> can be installed on SQL Server so that any code developed [Stored
> procedures/functions etc] can be managed in Clearcase.[I am looking for
> Eclipse plugin's kind of plugins which would enable you to check in and
> check out from Eclipse after the initial configuration is done on
> Eclipse]?

I have no experience of ClearCase, but stored procedures etc are no
different from everything else you have under source control. It's
just files. Some people think they are database objects, but that
is the "binary" representation, so to speak.

Sure, some people think it's nice to check out a file from the
version-control system directly in the tool. I for my part, prefer
to access the CM tool directly (in my case SourceSafe), so I know what
is going on.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Need help - Error: 823, Severity: 24, State: 11

I got the following errors......

Error: 823, Severity: 24, State: 11

I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset 0x000002983a0000 in file 'e:\MSSQL\data\tempdb.mdf'..

Error: 15457, Severity: 0, State: 1

Seems like a bad disk. It is a Raid 0 drive.

I have asked a drive check to be performed for this drive. I am not sure if it can be done while the SQL Server is running.

I am thinking of shrinking the tempDB and let it grow again so that it may not expand on the bad sectors.

I could also move the tempDB to another drive temporarily, but would require me to reboot our production server.

Also, my concern is that if the drive is bad, it will affect the other system databases as they are sitting on the same drive. The sytem databases have passed integrity checks over the weekend and seem to be okay.

Anyone has any ideas on what steps I should take or the best way to resolve this problem.Not too sure, but you could check to see that virus scan and any backup software remembers to ignore *.MDF, *.LDF, and *.NDF files. Also you may want to check on tempdb to make sure that auto-close is off (though, I am not sure you can set it to auto-close). Good luck, and let us know if you find the problem.|||Sorry to get back late here, we had some other problems I was working on. Well, our hardware people said that there was nothing wrong with the drive in question. So, it had to be the tempdb problem.

I ran the DBCC CheckDB with All_ErrorMsgs and it returned me no errors. There were no non-system objects in the tempdb and the space used in the 35GB tempdb database was only 4MB. Looking at the time it required to run and the results, it seems to me that the the DBCC command checks only on the space that has been allocated to the tables, etc. So, I ran the DBCC CheckDB with Physical_Only with the hopes that it will scan all the pages even if they are empty, but it came with the same results. No errors.

So, I restarted the server in minimum configuration mode (at the dos prompt with "SQLServr.Exe -c -f" and then using Query Analyzer, I altered the database files (mdf and log) size of the tempdb using the 'alter database' command. And restarted the SQLServer.

If this had not worked, I was going to try to use "DBCC ShrinkFile" and shrink the tempdb database files.

Thanks for your help.

need help

I have installed SQL 2005 reporting services and I configured it using
Reporting Services Configuration. Now I tried to access to report manager
using http://servername/Reports with login I used to start reporting
services. It doesn't work and keep prompting me for login and password. But
no use and I am sure this login is correct and password correct and it
started the reportings services successfully. Can anyone please help how to
solve this problem. Thanks.On Nov 4, 9:56 pm, " Miley Cyrus" <a...@.b.com> wrote:
> I have installed SQL 2005 reporting services and I configured it using
> Reporting Services Configuration. Now I tried to access to report manager
> using http://servername/Reports with login I used to start reporting
> services. It doesn't work and keep prompting me for login and password. But
> no use and I am sure this login is correct and password correct and it
> started the reportings services successfully. Can anyone please help how to
> solve this problem. Thanks.
You might want to check the Virtual Directories and see if the
permissions are set to anonymous. To do this, right-click My Computer
>> select Manage >> select Services and Applications >> Internet
Information Services >> Web Sites >> Default Web Site >> right-click
the Reports/ReportServer virtual directories >> select Properties >
select the Directory Security tab >> select the top Edit... button >>
select Anonymous access, Allow IIS to control password and Integrated
Windows authentication. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
In fact, it is checked already. Integrade window
authentication, digest authentication ... and basic authenitcation are
checked as well. Only .net passport atuthentication and default domain and
realm are empty. Is this this all right?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1194239797.654239.246180@.19g2000hsx.googlegroups.com...
> On Nov 4, 9:56 pm, " Miley Cyrus" <a...@.b.com> wrote:
>> I have installed SQL 2005 reporting services and I configured it using
>> Reporting Services Configuration. Now I tried to access to report manager
>> using http://servername/Reports with login I used to start reporting
>> services. It doesn't work and keep prompting me for login and password.
>> But
>> no use and I am sure this login is correct and password correct and it
>> started the reportings services successfully. Can anyone please help how
>> to
>> solve this problem. Thanks.
>
> You might want to check the Virtual Directories and see if the
> permissions are set to anonymous. To do this, right-click My Computer
>> select Manage >> select Services and Applications >> Internet
> Information Services >> Web Sites >> Default Web Site >> right-click
> the Reports/ReportServer virtual directories >> select Properties >
> select the Directory Security tab >> select the top Edit... button >>
> select Anonymous access, Allow IIS to control password and Integrated
> Windows authentication. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||If you have Window Vista, is need run Internet Explorer with Administrator
privileges.
" Miley Cyrus" <a@.b.com> pí¹e v diskusním pøíspìvku
news:eK30U02HIHA.4808@.TK2MSFTNGP05.phx.gbl...
> Enrique,
> In fact, it is checked already. Integrade window
> authentication, digest authentication ... and basic authenitcation are
> checked as well. Only .net passport atuthentication and default domain and
> realm are empty. Is this this all right?
>
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1194239797.654239.246180@.19g2000hsx.googlegroups.com...
>> On Nov 4, 9:56 pm, " Miley Cyrus" <a...@.b.com> wrote:
>> I have installed SQL 2005 reporting services and I configured it using
>> Reporting Services Configuration. Now I tried to access to report
>> manager
>> using http://servername/Reports with login I used to start reporting
>> services. It doesn't work and keep prompting me for login and password.
>> But
>> no use and I am sure this login is correct and password correct and it
>> started the reportings services successfully. Can anyone please help how
>> to
>> solve this problem. Thanks.
>>
>> You might want to check the Virtual Directories and see if the
>> permissions are set to anonymous. To do this, right-click My Computer
>> select Manage >> select Services and Applications >> Internet
>> Information Services >> Web Sites >> Default Web Site >> right-click
>> the Reports/ReportServer virtual directories >> select Properties >
>> select the Directory Security tab >> select the top Edit... button >>
>> select Anonymous access, Allow IIS to control password and Integrated
>> Windows authentication. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>

need help

How can I access information on how to check history of
sites that have been accessed on my computerSimple tracing, in IE just click the "History" button on the toolbar. Note
that this is user-specific.
Real auditing, use Win 2003 Server, and Group Policies.
For more info, I would suggest you to ask more in some Windows / IE group.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:60d801c4820f$ba699b20$a501280a@.phx.gbl...
> How can I access information on how to check history of
> sites that have been accessed on my computer

need help

I have installed SQL 2005 reporting services and I configured it using
Reporting Services Configuration. Now I tried to access to report manager
using http://servername/Reports with login I used to start reporting
services. It doesn't work and keep prompting me for login and password. But
no use and I am sure this login is correct and password correct and it
started the reportings services successfully. Can anyone please help how to
solve this problem. Thanks.On Nov 4, 9:56 pm, " Miley Cyrus" <a...@.b.com> wrote:
> I have installed SQL 2005 reporting services and I configured it using
> Reporting Services Configuration. Now I tried to access to report manager
> using http://servername/Reports with login I used to start reporting
> services. It doesn't work and keep prompting me for login and password. But
> no use and I am sure this login is correct and password correct and it
> started the reportings services successfully. Can anyone please help how to
> solve this problem. Thanks.
You might want to check the Virtual Directories and see if the
permissions are set to anonymous. To do this, right-click My Computer
>> select Manage >> select Services and Applications >> Internet
Information Services >> Web Sites >> Default Web Site >> right-click
the Reports/ReportServer virtual directories >> select Properties >
select the Directory Security tab >> select the top Edit... button >>
select Anonymous access, Allow IIS to control password and Integrated
Windows authentication. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
In fact, it is checked already. Integrade window
authentication, digest authentication ... and basic authenitcation are
checked as well. Only .net passport atuthentication and default domain and
realm are empty. Is this this all right?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1194239797.654239.246180@.19g2000hsx.googlegroups.com...
> On Nov 4, 9:56 pm, " Miley Cyrus" <a...@.b.com> wrote:
>> I have installed SQL 2005 reporting services and I configured it using
>> Reporting Services Configuration. Now I tried to access to report manager
>> using http://servername/Reports with login I used to start reporting
>> services. It doesn't work and keep prompting me for login and password.
>> But
>> no use and I am sure this login is correct and password correct and it
>> started the reportings services successfully. Can anyone please help how
>> to
>> solve this problem. Thanks.
>
> You might want to check the Virtual Directories and see if the
> permissions are set to anonymous. To do this, right-click My Computer
>> select Manage >> select Services and Applications >> Internet
> Information Services >> Web Sites >> Default Web Site >> right-click
> the Reports/ReportServer virtual directories >> select Properties >
> select the Directory Security tab >> select the top Edit... button >>
> select Anonymous access, Allow IIS to control password and Integrated
> Windows authentication. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||If you have Window Vista, is need run Internet Explorer with Administrator
privileges.
" Miley Cyrus" <a@.b.com> pí¹e v diskusním pøíspìvku
news:eK30U02HIHA.4808@.TK2MSFTNGP05.phx.gbl...
> Enrique,
> In fact, it is checked already. Integrade window
> authentication, digest authentication ... and basic authenitcation are
> checked as well. Only .net passport atuthentication and default domain and
> realm are empty. Is this this all right?
>
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1194239797.654239.246180@.19g2000hsx.googlegroups.com...
>> On Nov 4, 9:56 pm, " Miley Cyrus" <a...@.b.com> wrote:
>> I have installed SQL 2005 reporting services and I configured it using
>> Reporting Services Configuration. Now I tried to access to report
>> manager
>> using http://servername/Reports with login I used to start reporting
>> services. It doesn't work and keep prompting me for login and password.
>> But
>> no use and I am sure this login is correct and password correct and it
>> started the reportings services successfully. Can anyone please help how
>> to
>> solve this problem. Thanks.
>>
>> You might want to check the Virtual Directories and see if the
>> permissions are set to anonymous. To do this, right-click My Computer
>> select Manage >> select Services and Applications >> Internet
>> Information Services >> Web Sites >> Default Web Site >> right-click
>> the Reports/ReportServer virtual directories >> select Properties >
>> select the Directory Security tab >> select the top Edit... button >>
>> select Anonymous access, Allow IIS to control password and Integrated
>> Windows authentication. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>sql

Need function for padding

Hi

I am using Format function in my query when I was using access tables.

Now I have imported my tables in Sql server and linked through odbc. Now in my query it throws message for "Format" function. I tried for Replicate function but still not able to get this problem.

Same case for Date and chr.you need to use convert or cast, not format.|||it sounds like you are doing SQL Passthrough in jet against sql server, and now you need to use SQL Server syntax instead of jet/access sql.'

cheers|||Originally posted by eisoffind
Hi

I am using Format function in my query when I was using access tables.

Now I have imported my tables in Sql server and linked through odbc. Now in my query it throws message for "Format" function. I tried for Replicate function but still not able to get this problem.

Same case for Date and chr.

If i understand your question correctly, you want to be able to pad the left of a number with another value. For example '555' --> '000555'

Attached are two SQL2K UDFs to lpad and rpad.sql

Need for Multi-Threaded Visual Basic.NET to SQL Server Data Access Example Code

Of all the Visual Basic.NET data access books that I have purchased and all the Internet site example code that I have reviewed, none have had any good examples of multi-threaded VB.NET code doing data access.

I am trying to avoid the non-responsiveness in a VB app while a simple data retrieval from SQL Server 2005 is in progress.

If anyone knows of any book titles or web sites that have example code (good or not) of multi-threaded VB.NET applications doing data access against Microsoft SQL Server (7, 2000, or 2005) or even against Microsoft Access(TM), it would be very much appreciated if you could provide the book title or URL to point me in the right direction.

The more examples the better.

Thanks in advance.

Sounds like you need to make your calls Async.

http://msdn2.microsoft.com/en-us/library/ms379553(VS.80).aspx

|||

I've never seen any database programming books that cover this specific topic. You may want to start by looking for .NET books that cover multi-threaded programming in .NET. From my past experience, I remember it is a bit tricky.

|||

Async ADO.NET2.0- Summary of Best Search Results

The best discussions re async commands (transactions) using SQL Server 2005 that I have found are:

Books:

WROX?’s ‘Professional ADO.NET 2’

Web Articles :

http://msdn2.microsoft.com/en-us/vstudio/aa718334.aspx (Asych Query Samples from Data Access Samples) with source (this is the sample I chose)

www.codeproject.com/dotnet/asynchronousdataaccess.asp (with source)

http://msdn2.microsoft.com/en-us/library/ms379553.aspx (with source), but note warning at:

http://blogs.msdn.com/angelsb/archieve/2004/09/02/224964.aspx

http://nayyeri.net/archieve/2006/08/29/Asynchronous-command-execution-in-.NET-2.0.aspx

http://msdn.microsoft.com/library/en-us/cpgenref/html/cpconAsynchronousExecution.asp

http://msdn2.microsoft.com/en-us/library/ms228972(vs.80,d=printer).aspx

http://msdn2.microsoft.com/en-us/library/ms228975(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/wewwczdw(VS.80).aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/cpconAsynchronousProgrammingDesignPattern.asp

|||

Most books talk about how to do basic async execution from a button click this is fine but the trouble begins when you want to populate the UI with the results of an async db command. The .NET UI is NOT thread safe and you have to serialize everything to the UI thread when doing things like updating a datagrid control for example. If you stick with the data driven events you are OK but if you want to do things more complex it can get troublesome.

I remember doing this back in the old days (.NET 1.0 and 1.1 days) and I would end up with a dead datagrid control with a big red X on it when the grid control got out of sync. So what I finally did after much pain and suffering was write my own custom message passing system that would pass messages to background threads and queue responses back to UI thread, then final bit of data manipulation had to be done on the UI thread.

I remember even creating/manipulating a new datarow on a secondary thread would break the datagrid/dataset (note I would just call NewRow on background thread and then serialize the actual adding of the new row on primary UI thread). This stuff might be fixed in .NET 2.0, so maybe I am being overly cautious here.

Need Enterprise Manager for Remote Site

I need to get Enterprise Manager to a user at a remote site and I don't want
to provide shared access to my sql 2000 install cd.
What/where can I download to provide a user with the Enterprise Manager mmc
console snap-in?
Thanks
Paul Bergson
If that is a "licenced" user, you can get him a copy of the cd, but if he is
not licenced he is not allowed to install it. There is no snap in
downloadable for the enterprise manager, it all comes with the cd.
Another options would be: http://www.aspfaq.com/show.asp?id=2442
HTH, Jens Suessmeyer.
"Paul Bergson" wrote:

> I need to get Enterprise Manager to a user at a remote site and I don't want
> to provide shared access to my sql 2000 install cd.
> What/where can I download to provide a user with the Enterprise Manager mmc
> console snap-in?
>
> --
> Thanks
> Paul Bergson
>
>
|||Paul Bergson wrote:
> I need to get Enterprise Manager to a user at a remote site and I
> don't want to provide shared access to my sql 2000 install cd.
> What/where can I download to provide a user with the Enterprise
> Manager mmc console snap-in?
Try using the SQL Server Web Data Administrator instead:
http://www.microsoft.com/downloads/d...displaylang=en
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql

Need Enterprise Manager for Remote Site

I need to get Enterprise Manager to a user at a remote site and I don't want
to provide shared access to my sql 2000 install cd.
What/where can I download to provide a user with the Enterprise Manager mmc
console snap-in?
Thanks
Paul BergsonIf that is a "licenced" user, you can get him a copy of the cd, but if he is
not licenced he is not allowed to install it. There is no snap in
downloadable for the enterprise manager, it all comes with the cd.
Another options would be: http://www.aspfaq.com/show.asp?id=2442
HTH, Jens Suessmeyer.
"Paul Bergson" wrote:

> I need to get Enterprise Manager to a user at a remote site and I don't wa
nt
> to provide shared access to my sql 2000 install cd.
> What/where can I download to provide a user with the Enterprise Manager mm
c
> console snap-in?
>
> --
> Thanks
> Paul Bergson
>
>|||Paul Bergson wrote:
> I need to get Enterprise Manager to a user at a remote site and I
> don't want to provide shared access to my sql 2000 install cd.
> What/where can I download to provide a user with the Enterprise
> Manager mmc console snap-in?
Try using the SQL Server Web Data Administrator instead:
http://www.microsoft.com/downloads/...&displaylang=en
David Gugick
Quest Software
www.imceda.com
www.quest.com

Need Enterprise Manager for Remote Site

I need to get Enterprise Manager to a user at a remote site and I don't want
to provide shared access to my sql 2000 install cd.
What/where can I download to provide a user with the Enterprise Manager mmc
console snap-in?
--
Thanks
Paul BergsonIf that is a "licenced" user, you can get him a copy of the cd, but if he is
not licenced he is not allowed to install it. There is no snap in
downloadable for the enterprise manager, it all comes with the cd.
Another options would be: http://www.aspfaq.com/show.asp?id=2442
HTH, Jens Suessmeyer.
"Paul Bergson" wrote:
> I need to get Enterprise Manager to a user at a remote site and I don't want
> to provide shared access to my sql 2000 install cd.
> What/where can I download to provide a user with the Enterprise Manager mmc
> console snap-in?
>
> --
> Thanks
> Paul Bergson
>
>|||Paul Bergson wrote:
> I need to get Enterprise Manager to a user at a remote site and I
> don't want to provide shared access to my sql 2000 install cd.
> What/where can I download to provide a user with the Enterprise
> Manager mmc console snap-in?
Try using the SQL Server Web Data Administrator instead:
http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959&displaylang=en
David Gugick
Quest Software
www.imceda.com
www.quest.com

Need basic guidance on massive access to sql conversion

I have a rather daunting task ahead of me. I have ot take an access mdb and bring it into sql express. Converting the tables is not a biggie, but I have more than 2500 queries that need to be brought over. I have several questions.

1. In many places I use queries with parameters as views. For example I might have a query that takes a couple of parameters and then turn around write another query that uses the first query as a table. Is there a way to do parameterized views in Sql?

2. Does anyone know of a decent automated tool to do this? The upsizing wizard is useless and I tried teh sql migration assistant, and it doesn't convert queries that have parameters.

3. Has anyone done anything on this kind of scale before?

Thanks ... Ed

1. The closest to parameterized views is using Table Valued Functions. Check in Books Online about using TVFs.

2. No 'decent tools' known. Parameterized views can be 'troublesome'.

3. Yes, and it will take a lot of dilligent and concentrated effort.

and 4. Good Luck -because you WILL need it!

Need Assistance: Trying to run jobs and getting nowhere/SQLServerAgent

I've gone through hundreds of posts here and on the Net. I'm creating an ssis package to access my db on my desktop. I then save a copy as "Test" on the msdb database on our sql 2005 box.

Try to create a job and I'm getting nowhere. My SQL Server Agent account is a member of local Administrator group and a member of the domain.

Now what I noticed is if I run the dtsexec from the command prompt and use the command line string then it works. Just seems like I'm not able to run anything from the SQL Server>>Jobs interface.

Please Help :)

What errors do you get? Have you seen http://support.microsoft.com/kb/918760?|||

I have it working with the set password but I'd like not to do this in case other developers create packages and then forget. I'd like to use the below method but how do you do this --> "SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
"

Method 1: Use a SQL Server Agent proxy account

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

|||Books online have topic Creating SQL Server Agent Proxies under Administering the Database Engine > Automating Administrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security >

Need Assistance: Trying to run jobs and getting nowhere/SQLServerAgent

I've gone through hundreds of posts here and on the Net. I'm creating an ssis package to access my db on my desktop. I then save a copy as "Test" on the msdb database on our sql 2005 box.

Try to create a job and I'm getting nowhere. My SQL Server Agent account is a member of local Administrator group and a member of the domain.

Now what I noticed is if I run the dtsexec from the command prompt and use the command line string then it works. Just seems like I'm not able to run anything from the SQL Server>>Jobs interface.

Please Help :)

What errors do you get? Have you seen http://support.microsoft.com/kb/918760?|||

I have it working with the set password but I'd like not to do this in case other developers create packages and then forget. I'd like to use the below method but how do you do this --> "SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
"

Method 1: Use a SQL Server Agent proxy account

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

|||Books online have topicCreating SQL Server Agent Proxies under Administering the Database Engine > Automating Administrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security >

Need an opinion: Access vs. SQL Server Express or another alternative?

I inherited this website from a previous developer that uses an Access DB as a backend. Ugh. Apparently, he was extremely limited in what databases he could use. The website is filled with problems related to connecting to this database, which is no surprise. Most of them are related to permissions to the database file, multiple user access, etc.

I am thinking about moving this to a SQL Server Express DB and upgrading the site to the 2.0 framework. Most of my experience is in SQL Server, but since they can't, or won't, pay for the SQL Server Database, is this a good solution? I've considered using MySQL as well, but their host won't provide support for this. Are there any other recommended alternatives?

I would go for SQL Express out of any of those alternatives... and I've done a lot of alternatives. SQL Express is actually very nice, and if you are already farmiliar with SQL Server, then a lot of the syntax will still make sense.

Actually,www.SingingEels.com was running on SQL Express for a while, but when I put it on my shared hosting environment, they have SQL Server 2005, and don't support SQL Express, so I moved it to that.

|||

I suggest that you move toSQL Server 2005 Express Edition which is completely free (though it has less features than the commercial full edition) and good to use for small-medium websites. It is a much better alternative than MS Access, besides its free for commercial use too.

Hope this helps,

Vivek

Need advice on SQL Server account to use for my ASP.NET application

Hi,

I know it's bad to use the built-in SA account to access SQL Server from my ASP.NET application.

I did some reading but would like some suggestions as well. One article I read talked about Application roles in SQL Server. Is this the best way to handle SQL Server access for read/write/update/delete privileges from an ASP.NET application?

If this is the way to go, how do I set it up? Can anyone suggest a good web site to read up on this?Yes look at the Microsoft Patterns and Practices Site..."Building Secure Microsoft ASP.NET Applications"

Need advice on obtaining an SQL server

The company I work for is looking into purchasing a server and SQL license
to serve database content. We are currently using IIS and Access.
The price was estimated to be $10,000. It seemed a little steep to me, but
our IT guy said that when you buy SQL for web use they charge more than if
you only needed it for use within a company. We are a non-profit government
appointed agency and are not setting this up for sales, just for serving
data to the public free of charge. And only the web server would be
interacting with the SQL server, we are not trying to set up some kind of
tunneling application for people to access the SQL server directly. The web
administrator and web server would be the only two clients.
Does this sound like the correct price for a basic SQL server? The lowest
tier of service should suit us fine, we just want a boost in speed from the
IIS/Access setup and avoid the odd crashes associated it (once every few
months).
I can only comment on the SQL Server side:
If you expose data through an app server (which IIS is in this aspect), you need to either buy per
processor license or make sure that every user who uses the app server (your web site) has a SQL
Server Client Access License. I assume that the web server is public, so you need to go per
processor. It is *not* enough to have a CAL for only the app server (read about multiplexing in the
licensing doc).
One thing to consider is SQL Server Express.
Another thing is if MS has some special deal for non-profit organizations. Talk to your MS rep about
this.
Licensing FAQ: http://www.microsoft.com/sql/howtobuy/faq.mspx
To speak to someone regarding 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
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt" <mattmatt32@.notmail.com> wrote in message news:OFO$G%23T5FHA.3384@.TK2MSFTNGP11.phx.gbl...
> The company I work for is looking into purchasing a server and SQL license to serve database
> content. We are currently using IIS and Access.
> The price was estimated to be $10,000. It seemed a little steep to me, but our IT guy said that
> when you buy SQL for web use they charge more than if you only needed it for use within a company.
> We are a non-profit government appointed agency and are not setting this up for sales, just for
> serving data to the public free of charge. And only the web server would be interacting with the
> SQL server, we are not trying to set up some kind of tunneling application for people to access
> the SQL server directly. The web administrator and web server would be the only two clients.
> Does this sound like the correct price for a basic SQL server? The lowest tier of service should
> suit us fine, we just want a boost in speed from the IIS/Access setup and avoid the odd crashes
> associated it (once every few months).
>
|||Seems like SQL Express would be ideal for you (and it is free
http://msdn.microsoft.com/sql/express/default.aspx
William Stacey [MVP]
"Matt" <mattmatt32@.notmail.com> wrote in message
news:OFO$G%23T5FHA.3384@.TK2MSFTNGP11.phx.gbl...
> The company I work for is looking into purchasing a server and SQL license
> to serve database content. We are currently using IIS and Access.
> The price was estimated to be $10,000. It seemed a little steep to me, but
> our IT guy said that when you buy SQL for web use they charge more than if
> you only needed it for use within a company. We are a non-profit
> government appointed agency and are not setting this up for sales, just
> for serving data to the public free of charge. And only the web server
> would be interacting with the SQL server, we are not trying to set up some
> kind of tunneling application for people to access the SQL server
> directly. The web administrator and web server would be the only two
> clients.
> Does this sound like the correct price for a basic SQL server? The lowest
> tier of service should suit us fine, we just want a boost in speed from
> the IIS/Access setup and avoid the odd crashes associated it (once every
> few months).
>

Need advice on obtaining an SQL server

The company I work for is looking into purchasing a server and SQL license
to serve database content. We are currently using IIS and Access.
The price was estimated to be $10,000. It seemed a little steep to me, but
our IT guy said that when you buy SQL for web use they charge more than if
you only needed it for use within a company. We are a non-profit government
appointed agency and are not setting this up for sales, just for serving
data to the public free of charge. And only the web server would be
interacting with the SQL server, we are not trying to set up some kind of
tunneling application for people to access the SQL server directly. The web
administrator and web server would be the only two clients.
Does this sound like the correct price for a basic SQL server? The lowest
tier of service should suit us fine, we just want a boost in speed from the
IIS/Access setup and avoid the odd crashes associated it (once every few
months).I can only comment on the SQL Server side:
If you expose data through an app server (which IIS is in this aspect), you
need to either buy per
processor license or make sure that every user who uses the app server (your
web site) has a SQL
Server Client Access License. I assume that the web server is public, so you
need to go per
processor. It is *not* enough to have a CAL for only the app server (read ab
out multiplexing in the
licensing doc).
One thing to consider is SQL Server Express.
Another thing is if MS has some special deal for non-profit organizations. T
alk to your MS rep about
this.
Licensing FAQ: http://www.microsoft.com/sql/howtobuy/faq.mspx
To speak to someone regarding 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
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt" <mattmatt32@.notmail.com> wrote in message news:OFO$G%23T5FHA.3384@.TK2MSFTNGP11.phx.gb
l...
> The company I work for is looking into purchasing a server and SQL license
to serve database
> content. We are currently using IIS and Access.
> The price was estimated to be $10,000. It seemed a little steep to me, but
our IT guy said that
> when you buy SQL for web use they charge more than if you only needed it f
or use within a company.
> We are a non-profit government appointed agency and are not setting this u
p for sales, just for
> serving data to the public free of charge. And only the web server would b
e interacting with the
> SQL server, we are not trying to set up some kind of tunneling application
for people to access
> the SQL server directly. The web administrator and web server would be the
only two clients.
> Does this sound like the correct price for a basic SQL server? The lowest
tier of service should
> suit us fine, we just want a boost in speed from the IIS/Access setup and
avoid the odd crashes
> associated it (once every few months).
>|||Seems like SQL Express would be ideal for you (and it is free
http://msdn.microsoft.com/sql/express/default.aspx
William Stacey [MVP]
"Matt" <mattmatt32@.notmail.com> wrote in message
news:OFO$G%23T5FHA.3384@.TK2MSFTNGP11.phx.gbl...
> The company I work for is looking into purchasing a server and SQL license
> to serve database content. We are currently using IIS and Access.
> The price was estimated to be $10,000. It seemed a little steep to me, but
> our IT guy said that when you buy SQL for web use they charge more than if
> you only needed it for use within a company. We are a non-profit
> government appointed agency and are not setting this up for sales, just
> for serving data to the public free of charge. And only the web server
> would be interacting with the SQL server, we are not trying to set up some
> kind of tunneling application for people to access the SQL server
> directly. The web administrator and web server would be the only two
> clients.
> Does this sound like the correct price for a basic SQL server? The lowest
> tier of service should suit us fine, we just want a boost in speed from
> the IIS/Access setup and avoid the odd crashes associated it (once every
> few months).
>