Friday, March 23, 2012
Promt user for criteria ?
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
Prompts in Model Designer
I would like to set up a prompt / filter as part of the model that carries through to the report builder. I have a query where I would like to force a prompt on the user as part of any report they create. Ideally, I would like to have it default as well.
I noticed that there is a prompt attribute in model designer. When I go to add the filter attribute in model designer, I get the same dialog box I get in the report builder - except it does not have the prompt as an option. Is there a way to accomplish this?
Thanks
There is no way to specify in the report model that filter conditions created for a given attribute should be set to "Prompt" by default.
It's an interesting request, though. If you have a minute, I'd be interested to hear more about why this feature is important to you. You can contact me through my blog.
|||For the same reason an administrator might set up Prompts on Reports in RS...RB reports are no different. We might want to impose certain filters...either to guide users in report building, or perhaps to limit the amount of resources in querying. the reasons are too numerous to list.
Having the capability on the user front-end but not the development back-end is a deficiency.
sqlPrompts in Model Designer
I would like to set up a prompt / filter as part of the model that carries through to the report builder. I have a query where I would like to force a prompt on the user as part of any report they create. Ideally, I would like to have it default as well.
I noticed that there is a prompt attribute in model designer. When I go to add the filter attribute in model designer, I get the same dialog box I get in the report builder - except it does not have the prompt as an option. Is there a way to accomplish this?
Thanks
There is no way to specify in the report model that filter conditions created for a given attribute should be set to "Prompt" by default.
It's an interesting request, though. If you have a minute, I'd be interested to hear more about why this feature is important to you. You can contact me through my blog.
|||For the same reason an administrator might set up Prompts on Reports in RS...RB reports are no different. We might want to impose certain filters...either to guide users in report building, or perhaps to limit the amount of resources in querying. the reasons are too numerous to list.
Having the capability on the user front-end but not the development back-end is a deficiency.
Prompting for input from user using SQL Server 2000...
ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
How do you do it in SQL Server 2000?SQL Server cannot do this, as SQL Server executes entirely at the server
side. (I take it that you don't want to prompt to pop up on the server
machine?)
So, this has to be done my the client application. None of the tools that
comes with SQL Server has that feature, as they aren't designed to be
end-user tools not development packages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
> I know how to do it in Oracle.
> ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
> How do you do it in SQL Server 2000?
>|||Hi,
You do not have that kind of facility in SQL Server.
Thanks
Hari
MCDBA
"Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
> I know how to do it in Oracle.
> ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
> How do you do it in SQL Server 2000?
>|||I want to create a procedure the I can run from Query Analyzer that prompts
the user for input and supplies it to the procedure.
Not a big deal, but the client can do it in Oracle and wonders why SQL
can't.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OOR%23rXIBEHA.2628@.TK2MSFTNGP11.phx.gbl...
> SQL Server cannot do this, as SQL Server executes entirely at the server
> side. (I take it that you don't want to prompt to pop up on the server
> machine?)
> So, this has to be done my the client application. None of the tools that
> comes with SQL Server has that feature, as they aren't designed to be
> end-user tools not development packages.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
> news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
>|||Hi,
In Query analyzer, Object browser window , right click above the procedure
name and select the debug option. This will ask you to enter the
input parameters of the procedure.
(It may not be same as you required, but allows you to take parameter
values.)
Thanks
Hari
MCDBA
"Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
news:GaM2c.137196$%72.44266@.twister.nyroc.rr.com...
> I want to create a procedure the I can run from Query Analyzer that
prompts
> the user for input and supplies it to the procedure.
> Not a big deal, but the client can do it in Oracle and wonders why SQL
> can't.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OOR%23rXIBEHA.2628@.TK2MSFTNGP11.phx.gbl...
that
>
Prompting for input from user using SQL Server 2000...
ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
How do you do it in SQL Server 2000?SQL Server cannot do this, as SQL Server executes entirely at the server
side. (I take it that you don't want to prompt to pop up on the server
machine?)
So, this has to be done my the client application. None of the tools that
comes with SQL Server has that feature, as they aren't designed to be
end-user tools not development packages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
> I know how to do it in Oracle.
> ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
> How do you do it in SQL Server 2000?
>|||Hi,
You do not have that kind of facility in SQL Server.
Thanks
Hari
MCDBA
"Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
> I know how to do it in Oracle.
> ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
> How do you do it in SQL Server 2000?
>|||I want to create a procedure the I can run from Query Analyzer that prompts
the user for input and supplies it to the procedure.
Not a big deal, but the client can do it in Oracle and wonders why SQL
can't.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OOR%23rXIBEHA.2628@.TK2MSFTNGP11.phx.gbl...
> SQL Server cannot do this, as SQL Server executes entirely at the server
> side. (I take it that you don't want to prompt to pop up on the server
> machine?)
> So, this has to be done my the client application. None of the tools that
> comes with SQL Server has that feature, as they aren't designed to be
> end-user tools not development packages.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
> news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
> > I know how to do it in Oracle.
> >
> > ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
> >
> > How do you do it in SQL Server 2000?
> >
> >
>|||Hi,
In Query analyzer, Object browser window , right click above the procedure
name and select the debug option. This will ask you to enter the
input parameters of the procedure.
(It may not be same as you required, but allows you to take parameter
values.)
Thanks
Hari
MCDBA
"Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
news:GaM2c.137196$%72.44266@.twister.nyroc.rr.com...
> I want to create a procedure the I can run from Query Analyzer that
prompts
> the user for input and supplies it to the procedure.
> Not a big deal, but the client can do it in Oracle and wonders why SQL
> can't.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OOR%23rXIBEHA.2628@.TK2MSFTNGP11.phx.gbl...
> > SQL Server cannot do this, as SQL Server executes entirely at the server
> > side. (I take it that you don't want to prompt to pop up on the server
> > machine?)
> > So, this has to be done my the client application. None of the tools
that
> > comes with SQL Server has that feature, as they aren't designed to be
> > end-user tools not development packages.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
> > news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
> > > I know how to do it in Oracle.
> > >
> > > ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
> > >
> > > How do you do it in SQL Server 2000?
> > >
> > >
> >
> >
>
prompt/parameter position in UI
The ordering of the controls is based on the ordering in the rdl. The layout for the parameters in Report Manager is not user adjustable. However, if you want to create your own web pages you can use the new web viewer control and create your own parameter area, ordering and displaying the parameters however you please. There is a significant amount of work required to do this generically, but it would be farily simple for a small set of reports and parameters.
-Daniel
prompt/parameter position in UI
The ordering of the controls is based on the ordering in the rdl. The layout for the parameters in Report Manager is not user adjustable. However, if you want to create your own web pages you can use the new web viewer control and create your own parameter area, ordering and displaying the parameters however you please. There is a significant amount of work required to do this generically, but it would be farily simple for a small set of reports and parameters.
-Daniel
sqlPrompt when new message is submitted
An easy expamle of this that everyone is really fimaler with is myspace. When a new database entry is submitted then on the admin page it will say like new message(s).. how can I do this. It doesnt have to be per member. It is only going to be on the admin page.
Anyone?
prompt user from stored procedure?
The procedure currently skips over recipient tables that are already populated with the data that's being moved. I want the ability to either skip the table or delete the existing records from the recpient table data based on the user's response. Therefore, I somehow need to prompt the user to get a response when the data already exists in the existing table.
Is there any way to prompt the user from a stored procedure, or do I have to re-develop the procedure in DTS or write and application?You need to write an application. SQL Server has no built in interface for interacting with the user.
Your simplest approach might be to create an Access Data Project tied to your SQL Server database. Then you could easily create forms and vb code to do what you want.|||That's what I initially thought. Thanks.
Prompt password when calling certain reports only?
Hi,
When I call certain reports from an ASP application, some prompt for password and some are not.
I wonder why is this happen?
Under the Directory Security of Reports under IIS 6, I already enable Anonymous Access for IUSR_XXXX with the correct password.
I also assign IUSR_XXXX as Browser in Report Manager of Reporting Services.
The enviroment is Windows Server 2003 standard edition, IIS 6, SQL Server 2005.
Thanks in advance.
Best regards,
Pop
You can check the data source settings for the reports. There is an option to prompt for password for the datasources of reports.|||I had try
1)"Use Windows Authentication"
2)"Use a specific user name and password"
3)"No credentials"
in Data source setting and non of it works.
When I try with option 1 and 2, only one of the reports prompt for password(so far that's what I discover, only 1 report prompt for password), the rest no need.
Any clue?
Best regards,
Pop
Prompt Parameter Query?
How to I convert Northwind Access Query look like this
SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName,
Employees.HireDate
FROM Employees
WHERE
(((Employees.HireDate) Between [Enter Begining date]
And
[Enter ending date]))
OR
(((([Employees].[HireDate]) Like [Enter Begining date]) Is Null))
OR
(((([Employees].[HireDate]) Like [Enter ending date]) Is Null));
Into SQL Server 2005 Stored Proc. I tried
create proc usp_hdate as
declare @.Hdate datetime
select FirstName, LastName, HireDate
From Employees
Where (HireDate = @.Hdate) or HireDate Is Not Null
but no results
All I want to create prompt parameter for HireDate
When you don't type parameter It will return all records
when you type the date it will return specific record
Thanks an advanced
Oded DrorCREATE PROC usp_hdate
AS
DECLARE @.Hdate DATETIME
SELECT FirstName, LastName, HireDate
FROM Employees
WHERE HireDate = COALESCE(@.Hdate,HireDate)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Oded Dror wrote:
> Hi there,
> How to I convert Northwind Access Query look like this
> SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName,
> Employees.HireDate
> FROM Employees
> WHERE
> (((Employees.HireDate) Between [Enter Begining date]
> And
> [Enter ending date]))
> OR
> (((([Employees].[HireDate]) Like [Enter Begining date]) Is Null))
> OR
> (((([Employees].[HireDate]) Like [Enter ending date]) Is Null));
>
> Into SQL Server 2005 Stored Proc. I tried
> create proc usp_hdate as
> declare @.Hdate datetime
> select FirstName, LastName, HireDate
> From Employees
> Where (HireDate = @.Hdate) or HireDate Is Not Null
> but no results
> All I want to create prompt parameter for HireDate
> When you don't type parameter It will return all records
> when you type the date it will return specific record
>
> Thanks an advanced
> Oded Dror
>
>
>
>|||Thanks for your help
This will return all value if the parameter is null
but what about when I'm submitting parameter
Basically I want to submit parameter and received one record or
don't submit record and received all records
Thanks,
Ed Dror
"MGFoster" <me@.privacy.com> wrote in message
news:iiVyf.3116$Hd4.2207@.newsread1.news.pas.earthlink.net...
> CREATE PROC usp_hdate
> AS
> DECLARE @.Hdate DATETIME
> SELECT FirstName, LastName, HireDate
> FROM Employees
> WHERE HireDate = COALESCE(@.Hdate,HireDate)
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> Oded Dror wrote:
Prompt Parameter in Ad Hoc Reports
In the filter window, i want to add a field and make it into a prompt. But
then when the users run the report they should have the ability to not select
anything from the prompt and hence see the report for all values of that
field...On Feb 27, 11:47 am, Tk_Neo <T...@.discussions.microsoft.com> wrote:
> Can i do this?
> In the filter window, i want to add a field and make it into a prompt. But
> then when the users run the report they should have the ability to not select
> anything from the prompt and hence see the report for all values of that
> field...
One way of doing it is to create a report parameter for the filter
options (incl. no filter as an option) and either use the report
parameter value selected to filter with -or- use the report parameter
in the query or stored procedure to filter the results. Hope this
helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I posted a way to do this in the msdn forum:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1285083&SiteID=1
hth
Helen
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1172636771.488607.218240@.t69g2000cwt.googlegroups.com...
> On Feb 27, 11:47 am, Tk_Neo <T...@.discussions.microsoft.com> wrote:
>> Can i do this?
>> In the filter window, i want to add a field and make it into a prompt.
>> But
>> then when the users run the report they should have the ability to not
>> select
>> anything from the prompt and hence see the report for all values of that
>> field...
> One way of doing it is to create a report parameter for the filter
> options (incl. no filter as an option) and either use the report
> parameter value selected to filter with -or- use the report parameter
> in the query or stored procedure to filter the results. Hope this
> helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>sql
Prompt page in SSRS
Hi,
I checked in SQL Server Reporting Services and it has the option to create only the reports. How do I create any prompt page from SSRS? Is it possible to do it? Thank You.
Regards,
Das.
What do you mean by prompt page ? Asking for some parameters before rendering the report ? That can be done by clicking in the background of the report and navigating through report parameters.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Prompt for variable values in a SQL script
Thanks in advance for your timeNo, you can't prompt for variables in the SQL script.
You can create your script as a stored procedure with parameters, or you can define and set your variables at the top of the script so the user can easily modify them (good programming practice anyway!).
That said...
bad programming practice is letting users run scripts from Query Analyzer! I hope the "users" have knowledge of databases, and I hope their server and database permissions are well defined, or they could inadvertently (or even maliciously) mess things up.
Why not build a simple interface, such as an Access Data Project, that calls the procedure after prompting the User for parameters?
blindman|||I realize that it is a bad and dangerous practice to let the users access the database from the query analyzer. We are redesigning the system from the scratch but till then, we have to support the existing system. Prevoius DBA let this hole into the system and we have to live with this till we finish our re-design. I was almost positive what I was looking for is not possible but I just wanted to make sure. Thanks a lot for your time blindman.
Originally posted by blindman
No, you can't prompt for variables in the SQL script.
You can create your script as a stored procedure with parameters, or you can define and set your variables at the top of the script so the user can easily modify them (good programming practice anyway!).
That said...
bad programming practice is letting users run scripts from Query Analyzer! I hope the "users" have knowledge of databases, and I hope their server and database permissions are well defined, or they could inadvertently (or even maliciously) mess things up.
Why not build a simple interface, such as an Access Data Project, that calls the procedure after prompting the User for parameters?
blindman
Prompt for value
Can I do this in query analyzer or do I have to send the value from some other application?the query analyser cannot prompt the user for a value.|||Prompting for values is the responsibility of the application interface. Query Analyzer is a tool, but it is by no means an application interface.
prompt for value
-PatP
Prompt for user input in criteria field of view
In Access, I use [Enter Date] in the Criteria field of the Query. I tried the same thing in SQL Server in the Criteria field of the View and it does not recognize this. Is there a comparable command in SQL to get user input into the Criteria field of a view?
Hi,
you either have to use a procedure with an input parameter or have to put a condition on the query with querying the view with:
Select * from SomeView Where SomeColumn = 'SomeValue'
But there is no GUI on SQL Server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||MSSQL as service prompts you nothing. You have to write client application to be prompted.|||Hi Jens,
I was able to find out how to do what I needed using the @. sign (i.e. @.Date Required?). In the criteria field of the SQL view, this generates a 'Date Required?' prompt box when running the view.
Thanks anyway!
Ernie
sqlPrompt for user credentials for database
Hello friends,
In my web application I have saved my connection string into web.config file. Now I have to send this web application to the client in the form of web set up. The issue is that on the client side, user credentials for the database will be different. So can we do some setings that the web application whenever will run should ask for the server name, username & Password. If this can be done then the application can be run anywhere considering all compatibilities.
Please let me know how this can be done.
Thanks & Regards
Girish Nehte
Hi,
We also encounter same issue...we have done following things...
we have stored IP,Database Name,userid and password ( separated by "," ) in notepad file and saved it at some common location.
and in applications we are referring this information to access database...
and we are telling client to put these details in file...so user can not make any other changes in web.config by mistake.
|||
Hi there,
Maybe I didn't understand well what's your problem, but it seems quite simple to me.
You only have one connection string (or user of the database). Then you have, for example, a table with many usernames/passwords you like.
When your client uses the applicaction, you ask for credentials right? Then you use your connection string, to check if its credentials match with any stored on the passwords table! If so you allow him to continue. If not, you don't allow the client to continue.
Hope it helped out :) And its safer than having it written on a text file!
gonzzas
Wednesday, March 21, 2012
prompt for parameter in a view
Much the same way a user can provide the paramenter for a query in Access.
Seems like it should be simple, but I don't know how to do it.
Any ideas?
Thanks
Tyler
Hi,
SQL Server will not prompt for a user input. Access will allow because it
have both Front end and back end.
Thanks
Hari
SQL Server MVP
"Tyler" <Tyler@.discussions.microsoft.com> wrote in message
news:CA2CA968-4B15-4ED6-AECF-FE3C17C119AB@.microsoft.com...
>I would like to create a view that will prompt the user for a parameter.
> Much the same way a user can provide the paramenter for a query in Access.
> Seems like it should be simple, but I don't know how to do it.
> Any ideas?
> Thanks
> --
> Tyler
Prompt for login credentials using Windows-NT Authentication
I am using a SQL Server (MSDE) db, currently configured to only accept
Windows-NT Authentication logins. This has worked well for a while -
much easier to administer. However I have a group of users that have
presented a special problem.
These users have valid logins under a valid Windows Domain ie
<Domain>\<user>. Unlike most users, however, they do not login to the
windows domain when they log into their machines (instead they login
to the local machine). In cases other than mine, when they access
applications/web services that require authentication to the said
windows domain, they are prompted for login credentials (ie
username=<domain>\<user> and password). They can then proceed to use
the application/web service.
With my SQL Server database, however, this does not happen. When they
attempt to login to my database, I am guessing that the server figures
out that they are not currently logged in to the windows domain, so
denies them access outright. They are given no opportunity to enter
their credentials. They are attempting to access the database using
data access pages / OWC10.
Is there any way that I can setup a connection string which allows me
to continue using Windows-NT Authentication, but will prompt users for
login credentials if they are not currently logged in to the required
Windows Domain?
Thanks heaps for your help.
Nick TompsonIt's been a while since I've worked with data access pages, but as I
recollect the connection is hard-wired into the page. What I think
you're going to have to do is to provide a login where they can join
the domain that is accredited with your SQL Server prior to loading
the page. I could be wrong about this -- you might want to post the
question in the microsoft.public.access.dataaccess.pages ng.
-- Mary
On 2 May 2004 23:48:13 -0700, ntompson@.utas.edu.au (Nick Tompson)
wrote:
>Hello,
>I am using a SQL Server (MSDE) db, currently configured to only accept
>Windows-NT Authentication logins. This has worked well for a while -
>much easier to administer. However I have a group of users that have
>presented a special problem.
>These users have valid logins under a valid Windows Domain ie
><Domain>\<user>. Unlike most users, however, they do not login to the
>windows domain when they log into their machines (instead they login
>to the local machine). In cases other than mine, when they access
>applications/web services that require authentication to the said
>windows domain, they are prompted for login credentials (ie
>username=<domain>\<user> and password). They can then proceed to use
>the application/web service.
>With my SQL Server database, however, this does not happen. When they
>attempt to login to my database, I am guessing that the server figures
>out that they are not currently logged in to the windows domain, so
>denies them access outright. They are given no opportunity to enter
>their credentials. They are attempting to access the database using
>data access pages / OWC10.
>Is there any way that I can setup a connection string which allows me
>to continue using Windows-NT Authentication, but will prompt users for
>login credentials if they are not currently logged in to the required
>Windows Domain?
>Thanks heaps for your help.
>Nick Tompson|||If the users log on to the local machine then that is the login tha SQL
Server expects. You can workaround this problem by creating a user account
on the SQL Server machine with the same name and password as the users who
are logging in and grant that login a login within SQL Server. This should
allow them to log in to SQL Server using the local machine account.
Outside of that I am not sure there is a way, unless you can get the IIS
anonymous login to be used by these users.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Thankyou both for your comments. I think that your solutions may work,
however, I am keen to stick with Windows-NT Authentication if I can, as
I do not want to have to manage usernames and passwords.
Any other ideas?
Thanks
Nick Tompson
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||If you don't want to create mirrored local accounts, you might try having
the users map a drive to a share on the SQL Server using their domain
account. I believe SQL Server will use these security credentials for the
trusted connection.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nick Tompson" <ntompson@.utas.edu.au> wrote in message
news:uOTSDsWMEHA.3012@.tk2msftngp13.phx.gbl...
> Thankyou both for your comments. I think that your solutions may work,
> however, I am keen to stick with Windows-NT Authentication if I can, as
> I do not want to have to manage usernames and passwords.
> Any other ideas?
> Thanks
> Nick Tompson
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!