Dynamic Query How To?

Hello,
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
JimCREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Hi
Yes . You need to write stored procedure that accept input parameter and
generate the out put based on the parameter passed in
this site might you to start with
http://www.sql-server-performance.c..._procedures.asp
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.c..._procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>
>

Dynamic Query How To?

Hello,
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
Jim
CREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>
|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.com/tn_stored_procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>
>
sql

Dynamic Query How To?

Hello,
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
JimCREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Hi
Yes . You need to write stored procedure that accept input parameter and
generate the out put based on the parameter passed in
this site might you to start with
http://www.sql-server-performance.com/tn_stored_procedures.asp
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.com/tn_stored_procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>> Hello,
>> I have a website that I designed with Visual Web Developer and SQL 2005
>> Express. Everything works fine, except that I had to write a seperate
>> query for each product category which is currently around 10. I expect
>> this to grow as we add new products and I can see this getting hard to
>> manage in the future.
>> Is there a way to write a dynamic query that will take a single parameter
>> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
>> but new to SQL 2005 Express.
>> Thanks
>> Jim
>>
>
>

Dynamic Images with ASPX extensions in Reports

I want to create a report that shows dynamically created images from a web
site. It looks like it will work OK, I can put the graphic in and a link
http://localhost/image.aspx?123 but the image doiesnot show in the reports.
I am guessing there is a miss match between the extension and the MIMEType
thats stuffing it up. Can anybody confirm this, before I rush into building
a HTTPRequestHandler.
Message posted via http://www.sqlmonster.com
I think you meant to post this in the reporting services newsgroup.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tom Robson via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:acbdec17de10456a86dee6a044719188@.SQLMonster.c om...
> I want to create a report that shows dynamically created images from a web
> site. It looks like it will work OK, I can put the graphic in and a link
> http://localhost/image.aspx?123 but the image doiesnot show in the
reports.
> I am guessing there is a miss match between the extension and the MIMEType
> thats stuffing it up. Can anybody confirm this, before I rush into
building
> a HTTPRequestHandler.
> --
> Message posted via http://www.sqlmonster.com
|||Tom
I am facing the same problem! Did you ever find a way to show an image
using a url such as http://localhost/image.aspx?123
Any help would be great as I am starting to tear my hair out!!!
Richard
Message posted via http://www.sqlmonster.com

Dynamic Dimension Security using something else than ROLES

We are incorporating some OLAP cubes into our secure website and what to restrict the cubes information base on the person or client logged in. We are using Dundas OLAP as the front end for displaying the cubes. The Roles approach is not a viable solution since it is impossible for us to gather and keep track of our client’s windows usernames.

Is there a way to somehow pass a value through the control or the connection string and later use this value to filter dimension members?

> Is there a way to somehow pass a value through the control or the connection string and later use this value to filter dimension members?

Yes, there is a connection string property called "Roles", where you can specify which Roles should be used.

|||

I did a little bit of digging on connection string properties and came across this in one of your blogs.

There is a new MDX function for SSAS 2005 called “CustomData”.

http://sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx

I am using the “Roles” and “CustomData” properties of the connection string to achieve my purpose.

Thanks Mosha

Dynamic Connection String Problem

I need to be able to deploy my updated website to many customers on a monthly basis and dont want to be mucking around changing the connection strings each time. Some of my web servers have multiple copies of my site and DB so each website will need a different connection string.

The simplest method I could come up with is to use the Application Name field in IIS as it doesn't get overwritten by Visual Studio when I deploy the site.

I am trying to write some code to dynamically change the connection string in the web config but cannot find any way of reading the Application Name field in IIS to use in altering the connection string. I'm using the Global.asax file to change the connection string before the DB gets called.

I had tried embedding the DB in the website folder but it would overwrite the customers database.

Hi Matthew,

The only way that comes to me is that we can write a small tool that parses the web.config file as xml, and change the connection string accordingly.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

I searched and searched and eventually found this snippet but it the last line doesn't work for me. For other people it has worked and I'm not sure what is wrong with my setup:

' Create a connectionn string element and add it to the connection strings section.Sub ChangeConnectionString(ByVal cs_nameAs String,ByVal cs_valueAs String)Try Dim webConfigAs System.Configuration.Configuration = WebConfigurationManager.OpenWebConfiguration("~")Dim dbConnStringAs ConnectionStringsSection = webConfig.ConnectionStrings'// To change existing connectionstring dbConnString.ConnectionStrings(cs_name).ConnectionString = cs_value webConfig.Save()Catch exAs ExceptionEnd Try End Sub

|||

Hi Matthew,

Could you show us the exception message?

|||

The exception is:

{System.Configuration.ConfigurationErrorsException}

An error occurred loading a configuration file: Access to the path 'C:\inetpub\wwwroot\flo_dev\6egcsvyu.tmp' is denied. (C:\inetpub\wwwroot\flo_dev\web.config)

Hope that spreads some light on what it might be.

|||

Hi Matthew,

If you're using an ASP.NET app to do this, please check if the account ASPNET do have permission to write to that file.

|||

I will check the rights of the ASPNET user. If the ASPNET user has rights to modify that file wont that allow anyone to alter the file? Or just server-side code?