multiple items

the key is in your post above my first post where you ask "how can I build UI
for it" - that is the key - you have to build your own custom user interface
for the report parameters. Instead of using a drop down list to display the
allowable parameter values, use a list box, which supports selecting of
multiple values. You have to go the route of using the web services, not url
access (for my solution) - which direction are you currently headed?
It is a lot of work to create the custom ui, but it does give you the
ability to pass multiple values into a stored procedure and use dynamic SQL
to fuel the dataset for the report.
I hope this helps,Actually you could still use the URL approach and code a function that parses
your parameter string. Simply encode a delimiter between the multi-select
values and use a UDF to parse the parameter in your stored procedure. Unless
the URL will be too large to pass.
--
Keith Powers, MCDBA
"Myles" wrote:
> the key is in your post above my first post where you ask "how can I build UI
> for it" - that is the key - you have to build your own custom user interface
> for the report parameters. Instead of using a drop down list to display the
> allowable parameter values, use a list box, which supports selecting of
> multiple values. You have to go the route of using the web services, not url
> access (for my solution) - which direction are you currently headed?
> It is a lot of work to create the custom ui, but it does give you the
> ability to pass multiple values into a stored procedure and use dynamic SQL
> to fuel the dataset for the report.
>
> I hope this helps,
>|||thanks Keith - would you be able to cut and paste your response into the
correct thread for mvp? I somehow posted this to the wrong area (oops!) The
correct post is 'Select Multiple Items from the Drop Down' - a couple of
threads below.
I was just trying to explain how we did it, but you are entirely correct.
"Keith Powers" wrote:
> Actually you could still use the URL approach and code a function that parses
> your parameter string. Simply encode a delimiter between the multi-select
> values and use a UDF to parse the parameter in your stored procedure. Unless
> the URL will be too large to pass.
> --
> Keith Powers, MCDBA
>
> "Myles" wrote:
> > the key is in your post above my first post where you ask "how can I build UI
> > for it" - that is the key - you have to build your own custom user interface
> > for the report parameters. Instead of using a drop down list to display the
> > allowable parameter values, use a list box, which supports selecting of
> > multiple values. You have to go the route of using the web services, not url
> > access (for my solution) - which direction are you currently headed?
> >
> > It is a lot of work to create the custom ui, but it does give you the
> > ability to pass multiple values into a stored procedure and use dynamic SQL
> > to fuel the dataset for the report.
> >
> >
> > I hope this helps,
> >
> >sql

multiple instances of spid's

I have sql server 2000 setup on 2003. When users are running queries agains
t
SQL Server from MS Access it opens up 10 - 18 instances for that user instea
d
of just 1 instance. Why would this happen and how can I fix it.
Your help/suggestion is greatly appreciated.
Thank youhelpplease wrote:
> I have sql server 2000 setup on 2003. When users are running queries
> against SQL Server from MS Access it opens up 10 - 18 instances for
> that user instead of just 1 instance. Why would this happen and how
> can I fix it.
> Your help/suggestion is greatly appreciated.
> Thank you
Sounds like an application design issue, not a SQL Server issue. You're
not saying you are seeing the same spid in sysprocesses listed multiple
times, are you? Either the application is programmed to perform multiple
connections to the database or this is happening automatically; possibly
because of active result sets in the application. Not an Access expert.
Probably better to ask this question in an Access ng.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes, I am seeing the same spid in sysprocesses listed multiple times.
What could be the problem?
Thank you
Divya
"David Gugick" wrote:

> helpplease wrote:
> Sounds like an application design issue, not a SQL Server issue. You're
> not saying you are seeing the same spid in sysprocesses listed multiple
> times, are you? Either the application is programmed to perform multiple
> connections to the database or this is happening automatically; possibly
> because of active result sets in the application. Not an Access expert.
> Probably better to ask this question in an Access ng.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||helpplease wrote:
> Yes, I am seeing the same spid in sysprocesses listed multiple times.
> What could be the problem?
> Thank you
> Divya
>
That's probably because the same spid is running in multiple threads on
multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
really going on. What version (and SP) of SQL Server are you using? How
many CPUs?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||SQL Server 2000, Service Pack 3.
I have 4 Processors.
Thank you
"David Gugick" wrote:

> helpplease wrote:
> That's probably because the same spid is running in multiple threads on
> multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
> really going on. What version (and SP) of SQL Server are you using? How
> many CPUs?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Any Suggestions on this one.
Thank you
"helpplease" wrote:
[vbcol=seagreen]
> SQL Server 2000, Service Pack 3.
> I have 4 Processors.
> Thank you
> "David Gugick" wrote:
>

multiple instances of spid's

I have sql server 2000 setup on 2003. When users are running queries against
SQL Server from MS Access it opens up 10 - 18 instances for that user instead
of just 1 instance. Why would this happen and how can I fix it.
Your help/suggestion is greatly appreciated.
Thank you
helpplease wrote:
> I have sql server 2000 setup on 2003. When users are running queries
> against SQL Server from MS Access it opens up 10 - 18 instances for
> that user instead of just 1 instance. Why would this happen and how
> can I fix it.
> Your help/suggestion is greatly appreciated.
> Thank you
Sounds like an application design issue, not a SQL Server issue. You're
not saying you are seeing the same spid in sysprocesses listed multiple
times, are you? Either the application is programmed to perform multiple
connections to the database or this is happening automatically; possibly
because of active result sets in the application. Not an Access expert.
Probably better to ask this question in an Access ng.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Yes, I am seeing the same spid in sysprocesses listed multiple times.
What could be the problem?
Thank you
Divya
"David Gugick" wrote:

> helpplease wrote:
> Sounds like an application design issue, not a SQL Server issue. You're
> not saying you are seeing the same spid in sysprocesses listed multiple
> times, are you? Either the application is programmed to perform multiple
> connections to the database or this is happening automatically; possibly
> because of active result sets in the application. Not an Access expert.
> Probably better to ask this question in an Access ng.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||helpplease wrote:
> Yes, I am seeing the same spid in sysprocesses listed multiple times.
> What could be the problem?
> Thank you
> Divya
>
That's probably because the same spid is running in multiple threads on
multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
really going on. What version (and SP) of SQL Server are you using? How
many CPUs?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||SQL Server 2000, Service Pack 3.
I have 4 Processors.
Thank you
"David Gugick" wrote:

> helpplease wrote:
> That's probably because the same spid is running in multiple threads on
> multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
> really going on. What version (and SP) of SQL Server are you using? How
> many CPUs?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Any Suggestions on this one.
Thank you
"helpplease" wrote:
[vbcol=seagreen]
> SQL Server 2000, Service Pack 3.
> I have 4 Processors.
> Thank you
> "David Gugick" wrote:
sql

multiple instances of spid's

I have sql server 2000 setup on 2003. When users are running queries against
SQL Server from MS Access it opens up 10 - 18 instances for that user instead
of just 1 instance. Why would this happen and how can I fix it.
Your help/suggestion is greatly appreciated.
Thank youhelpplease wrote:
> I have sql server 2000 setup on 2003. When users are running queries
> against SQL Server from MS Access it opens up 10 - 18 instances for
> that user instead of just 1 instance. Why would this happen and how
> can I fix it.
> Your help/suggestion is greatly appreciated.
> Thank you
Sounds like an application design issue, not a SQL Server issue. You're
not saying you are seeing the same spid in sysprocesses listed multiple
times, are you? Either the application is programmed to perform multiple
connections to the database or this is happening automatically; possibly
because of active result sets in the application. Not an Access expert.
Probably better to ask this question in an Access ng.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes, I am seeing the same spid in sysprocesses listed multiple times.
What could be the problem?
Thank you
Divya
"David Gugick" wrote:
> helpplease wrote:
> > I have sql server 2000 setup on 2003. When users are running queries
> > against SQL Server from MS Access it opens up 10 - 18 instances for
> > that user instead of just 1 instance. Why would this happen and how
> > can I fix it.
> >
> > Your help/suggestion is greatly appreciated.
> >
> > Thank you
> Sounds like an application design issue, not a SQL Server issue. You're
> not saying you are seeing the same spid in sysprocesses listed multiple
> times, are you? Either the application is programmed to perform multiple
> connections to the database or this is happening automatically; possibly
> because of active result sets in the application. Not an Access expert.
> Probably better to ask this question in an Access ng.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||helpplease wrote:
> Yes, I am seeing the same spid in sysprocesses listed multiple times.
> What could be the problem?
> Thank you
> Divya
>
That's probably because the same spid is running in multiple threads on
multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
really going on. What version (and SP) of SQL Server are you using? How
many CPUs?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||SQL Server 2000, Service Pack 3.
I have 4 Processors.
Thank you
"David Gugick" wrote:
> helpplease wrote:
> > Yes, I am seeing the same spid in sysprocesses listed multiple times.
> >
> > What could be the problem?
> >
> > Thank you
> > Divya
> >
> That's probably because the same spid is running in multiple threads on
> multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
> really going on. What version (and SP) of SQL Server are you using? How
> many CPUs?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Any Suggestions on this one.
Thank you
"helpplease" wrote:
> SQL Server 2000, Service Pack 3.
> I have 4 Processors.
> Thank you
> "David Gugick" wrote:
> > helpplease wrote:
> > > Yes, I am seeing the same spid in sysprocesses listed multiple times.
> > >
> > > What could be the problem?
> > >
> > > Thank you
> > > Divya
> > >
> >
> > That's probably because the same spid is running in multiple threads on
> > multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
> > really going on. What version (and SP) of SQL Server are you using? How
> > many CPUs?
> >
> > --
> > David Gugick
> > Quest Software
> > www.imceda.com
> > www.quest.com
> >
> >

Multiple devices for a subscription

We have a need to send events out to multiple devices for a single subscription, the user may be able to decide what devices with what subscriptions. So far we have the following two ideas for accomplishing this:

1) create a multiple subscriptions, one for each device the user wants the events to go to
2) create a multiple unique device entries for each subscription

So far as I’ve been able to tell there is no transaction support when using the notification services API, so having to update multiple records for a single subscription can lead to further maintenance issues if the updating were to fail part way through.

Is there a simpler way to have events for a single subscription to go out to multiple devices?

Typically you want to separate out subscriber management from subscription management in your application.

In subscriber management you would want the subscribers to configure which devices they have. This typically would happen only once and this list of devices is maintained only once in the instance database/schema. This list of subscribers and their respective devices is common across all applications and maintained only once per instance.

Yes, during subscription management the subscriber would need to create a subscription for each of their devices (your option 1), to ensure the notification is sent to each device.

You correctly cite, there is no transaction support when adding devices or subscriptions. We will evaluate support for for this in next release as we have received this request before in different forms.

Thanks,
Anand

|||

Next release? you mean the 2005 gold release? or next version of SQL server and notification services?

We need the ability to show the user one subscription with multiple devices, so we will handle creating the devices and multiple subscriptions on the back end, and to abtain transaction support we may have to go directly to the stored procedures for the time being.

|||Sorry, I should have been more clear. By next realease I meant the release AFTER SQL Server 2005.|||

Check out this related thread. It links to a posting from Shyam that offers a clever alternative for the current version.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=72056
--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

Multiple Datasets and Report Design problems

I have a table in Database A with users and a table in Database B with
call log records. I need to report the call log for each user in the
User table, sorting by Dept (User table) and then user (user table).
Call data will be detailed for each user. Report will run with a date
range selection. I do not know how to design this. I originally started
with a subreport to print the call detail for a user. Unfortunately, I
need to be able to total and avg call detail for each user, dept which I
believe must happen in the main report. By using a sub report I don?t
think this is possible.
How do I accomplish this?
Thanks in advance.
PamHi Pam,
I'm not sure how your databases are set up (are they on the same
server?), but I would probably choose to combine the data from the
database instead of combining it at the report level - that way you
only need 1 report table and no sub reports and grouping/toggling the
data will be a piece of cake:
SELECT * from User INNER JOIN DatabaseB.dbo.CallLogs CallLogs ON
User.username = CallLogs.username ORDER BY Department, UserName
I hope this helps.
Take Care!
Michelle

Multiple Datasets and Report Design confusion

I have a table in Database A with users and a table in Database B with
call log records. I need to report the call log for each user in the
User table, sorting by Dept (User table) and then user (user table).
Call data will be detailed for each user. Report will run with a date
range selection. I do not know how to design this. I originally started
with a subreport to print the call detail for a user. Unfortunately, I
need to be able to total and avg call detail for each user, dept which I
believe must happen in the main report. By using a sub report I don?t
think this is possible.
Any ideas on how to accomplish this scenario?
Thanks in advance.If both these databases are in the same SQL Server then it is a piece of
cake to do this in a Stored Procedure, just join the two tables. If they are
in different servers it is a little more difficult, you would need to use
linked servers. Same strategy though, you need to look at creating a stored
procedure. Based on your description it does seem to me that a subreport
will not work for you.
Oh, another idea. You don't even need a stored procedure if they are on the
same server, use the generic query designer and create the sql:
select a.field1, a.field2, b.field1, b.field2 from dbname.dbo.usertable a
innerjoin dbname2.dbo.calllog b on a.whatever = b.whatever where
b.somedatefield > @.startdate and b.somedatefield < @.enddate
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"pb" <pbrechlin@.hotmail.com> wrote in message
news:%23v$M9Z7AFHA.2112@.TK2MSFTNGP09.phx.gbl...
> I have a table in Database A with users and a table in Database B with
> call log records. I need to report the call log for each user in the
> User table, sorting by Dept (User table) and then user (user table).
> Call data will be detailed for each user. Report will run with a date
> range selection. I do not know how to design this. I originally started
> with a subreport to print the call detail for a user. Unfortunately, I
> need to be able to total and avg call detail for each user, dept which I
> believe must happen in the main report. By using a sub report I don?t
> think this is possible.
> Any ideas on how to accomplish this scenario?
> Thanks in advance.|||I believe that your best bet would be to either have some process that
creates an intermediary resultant table combining the abstract data
relationship between the two tables, or design your stored procedure so
that you are joining the two together.
As a design key I always try to leave the real work to the best of
breed. In this case you are going to be better off doing your real data
work in the database layer(SQL Server), not in the application layer
(Reporting Services).
-Brian
pb wrote:
> I have a table in Database A with users and a table in Database B
with
> call log records. I need to report the call log for each user in the
> User table, sorting by Dept (User table) and then user (user table).
> Call data will be detailed for each user. Report will run with a date
> range selection. I do not know how to design this. I originally
started
> with a subreport to print the call detail for a user. Unfortunately,
I
> need to be able to total and avg call detail for each user, dept
which I
> believe must happen in the main report. By using a sub report I
don't
> think this is possible.
> Any ideas on how to accomplish this scenario?
> Thanks in advance.|||THanks Brian, Bruce and Michelle. I have chosen to link my servers (data
on two different servers). I created some views and stored procedures.
All is woking great. Thanks for the direction!
Bruce L-C [MVP] wrote:
> If both these databases are in the same SQL Server then it is a piece of
> cake to do this in a Stored Procedure, just join the two tables. If they are
> in different servers it is a little more difficult, you would need to use
> linked servers. Same strategy though, you need to look at creating a stored
> procedure. Based on your description it does seem to me that a subreport
> will not work for you.
> Oh, another idea. You don't even need a stored procedure if they are on the
> same server, use the generic query designer and create the sql:
> select a.field1, a.field2, b.field1, b.field2 from dbname.dbo.usertable a
> innerjoin dbname2.dbo.calllog b on a.whatever = b.whatever where
> b.somedatefield > @.startdate and b.somedatefield < @.enddate
>

multiple data controls in gridview

one of my webpages uses the following sql query to allow the user to search through the database and present the qualifying data in gridview:

SELECT * FROM [Table1] WHERE ([comments] LIKE '%' + ? + '%')

how could i expand this so that the user can also search through the database but instead by searching through another column such as [type]?

thanks in advance

SELECT * FROM [Table1] WHERE ([comments] LIKE '%' + ? + '%') OR ([Type] LIKE '%' + ? + '%')

|||

yeah i tried that but it didnt work...i'll have another go though...

p.s. speedy reply! cheers

|||

yeah again it didnt work i got this stack trace error which i dont have a clue what it means...

[OleDbException (0x80040e07): Data type mismatch in criteria expression.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +177
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +56
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +105
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +91
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1657
System.Web.UI.WebControls.AccessDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +58
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +13
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +140
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68
System.Web.UI.WebControls.GridView.DataBind() +5
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +67
System.Web.UI.Control.EnsureChildControls() +97
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5731

|||

paste your aspx code also

|||

You should give your parameters names rather than using ?'s as placeholders. The problem is with the new query, it is expecting TWO parameters, one for each ? instead of one parameter used twice.

|||

for some reason it double posted so this post was the same as below but now its this until i find out how to delete my own post if its possible

|||

Motley: yeah dont worry i have always dont that, it would make no sense to keep on using ?s

<%@.PageLanguage="VB"AutoEventWireup="false"CodeFile="Login.aspx.vb"Inherits="Default2" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml"xmlns:spry="http://ns.adobe.com/spry">

<headid="Head1"runat="server">

<metahttp-equiv="Content-Type"content="text/html; charset=iso-8859-1"/>

<scriptsrc="SpryMenuBar.js"type="text/javascript"></script> <linkhref="SpryMenuBarHorizontal.css"rel="stylesheet"type="text/css"/>

<linkhref="screen.css"rel="stylesheet"type="text/css"/>

<title>View Products</title>

</head>

<body>

<ulid="MenuBar1"class="MenuBarHorizontal">

<li><ahref="Default.aspx">Home</a></li>

<li><ahref="Gallery.aspx">Gallery</a></li>

<li><ahref="Database.aspx">Database</a></li>

<li><ahref="Contact us.aspx">Contact us</a></li>

<li><ahref="Login.aspx">Login</a></li> </ul>

<scripttype="text/javascript">

<!--

var MenuBar1 =new Spry.Widget.MenuBar("MenuBar1", {imgDown:"SpryMenuBarDownHover.gif", imgRight:"SpryMenuBarRightHover.gif"});

var MenuBar2 =new Spry.Widget.MenuBar("MenuBar2", {imgRight:"SpryMenuBarRightHover.gif"});

//-->

</script>

<br/>

<br/>

<br/>

<br/>

<formid="form1"runat="server">

<div>

<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox>

<asp:ButtonID="Button1"runat="server"Text="Search"/><br/>

<asp:AccessDataSourceID="AccessDataSource1"runat="server"DataFile="~/App_Data/brakes.mdb"

SelectCommand="SELECT * FROM [Table1] WHERE ([comments] LIKE '%' + ? + '%')">

<SelectParameters>

<asp:ControlParameterControlID="TextBox1"DefaultValue="%"Name="?"PropertyName="Text"/>

</SelectParameters>

</asp:AccessDataSource>

<asp:GridViewID="GridView1"runat="server"AllowPaging="True"AllowSorting="True"

AutoGenerateColumns="False"CellPadding="4"DataKeyNames="product code"DataSourceID="AccessDataSource1"

ForeColor="#333333"GridLines="None"PageSize="5">

<FooterStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>

<RowStyleBackColor="#F7F6F3"ForeColor="#333333"/>

<Columns>

<asp:BoundFieldDataField="product code"HeaderText="product code"ReadOnly="True"

SortExpression="product code"/>

<asp:BoundFieldDataField="Name"HeaderText="Name"SortExpression="Name"/>

<asp:BoundFieldDataField="Type"HeaderText="Type"SortExpression="Type"/>

<asp:BoundFieldDataField="Price"HeaderText="Price"SortExpression="Price"/>

<asp:BoundFieldDataField="Comments"HeaderText="Comments"SortExpression="Comments"/>

</Columns>

<PagerStyleBackColor="#284775"ForeColor="White"HorizontalAlign="Center"/>

<SelectedRowStyleBackColor="#E2DED6"Font-Bold="True"ForeColor="#333333"/>

<HeaderStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>

<EditRowStyleBackColor="#999999"/>

<AlternatingRowStyleBackColor="White"ForeColor="#284775"/>

</asp:GridView>

<br/>

</div>

</form> </body>

</html>

and theres my webpage

|||

<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox>

<asp:TextBoxID="TextBox2"runat="server"></asp:TextBox>

<asp:ButtonID="Button1"runat="server"Text="Search"/><br/>

<asp:AccessDataSourceID="AccessDataSource1"runat="server"DataFile="~/App_Data/brakes.mdb"

SelectCommand="SELECT * FROM [Table1] WHERE ([comments] LIKE '%' + @.Comments + '%') OR ([Type] LIKE '%' + @.Type + '%') ">

<SelectParameters>

<asp:ControlParameterControlID="TextBox1"DefaultValue=""Name="comments"PropertyName="Text"/>

<asp:ControlParameterControlID="TextBox2"DefaultValue=""Name="type"PropertyName="Text"/>

</SelectParameters>

</asp:AccessDataSource>

|||

no luck! it still doesnt work, its a step forward though, the page actually loads without a error message, this time i can enter data into the textboxes to search and it just returns all the data not only results that should be returned...

|||

btw how come the page loads when you use the @. in front of the controlD?

|||

something to consider, page loads but the query doesnt work when using '@.comments' (even with songle control),

page loads and query works when using '?' and one control

page doesnt load when using '@.?'

only difference is that comments is text whereas '?' isnt, so what other non-text character can i use? ive tried using '#' but that doesnt work so is '?' the only control name i can use?

Multiple company

Hi. We have a SQL database for one company with multi user access. I am
curious as to best practice solutions for expanding a db to multiple company
access.
Some parts of the db need to be shared, others are private to the company
logged in.
If every time a query was executed it required company filtering, it seems a
waste having company parameters in every minor query, and would require
entire code rewrite. Not being a programmer I don't know what options sql
server may offer, but I know enough about sql to have these sort of questions
distract your thinking until answered...
I hope this is the correct forum for general questions such as this.
Thanks in advance.
swasswas
Create a LOGIN for those 'outside' companies. Grant the database access to
this LOGIN .
You can create VIEWS to show the data that only this LOGIN needs and GRANT
it SELECT ON the VIEW (For more details please refer to the BOL)
As well , you can GRANT an EXECUTE permissions to the LOGIN for STORED
PROCEDURES and not granting permissions on underlaying tables
"swas" <swas@.discussions.microsoft.com> wrote in message
news:C49B53B0-9483-4E87-9166-109ED0495E24@.microsoft.com...
> Hi. We have a SQL database for one company with multi user access. I am
> curious as to best practice solutions for expanding a db to multiple
> company
> access.
> Some parts of the db need to be shared, others are private to the company
> logged in.
> If every time a query was executed it required company filtering, it seems
> a
> waste having company parameters in every minor query, and would require
> entire code rewrite. Not being a programmer I don't know what options sql
> server may offer, but I know enough about sql to have these sort of
> questions
> distract your thinking until answered...
> I hope this is the correct forum for general questions such as this.
> Thanks in advance.
>
> swas

Multiple company

Hi. We have a SQL database for one company with multi user access. I am
curious as to best practice solutions for expanding a db to multiple company
access.
Some parts of the db need to be shared, others are private to the company
logged in.
If every time a query was executed it required company filtering, it seems a
waste having company parameters in every minor query, and would require
entire code rewrite. Not being a programmer I don't know what options sql
server may offer, but I know enough about sql to have these sort of question
s
distract your thinking until answered...
I hope this is the correct forum for general questions such as this.
Thanks in advance.
swasswas
Create a LOGIN for those 'outside' companies. Grant the database access to
this LOGIN .
You can create VIEWS to show the data that only this LOGIN needs and GRANT
it SELECT ON the VIEW (For more details please refer to the BOL)
As well , you can GRANT an EXECUTE permissions to the LOGIN for STORED
PROCEDURES and not granting permissions on underlaying tables
"swas" <swas@.discussions.microsoft.com> wrote in message
news:C49B53B0-9483-4E87-9166-109ED0495E24@.microsoft.com...
> Hi. We have a SQL database for one company with multi user access. I am
> curious as to best practice solutions for expanding a db to multiple
> company
> access.
> Some parts of the db need to be shared, others are private to the company
> logged in.
> If every time a query was executed it required company filtering, it seems
> a
> waste having company parameters in every minor query, and would require
> entire code rewrite. Not being a programmer I don't know what options sql
> server may offer, but I know enough about sql to have these sort of
> questions
> distract your thinking until answered...
> I hope this is the correct forum for general questions such as this.
> Thanks in advance.
>
> swas

Multiple Columns in Parameter Field

Hi

I have a report that has a number of parameters in it that the user must select prior to running the report. One of these parameters is the site_ref that they wish to use against the dataset. All my books point to the fact that if you wish, you can select what appears in the parameter field so the user does not get confused. So rather than the user having a list of site_ref which maybe useless to them, they can have a description next to each entry, e.g.

site_Ref Estate_Name
AL Aladdin's Cave
TH Bug's Bunnies Home
XN Aliens Home

Then when the user selects what he wants from list, the actual value that is passed to the parameter is 'TH' for example. In Access I believe this was called Bound to column(?). I can find no where in the 'Report Parameters' dialog box that allows for multiple columns to be displayed. I have written the SQL to create the right data;

Select site_ref, estate_name
From dbo_src_centre_list
Order By site_ref

How can I get the parameter box in Reporting Services to allow the display of both columns but bind to the site ref when it comes to passing the info into the report. None of my books actually tell me how to do it though. I have found how to get one or the other to display and the right one to pass the actually value through, but I would like to see both in list at the same time

Regards

A report parameter valid values list contains a set of value/label pairs. When the report is run, the user sees the label. When the user selects a label, the corresponding value is used as the parameter value.

In your case, you just need to set the label of the report parameter to be bound to the Estate_Name field, while the value is bound to the Site_Ref field.

Additional information on parameters in reports can be found here: http://msdn2.microsoft.com/en-us/library/ms155917.aspx

-- Robert

|||

Thank you Robert. That is exactly what I did, what I was wondering though, is it possible to have two values showing in the parameter field e.g. AC - Aladdin's Cave and tie the value to just the AC.

I will read that link you have sent through and see what direction that pushes me in.

Regards

|||

One way of doing this is to construct the desired label directly in the query:

Select site_ref, site_ref + ' - ' + estate_name AS Label
From dbo_src_centre_list
Order By site_ref

-- Robert

|||

Thank you so much Robert, that is exactly what I wanted. Works perfectly.

Kindest Regards

Multiple columns and row values

Hi. I am trying to write a single stored procedure which would trace the
changes made by a user on a table. I would like this implemented on multiple
tables having the most efficient code possible. Is it possible to browse to
a
table and extract all its columns (column_name from information_schema) and
get the row value for these columns having only a record id. I was able to
get the column_name but unable to make a sql statement retrieving the values
of the column_name.
Any help is appreciated.Can you post more detail? What do you mean "columns having only a record
id"? If you post actual DDL, sample data, and sample output, that would be
best...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
news:871EF4F4-B4A4-493D-BBC1-6787783C1BFF@.microsoft.com...
> Hi. I am trying to write a single stored procedure which would trace the
> changes made by a user on a table. I would like this implemented on
multiple
> tables having the most efficient code possible. Is it possible to browse
to a
> table and extract all its columns (column_name from information_schema)
and
> get the row value for these columns having only a record id. I was able to
> get the column_name but unable to make a sql statement retrieving the
values
> of the column_name.
> Any help is appreciated.
>|||OK. Sorry about that.. I have TABLE1 with COL1, COL2, COL3, COL4. COL1 is in
t
and the primary key. also I have TABLE2 with COL1, COL2, COL3, etc.. with
COL1 as primary key and an int. I want to write a stored procedure to extrac
t
a record from TABLE1 or TABLE2 (table_name being passed as parameter) with a
record id (COL1) and loops over the columns and its row values. So I can use
this stored procedure in these two tables or in any other table as long as
the primary key is int. better if the int (primary key) is eliminated as a
constraint as well..
so a query from information_schema would give me the column names (given the
table as a parameter) but how do i extract the row value of those columns if
i know the record primary key value (COL1).
Thanks.
"Adam Machanic" wrote:

> Can you post more detail? What do you mean "columns having only a record
> id"? If you post actual DDL, sample data, and sample output, that would b
e
> best...
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
> news:871EF4F4-B4A4-493D-BBC1-6787783C1BFF@.microsoft.com...
> multiple
> to a
> and
> values
>
>|||"[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
news:D66A9835-AAAF-4811-8F6F-1473F095166A@.microsoft.com...
> OK. Sorry about that.. I have TABLE1 with COL1, COL2, COL3, COL4. COL1 is
int
> and the primary key. also I have TABLE2 with COL1, COL2, COL3, etc.. with
> COL1 as primary key and an int. I want to write a stored procedure to
extract
> a record from TABLE1 or TABLE2 (table_name being passed as parameter) with
a
> record id (COL1) and loops over the columns and its row values. So I can
use
> this stored procedure in these two tables or in any other table as long as
> the primary key is int. better if the int (primary key) is eliminated as a
> constraint as well..
Why do you want to do this? You're completely eliminating most of the
benefits of using stored procedures, and DBMSs in general -- keeping the
application out of the data management business! My advice to you is to
very carefully consider your motives for doing this -- I can guarantee that
you will not end up simplifying anything by tightly coupling your
application to your database (which is what this stored procedure will
accomplish). You can take that with however many grains of salt as you
choose, but you may want to search the archives of this group for lots of
threads about these kinds of techniques and the problems they invariably
cause.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||i know.. well. i have written a large application and i everything is almost
in place. I just need this user tracking history. who inserts what and who
updates which record.. and so on.. i can use triggers but that would mean
going to each one of the tables which is 100+. I want a single stored
procedure, called from a class that I can reuse on every page. So i need the
columns and the current values so i can record them in a table where it can
be retrieved in a report. but this has to go on a per column value.. and the
pages are a lot less that the tables..
"Adam Machanic" wrote:

> "[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
> news:D66A9835-AAAF-4811-8F6F-1473F095166A@.microsoft.com...
> int
> extract
> a
> use
>
> Why do you want to do this? You're completely eliminating most of the
> benefits of using stored procedures, and DBMSs in general -- keeping the
> application out of the data management business! My advice to you is to
> very carefully consider your motives for doing this -- I can guarantee tha
t
> you will not end up simplifying anything by tightly coupling your
> application to your database (which is what this stored procedure will
> accomplish). You can take that with however many grains of salt as you
> choose, but you may want to search the archives of this group for lots of
> threads about these kinds of techniques and the problems they invariably
> cause.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>

multiple charts based on employees in report

I have two charts that are subreports. I want to see if I can do one of two things.

Give the user the ability once the report processes to change the employee name and refresh the chart. Format the subreports to repeat the charts for as many employees that are in the main report. Sometimes there will be 8 other times there will be as many as 20.

Yes. For 1, you can pass the employee name through report parameter and let the user to change the parameter value. For 2, you can put the subreports into a list, and set the grouping on the list to be the employee name.

|||Could you give me a little more detail on the solution for option 2?|||Int he example of a table you place your chart in the details pane. For every employee listed (detailed grouping) or if you want to do it on a grouping level for every grouping value, none chart will be displayed on the based data for the group / detailed row.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de