Promt user for criteria ?

I know it can be done with a SP but is there a way to prompt a user for
specific criteria like date range (between ? and ?) in a view.
I have a query in a view but I need to prompt the user for a date range, my
front end is access 2002 I can do it as a passthrough but it takes to long to
run and while testing I found that as a view it runs in half the time.
Thanks
Xavier
On Fri, 20 Jan 2006 07:36:05 -0800, Xavier wrote:

>I know it can be done with a SP but is there a way to prompt a user for
>specific criteria like date range (between ? and ?) in a view.
>I have a query in a view but I need to prompt the user for a date range, my
>front end is access 2002 I can do it as a passthrough but it takes to long to
>run and while testing I found that as a view it runs in half the time.
Hi Xavier,
SQL Server can't ever prompt the user. Not in a view, and not in a
stored procedure either.
If you refer to prompting for arguments in the front end, then passing
them as parameters to the back end: that is possible in stored procs,
but not in a view. But you can use variables in a SELECT statement that
queries a view.
However, I am intrigued by your statement that you found a view to be
faster than a stored procedure. While I don't doubt your observations,
I'm pretty sure that there's no such blanket statement about performance
of views vs stored procedures. I suspect that is has something to do
with the specific details of your tables, your stored procedure and your
view.
To investigate this further, you'll have to give more details: how do
your tables look (CREATE TABLE statements), how are the view and the
stored procedure defined (CREATE VIEW / CREATE PROC), what does your
data typically look like (INSERT statements for a small sample of your
data) and how do you call the view and the stored proc?
Chcek out www.aspfaq.com/5006 for some tips on how to assemble this
information.
Hugo Kornelis, SQL Server MVP
|||Hugo
You are correct I mistakenly said faster than a SP but I should has said
faster that a passthrough query from my front end which is access.
I could not run my report from access unless I add the view to my access
front ent as a linked table, is there any other way ? for an access report to
run a sql view and pass a parameter, like a date range to extrack particular
data example ()now
minus 48hrs everytime the user runs it.
Thanks
Xavier
"Hugo Kornelis" wrote:

> On Fri, 20 Jan 2006 07:36:05 -0800, Xavier wrote:
>
> Hi Xavier,
> SQL Server can't ever prompt the user. Not in a view, and not in a
> stored procedure either.
> If you refer to prompting for arguments in the front end, then passing
> them as parameters to the back end: that is possible in stored procs,
> but not in a view. But you can use variables in a SELECT statement that
> queries a view.
> However, I am intrigued by your statement that you found a view to be
> faster than a stored procedure. While I don't doubt your observations,
> I'm pretty sure that there's no such blanket statement about performance
> of views vs stored procedures. I suspect that is has something to do
> with the specific details of your tables, your stored procedure and your
> view.
> To investigate this further, you'll have to give more details: how do
> your tables look (CREATE TABLE statements), how are the view and the
> stored procedure defined (CREATE VIEW / CREATE PROC), what does your
> data typically look like (INSERT statements for a small sample of your
> data) and how do you call the view and the stored proc?
> Chcek out www.aspfaq.com/5006 for some tips on how to assemble this
> information.
> --
> Hugo Kornelis, SQL Server MVP
>
|||On Mon, 23 Jan 2006 06:36:03 -0800, Xavier wrote:

>Hugo
>You are correct I mistakenly said faster than a SP but I should has said
>faster that a passthrough query from my front end which is access.
>I could not run my report from access unless I add the view to my access
>front ent as a linked table, is there any other way ? for an access report to
>run a sql view and pass a parameter, like a date range to extrack particular
>data example ()now
>minus 48hrs everytime the user runs it.
Hi Xavier,
When using Access, you want to make sure that network traffic is kept at
a minimum. Stored procedures and pass-through queries meet this
requirement without any doubt. I'm less sure about queries on linked
tables.
I have read reports claiming that Access will fetch a complete linked
table over the network to execute the query client-side. I have also
read reports claiming that the former reports are rubbish. My personal
experience with Access is too limited to be able to tell which reports
are true and which aren't. If you want to find out, then run a profiler
trace while running Access and check what Access really sends to the
server.
I would personally use a stored procedure, but that's probably because
I'm more at ease on SQL Server - I know how to squeeze every drop of
performance out of the SP code; I'm not nearrly as proficient in
optimizing Jet SQL.
If your tests indicate that an Access query on an Access linked table
(to a SQL Server table or view) is faster than calling a stored
procedure, then by all means go for it.
Hugo Kornelis, SQL Server MVP
|||Hugo
Thank you, based on your experience with SQL can you recommend
and good books on SP and queries within sql.
We are planning on moving from access as a front end reporting tool and
replacing it with Crystal Reports, any thoughts on this you might want to
share?
We have a web based application where our users need to print certain forms
with the data that is on their screen plus a little more not seen at the
moment
that we have determined this information will print on every form allways.
The provider of our web based application which uses a propriatary tool kit
together with FrontPage to compile the forms will only support Access or
Crystal Reports.
Thanks
Xavier
"Hugo Kornelis" wrote:

> On Mon, 23 Jan 2006 06:36:03 -0800, Xavier wrote:
>
> Hi Xavier,
> When using Access, you want to make sure that network traffic is kept at
> a minimum. Stored procedures and pass-through queries meet this
> requirement without any doubt. I'm less sure about queries on linked
> tables.
> I have read reports claiming that Access will fetch a complete linked
> table over the network to execute the query client-side. I have also
> read reports claiming that the former reports are rubbish. My personal
> experience with Access is too limited to be able to tell which reports
> are true and which aren't. If you want to find out, then run a profiler
> trace while running Access and check what Access really sends to the
> server.
> I would personally use a stored procedure, but that's probably because
> I'm more at ease on SQL Server - I know how to squeeze every drop of
> performance out of the SP code; I'm not nearrly as proficient in
> optimizing Jet SQL.
> If your tests indicate that an Access query on an Access linked table
> (to a SQL Server table or view) is faster than calling a stored
> procedure, then by all means go for it.
> --
> Hugo Kornelis, SQL Server MVP
>
|||On Thu, 26 Jan 2006 18:31:01 -0800, Xavier wrote:

>Hugo
>Thank you, based on your experience with SQL can you recommend
>and good books on SP and queries within sql.
Hi Xavier,
I've learnt most from Books Online and by trial and error. But I'll give
you some titles that are often recommended in these groups by very
kowledgeable persons.
Two books specifically targetted towards coding for SQL Server:
* Advanced Transact-SQL for SQL Server 2000 (Ben-Gan/Moreau)
* The Guru's Guide to Transact-SQL (Henderson)
A book about the inner working of SQL Server - a great aid if you start
to think about fine-tuning, since knowing how things work is the best
way to tune:
* Inside SQL Server 2000 (Delaney)
An advanced book, full with tips and tricks. Definitely no easy stuff
here. And it uses ANSI standard SQL - you'll have to change some things
here and there to amke it run on SQL Server:
* SQL for Smarties (Celko)

>We are planning on moving from access as a front end reporting tool and
>replacing it with Crystal Reports, any thoughts on this you might want to
>share?
I've never worked with Crystal Reports.
Hugo Kornelis, SQL Server MVP

Program type out of range - S1003

We have a Powerbuilder 4.0 application that used to run on SQL Server 7.0. Recently, we migrated our database to SQL Server 2000. We can do ODBC and can connect to the database from any tools such as Access, Excel and register the new SQL Server 2000 to other SQL Server, but when we try to use the client application, the user can not connect to the database. We receive the error message as the subject line. The user interface opens up but there is no data. The orginal program was written in Powerbuilder 4.0 by our vendor who probably does not have a copy of the source codes in proper state as I understand from talking to them. My question is, is there something that I can do to help my user connect to the database by using the client interface? This is getting a bit frustrating as no one really has an answer for it. I tried to install Powerbuilder 9.0 just to see if that updates any driver file that my machine has and that would be recongnize by the client programs, but it will not.

What can we do or what suggestions anyone can extend? Thanks for any inputs.What is the error message ? Look in the sql server log file as well as the event viewer for additional error messages.|||Originally posted by rnealejr
What is the error message ? Look in the sql server log file as well as the event viewer for additional error messages.

Actually, in the SQL Server log there is nothing since no connection was established from the client side.|||Originally posted by wellsound
Actually, in the SQL Server log there is nothing since no connection was established from the client side.

Well then it's not a sql server problem or issue...|||Yes I knew that, but since this is a Powerbuilder-client app with SQL Server backend, I have known that there are problems with the 16 bit ODBC driver that Powerbuilder used in their earlier version which is what the client app is built on, that is Powerbuilder Version 4.0 and that is exactly why I thought of asking in this forum to see if someone may have a related answer to that. But thanks for your reply.|||Is it using a dsn connection ? If so, what are the properties - and have you tried updating the driver and use that dsn ?|||I think I've seen this error before when a variable or column is unable toa accept a value submitted to it. Is there a procedure that runs when the user logs in, and are there any parameters passed to the procedure?

blindman|||Originally posted by rnealejr
Is it using a dsn connection ? If so, what are the properties - and have you tried updating the driver and use that dsn ? This is what we have in the configuration file
[<name of the sytem]
DBMS=SYB SQL Server v4.x
ServerName=, server name>
Database= <database name>
DbParm=appname='name of the app>',release='4.2'

We made a number of changes to this such as change the SYB SQL Server v4.x to DSN and on the DbParm we have used = dsn=<data source name> database = <database> name

None of these seem to work. The only thing we had was the client user interface opens up but no data comes up.