Binary files in DB : set name on retrieve

Hello,

I have a table that stores binary files. When I serve them up to the user, I call the following page (serveDocument.aspx?DocumentID=xxx) which holds only the following code and pass the document ID:

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load
Dim DocumentIDAs Integer = Convert.ToInt32(Request.QueryString("DocumentID"))
'Connect to the database and bring back the image contents & MIME type for the specified picture Using myConnectionAs New SqlConnection(ConfigurationManager.ConnectionStrings("kelly_lmConnectionString1").ConnectionString)
Const SQLAs String ="SELECT [Content_Type], [Document_Data] FROM [lm_Service_Detail_Documents] WHERE [Document_id] = @.Document_id"Dim myCommandAs New SqlCommand(SQL, myConnection)
 myCommand.Parameters.AddWithValue("@.Document_id", DocumentID)
 myConnection.Open()
Dim myReaderAs SqlDataReader = myCommand.ExecuteReader
If myReader.ReadThen Response.ContentType = myReader("Content_Type").ToString()
 Response.BinaryWrite(myReader("Document_Data"))
End If myReader.Close() myConnection.Close()End Using
End Sub

It works perfectly. But heres the thing: I want users to be able to right click on the link and chose 'Save Target As'. In IE 7, this works fine. In FireFox it saves the document as 'serveDocument.aspx'. Is there any way toinjectthe filename that I want to save the document as?

Thank you.

Add this Header also

context.Response.ContentType = "application/octet-stream";
context.Response.AddHeader("Content-Disposition", "attachment; filename=\"" + filename+".txt" + "\"");

------------------

Mark as Answer if you feel

|||

What does 'context' refer to?

And, are you saying I shouldn't set the ContentType to the actual type of file?

|||

REmove the context

it uses to html encode when you are using http handler

|||

Well, that partially works. When a user clicks the linkthen saves the file, the correct name is present. But, my issue isn't that... I want the user to be able to right click the link and 'Save Link As...' with the correct filename. When I do that, it still says 'documentServe.aspx'.

|||

Hi,

But heres the thing: I want users to be able to right click on the link and chose 'Save Target As'. In IE 7, this works fine. In FireFox it saves the document as 'serveDocument.aspx'.

From your description, it seems that when you right click on the link and choose the 'save target as' in IE7, you can got the whole url with parameters, but not in FireFox, right?

If so, I'm afraid that the behavior is related to the Explorer, and based on my knowledge, there's not any workaround to fix the problem, since working mechanism for IE and FireFox is just not the same.

Thanks.

sql

Big Picture Advice please to a SQL 2000 intermediate/beginner user

I am migrating a Mid-Frame DB to SQL Server 2000 PC.
I will call oldDB, OldTables the ones that belong to the Mid-Frame.
I will call newDB, newTables the ones that belong to the PC.
The oldDB has many versions and I have retrieved the oldTables to CSV
Files and then I use DTS to make a oldTables as tempTables in SQL 2000
PC.
I need to transform data and process the columns in tempTables to
create the NewTables (ie Final Tables).
I have used SQL scripts with UDFs to accomplish one run for one
version of OldDB to newDB.
The question is : Should I use ADO.NET or embedded SQL to process the
TemPTables
And CSV Files or Should stick with SQL SELECT, INSERT, UPDATE scripts
to do this ?
I have used SQL scripts with a dozen UDFs, is it time to start using
other tools /objects like SPs ?
GigaThanksInAdvanceGiga,
IMHO, if it works for you the way you're doing it, then I reckon stick with
it until you're comfortable moving to other features of SQL like stored
procs.
ADO.NET or SQL to me makes no diff if the end result is the same. You might
find things process quicker with native SQL commands. And there's not much
difference between scripts which run your UDFs or SPs to run them. Scripts
you run in QA will give you more of a visual on what's going on.
cheers
Danny
<placidite1@.yahoo.com> wrote in message
news:1179264486.895840.101790@.n59g2000hsh.googlegroups.com...
>I am migrating a Mid-Frame DB to SQL Server 2000 PC.
> I will call oldDB, OldTables the ones that belong to the Mid-Frame.
> I will call newDB, newTables the ones that belong to the PC.
> The oldDB has many versions and I have retrieved the oldTables to CSV
> Files and then I use DTS to make a oldTables as tempTables in SQL 2000
> PC.
> I need to transform data and process the columns in tempTables to
> create the NewTables (ie Final Tables).
> I have used SQL scripts with UDFs to accomplish one run for one
> version of OldDB to newDB.
> The question is : Should I use ADO.NET or embedded SQL to process the
> TemPTables
> And CSV Files or Should stick with SQL SELECT, INSERT, UPDATE scripts
> to do this ?
> I have used SQL scripts with a dozen UDFs, is it time to start using
> other tools /objects like SPs ?
>
> GigaThanksInAdvance
>

Big Picture Advice please to a SQL 2000 intermediate/beginner user

I am migrating a Mid-Frame DB to SQL Server 2000 PC.
I will call oldDB, OldTables the ones that belong to the Mid-Frame.
I will call newDB, newTables the ones that belong to the PC.
The oldDB has many versions and I have retrieved the oldTables to CSV
Files and then I use DTS to make a oldTables as tempTables in SQL 2000
PC.
I need to transform data and process the columns in tempTables to
create the NewTables (ie Final Tables).
I have used SQL scripts with UDFs to accomplish one run for one
version of OldDB to newDB.
The question is : Should I use ADO.NET or embedded SQL to process the
TemPTables
And CSV Files or Should stick with SQL SELECT, INSERT, UPDATE scripts
to do this ?
I have used SQL scripts with a dozen UDFs, is it time to start using
other tools /objects like SPs ?
GigaThanksInAdvanceGiga,
IMHO, if it works for you the way you're doing it, then I reckon stick with
it until you're comfortable moving to other features of SQL like stored
procs.
ADO.NET or SQL to me makes no diff if the end result is the same. You might
find things process quicker with native SQL commands. And there's not much
difference between scripts which run your UDFs or SPs to run them. Scripts
you run in QA will give you more of a visual on what's going on.
cheers
Danny
<placidite1@.yahoo.com> wrote in message
news:1179264486.895840.101790@.n59g2000hsh.googlegroups.com...
>I am migrating a Mid-Frame DB to SQL Server 2000 PC.
> I will call oldDB, OldTables the ones that belong to the Mid-Frame.
> I will call newDB, newTables the ones that belong to the PC.
> The oldDB has many versions and I have retrieved the oldTables to CSV
> Files and then I use DTS to make a oldTables as tempTables in SQL 2000
> PC.
> I need to transform data and process the columns in tempTables to
> create the NewTables (ie Final Tables).
> I have used SQL scripts with UDFs to accomplish one run for one
> version of OldDB to newDB.
> The question is : Should I use ADO.NET or embedded SQL to process the
> TemPTables
> And CSV Files or Should stick with SQL SELECT, INSERT, UPDATE scripts
> to do this ?
> I have used SQL scripts with a dozen UDFs, is it time to start using
> other tools /objects like SPs ?
>
> GigaThanksInAdvance
>

bidirectional snapshot replication?

I currently have a list of users in a SQL 2005 database. Users are
authenticated based on their email address, and each user has a number
of areas they do and don't have access to. A website is used to add
users to the database, or to add area permissions for an exisiting
user.
I would like to set up a number of replicated servers around the world.
At least one would have very high latency and very low bandwidth to it.
All Sites are connected by VPN.
My question is: Is there a way to do bidirectional replication between
all of the servers? All servers would ideally be both publishers and
subscribers.. If so, which type of replication would you use? I was
thinking snapshot replication twice a day, but that may not make
sense..
Thanks in advance..
Have a look at peer-to-peer transactional replication - this sounds like
what you require.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

BI tools from MS?

sorry for the basic questoin yet again but I'm new to this...

Could you please tell me what are the end user BI tools from MS that could be used to browse SSAS cube? That understand metadata etc?

Like the browser that is build in the SQL Management studio is great - I though that OWC control would provide similar inteface but instead it looks just like Pivot Table - it doesn't seem to understand metadata & so doesn't apply proper formatting to mesures, nor allows to browse the cube the way I want (group measures, dimension members etc)

Thanks!

Excel 2007 have full support for all features in SSAS2005.

ProClarity Analytics 6.3 (Current relase is 6.2) includes a windows client(Professional), a web client(Analytics Server) and a Dashboard. Microsoft bought ProClarity last year in may.

This year MS will release a new product for financial planning, Dashboard/Balance Scorecard and Analytics called Performance point. It is planned to be released the second half of this year.

HTH

Thomas Ivarsson

|||

cool - thanks for your help. unfortunately these tools aren't readily available...

so just the last question (and also to clarify my original question) - SQL Server Management studio does use OWC 1.1 for the cube browser, but it also uses WindowsForms10.SysTreeView32 control for the browser on the left hand side that does display metadata, groups properly etc.

Is it just a regular tree view control & MS has written custom logic to populate it based on the cube's metadata or that countrol could be somehow exposed & reused easily?

how do I even read that info from SSAS cube?

|||

Hello. I am not a .Net specialist so my answere is very general.

I think that you use the new object model AMO to retrieve meta data information from SSAS2005. If you install the samples that is an optional part of the SQL Server 2005 installation you will get different code samples and projects.

Books On Line also have information for developers. There is a new CTP 3 SP2 version at www.microsoft.com/sql

Excel 2003 also support SSAS2005 but you will have to install OLEDB for OLAP version 9. You can download it with an add-in for Excel 2003 here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

Edit: This add-in will show the SSAS2005 metadata.

Kind regards

Thomas Ivarsson

|||

unfortunately these tools aren't readily available

Excel 2007 is available for couple of months already...

|||Great info - thanks!

BI Data Presentation

Hi,

I'am very new to Datawarehousing concepts of SQL2005, my current doubt is how to present the data to the end user from the cube i generated in Visual studio2005?

i had worked in cognos before and there we had a viewer file which was having the extension as .ppr and can be called from application which is connected to the Cube, so here i would like to know what is the next step after cube so that i can present the data...

any help or links will be greatfull

Mat

Hi,

There are various posting in the forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1218497&SiteID=1

ProClarity

Office 2007

Performance Point

Panorama NovaView

Probably many more

Hope that helps

Matt

BETWEEN predicate with passed parameters

I have a report in SQL that passes parameters at runtime entered by the user for two date ranges (beginning and ending). I'm trying to write a formula that will print a specific field *only if* the specified date range entered by the user is BETWEEN a specific value (like 200401). This is kind of reverse of a normal WHERE, BETWEEN clause.

I tried a standard BETWEEN predicate in my WHERE clause like:
IF '200401' BETWEEN ?BegPer and ?EndPer then salesanal.ptdbud01 else 0

But, it's returning an error that my Then statement is missing. I can't use a normal statement like 'IF ?BegPer >= '200401' and ?EndPer <= '200401', then....' because users could enter a RANGE of periods, so it would be difficult to code all of the possible combinations this way. I'm actually doing this in Crystal, but if someone can give me a standard MSSQL example, I can translate that over to Crystal.

Thanks in advance,
MichelleUSE Northwind
GO

SELECT *
FROM Orders
WHERE OrderDate >= '1996-07-08 00:00:00.000'
AND ShippedDate <= '1996-07-15 23:59:59.000'
GO|||Yeah, I can't just use the standard WHERE clause with >= and <=. I need to return a separate value for each month of the calendar year (these are setup in separate fields on my report). So, if the user enters parameter values into begper = '200401' and endper = '200403', then the report needs to print all 3 fields for months 01, 02, and 03, because they all fall within the range of 200401 and 200403. I would have to code all possible combos of a begper >= 'xx' and endper <= 'yy', ya know? While that may be entertaining, I wonder if there's a more efficient route? <g>

More details:
Table:
Custid, Janbudget, Febbudget, Marbudget, Aprbudget, etc etc.
User enters runtime parameters for a monthly period range, such as between '200401' (January) and '200403' (March). In this case, my report should only print the values in fields Janbudget, Febbudget and Marbudget. All other fields will either not print or print 0.

Any other ideas?

Thanks!
Michelle :)

Originally posted by Brett Kaiser

USE Northwind
GO

SELECT *
FROM Orders
WHERE OrderDate >= '1996-07-08 00:00:00.000'
AND ShippedDate <= '1996-07-15 23:59:59.000'
GO|||You need to use Group BY and use scalar functions...hold on...|||Like this?

SELECT OrderId
, DATEPART(yy,OrderDate) AS OrderDate_yy
, DATEPART(mm,OrderDate) AS OrderDate_mm
, DATEPART(yy,ShippedDate) AS ShippedDate_yy
, DATEPART(mm,ShippedDate) AS ShippedDate_mm
, COUNT(*) AS Orders
FROM Orders
WHERE OrderDate >= '1996-07-08 00:00:00.000'
AND ShippedDate <= '1996-07-15 23:59:59.000'
GROUP BY OrderId
, DATEPART(yy,OrderDate)
, DATEPART(mm,OrderDate)
, DATEPART(yy,ShippedDate)
, DATEPART(mm,ShippedDate)

Best way to search for all records (Using a Case Statement in a SP)

I have a form with a dropdown or combo box, the user can select <All>
or pick a user name. If they pick a user name my where clause works
fine, buts what's the best way to write "Select All" if they choose
the <All
This is what I have so far, but I don't think I should be using the
LIKE operator.

WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
@.myqarep END
and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
@.myoffice END

thanks for your help!!On 13 Sep 2004 10:47:21 -0700, PaulMac wrote:

>I have a form with a dropdown or combo box, the user can select <All>
>or pick a user name. If they pick a user name my where clause works
>fine, buts what's the best way to write "Select All" if they choose
>the <All>
>This is what I have so far, but I don't think I should be using the
>LIKE operator.
>WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
>@.myqarep END
> and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> @.myoffice END
>thanks for your help!!

Hi Paul,

You could use this:

WHERE tblCase.qarep = CASE @.myqrep
WHEN '<All>' THEN tblCase.qarep
ELSE @.myqrep
END
AND tblOffice.officecode = CASE @.myoffice
WHEN -1 THEN tblOffice.officecode
ELSE @.myoffice
END

An alternative that's a bit more work to create but that will probably
perform better is to write different versions of the query; use IF to
select which search arguments are set to All and which are set to a value
and then execute the correct version of the query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||PaulMac (paulmac106@.hotmail.com) writes:
> I have a form with a dropdown or combo box, the user can select <All>
> or pick a user name. If they pick a user name my where clause works
> fine, buts what's the best way to write "Select All" if they choose
> the <All>
> This is what I have so far, but I don't think I should be using the
> LIKE operator.
> WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
> @.myqarep END
> and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> @.myoffice END

Since it's a combo, LIKE appears to be a bit of overkill. The normal
procedure is to pass NULL when you want all:

WHERE (qarep = @.myqarep OR @.myqarep IS NULL)
AND (officecode = @.myoffice OR @.myoffice IS NULL)

But of course this works too:

WHERE (qarep = @.myqarep OR @.myqarep = '<All>')
AND (officecode = @.myoffice OR @.myoffice = -1)

At least as long as you don't localize the string...

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I will not suggest putting a CASE command in the WHERE clause, you may
just want to use "if" control statement to separate two code blocks
which will be more efficient at database level.

IF Boolean_expression
{ sql_statement | statement_block }
[
ELSE
{ sql_statement | statement_block } ]

paulmac106@.hotmail.com (PaulMac) wrote in message news:<1ee8a467.0409130947.4f608eba@.posting.google.com>...
> I have a form with a dropdown or combo box, the user can select <All>
> or pick a user name. If they pick a user name my where clause works
> fine, buts what's the best way to write "Select All" if they choose
> the <All>
> This is what I have so far, but I don't think I should be using the
> LIKE operator.
> WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
> @.myqarep END
> and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> @.myoffice END
> thanks for your help!!|||Hi Hugo

That worked perfectly...Thank You!!

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<3e6ck0dsnb7m8pvq95ps7ceu2nu0lj353t@.4ax.com>...
> On 13 Sep 2004 10:47:21 -0700, PaulMac wrote:
> >I have a form with a dropdown or combo box, the user can select <All>
> >or pick a user name. If they pick a user name my where clause works
> >fine, buts what's the best way to write "Select All" if they choose
> >the <All>
> >This is what I have so far, but I don't think I should be using the
> >LIKE operator.
> >WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
> >@.myqarep END
> > and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> > @.myoffice END
> >thanks for your help!!
> Hi Paul,
> You could use this:
> WHERE tblCase.qarep = CASE @.myqrep
> WHEN '<All>' THEN tblCase.qarep
> ELSE @.myqrep
> END
> AND tblOffice.officecode = CASE @.myoffice
> WHEN -1 THEN tblOffice.officecode
> ELSE @.myoffice
> END
> An alternative that's a bit more work to create but that will probably
> perform better is to write different versions of the query; use IF to
> select which search arguments are set to All and which are set to a value
> and then execute the correct version of the query.
> Best, Hugo

best way to scrub data

We are not allowed to restore databases to the test environments that have
sensitive data like user info ,credit card info,etc..
Whats the best way to scrub this data that can be automated as opposed to
deleting these values ? Just want to hash them ..Hassan,
I've seen a post lately from Martin Bell on the topic you need pointing to
this nice article:
http://www.windowsitpro.com/Article...876/25876.html.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender. This message does not imply endorsement from Solid Quality Learning,
and it does not represent the point of view of Solid Quality Learning or any
other person, company or institution mentioned in this message
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OcpN4QbGGHA.208@.tk2msftngp13.phx.gbl...
> We are not allowed to restore databases to the test environments that have
> sensitive data like user info ,credit card info,etc..
> Whats the best way to scrub this data that can be automated as opposed to
> deleting these values ? Just want to hash them ..
>

best way to order results sequentially starting from somewhere in the middle

I'm working with SQL Server 2005, and I'm trying to sort the results based on a user selected letter. Say the user selects 'D' to filter his results. I'd like to return the results starting from D followed by E, F, G...Z, A, B, C. What I'm getting is the results for the D entries at the top of the result set, followed by A, B, C, E...Z.

A solution comes to mind that would be very long and db intensive, by querying on 'like 'D', followed by like 'E', followed by like 'F', etc, but I'm sure that there is a much more efficient way to do this. Below is the code that I'm using now.

' where @.SortString = 'd' and @.Test is a temp Table

BEGIN

Insert into @.Test

Select CompanyName,ContactId, CompanyId

from vContacts where CompanyName like @.SortString +'%'

Order by CompanyName

Insert into @.Test

Select CompanyName,ContactId, CompanyId

from vContacts where CompanyName not like @.SortString +'%'

Order by CompanyName

END

Thanks in advance for your help

Code Snippet

SELECT
OrderSet = 1,
CompanyName,
ContactId,
CompanyId
FROM vContacts
WHERE CompanyName >= @.SortString

UNION

Select
2
CompanyName,
ContactId,
CompanyId
FROM vContacts
WHERE CompanyName < @.SortString

ORDER BY
OrderSet,

CompanyName

|||Thank you Arnie. Right on with the answer

Best way to make a report multi-cultural

We wanted to add a language parameter to a report and allow the user to
output the report to multiple languages. We did this using three
techniques:
Static resource strings:
We created a custom assembly with resource files. We have one property and
one method. Each report has a textbox as it's first item that passes the
report parameter and sets the culture property. Each textbox then uses the
method and passes in a stringID. We configured the component to use an
instance in the report. I believe this will hurt scalability, to what
degree, uncertain, and will need to test. Looking into ways to set a
property on the report and use that instead so that the component method
could be static and shared across all report instances.
Strings from queries:
We used a Translation web service found on CodeProject to access BabelFish
and perform translations. We plan on using dual columns in the db, but for
current proof of concept this seemed like an easy approach. We may plan on
using this in the future for text fields input from the user where they do
not want to translate. We are not sure on this item yet. Either we add a
drop down and they can enter the text in multiple languages, we translate at
time of data input, we translate at time of report generation.
Strings from queries (incomplete):
We need to modify the source data to accomodate multiple languages, and add
support to our stored procedures for this. This will eliminate or greatly
reduce the dependency on BabelFish which will speed up the report generation
and scale.
I'd appreciate feedback, or what others have done to accomplish the same
feature.Rather than use a hidden textbox to set the property, used the following:
<Code>Protected Overrides Sub OnInit()
cust.SetLanguage(Report.Parameters!Language.Value)
End Sub</Code>
"Steve Munson" <smunson@.clearwire.net> wrote in message
news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> We wanted to add a language parameter to a report and allow the user to
> output the report to multiple languages. We did this using three
> techniques:
> Static resource strings:
> We created a custom assembly with resource files. We have one property
> and one method. Each report has a textbox as it's first item that passes
> the report parameter and sets the culture property. Each textbox then
> uses the method and passes in a stringID. We configured the component to
> use an instance in the report. I believe this will hurt scalability, to
> what degree, uncertain, and will need to test. Looking into ways to set a
> property on the report and use that instead so that the component method
> could be static and shared across all report instances.
> Strings from queries:
> We used a Translation web service found on CodeProject to access BabelFish
> and perform translations. We plan on using dual columns in the db, but
> for current proof of concept this seemed like an easy approach. We may
> plan on using this in the future for text fields input from the user where
> they do not want to translate. We are not sure on this item yet. Either
> we add a drop down and they can enter the text in multiple languages, we
> translate at time of data input, we translate at time of report
> generation.
> Strings from queries (incomplete):
> We need to modify the source data to accomodate multiple languages, and
> add support to our stored procedures for this. This will eliminate or
> greatly reduce the dependency on BabelFish which will speed up the report
> generation and scale.
> I'd appreciate feedback, or what others have done to accomplish the same
> feature.
>|||I am not understanding how you did this exactly .. I am trying to get this
going too. I added custom code which is of course NOT the way to go. I am
trying to change over to a class. I am not following this that you wrote. I
have never set up resource files and I am very new to that. I have written
custom code for MRS and used that. But I need something that can be shared
across all report instances as you say and the method you suggest will work.
I just need the steps to create the resource files and class. Thanks.
"Steve Munson" wrote:
> Rather than use a hidden textbox to set the property, used the following:
> <Code>Protected Overrides Sub OnInit()
> cust.SetLanguage(Report.Parameters!Language.Value)
> End Sub</Code>
>
> "Steve Munson" <smunson@.clearwire.net> wrote in message
> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> > We wanted to add a language parameter to a report and allow the user to
> > output the report to multiple languages. We did this using three
> > techniques:
> >
> > Static resource strings:
> > We created a custom assembly with resource files. We have one property
> > and one method. Each report has a textbox as it's first item that passes
> > the report parameter and sets the culture property. Each textbox then
> > uses the method and passes in a stringID. We configured the component to
> > use an instance in the report. I believe this will hurt scalability, to
> > what degree, uncertain, and will need to test. Looking into ways to set a
> > property on the report and use that instead so that the component method
> > could be static and shared across all report instances.
> >
> > Strings from queries:
> > We used a Translation web service found on CodeProject to access BabelFish
> > and perform translations. We plan on using dual columns in the db, but
> > for current proof of concept this seemed like an easy approach. We may
> > plan on using this in the future for text fields input from the user where
> > they do not want to translate. We are not sure on this item yet. Either
> > we add a drop down and they can enter the text in multiple languages, we
> > translate at time of data input, we translate at time of report
> > generation.
> >
> > Strings from queries (incomplete):
> > We need to modify the source data to accomodate multiple languages, and
> > add support to our stored procedures for this. This will eliminate or
> > greatly reduce the dependency on BabelFish which will speed up the report
> > generation and scale.
> >
> > I'd appreciate feedback, or what others have done to accomplish the same
> > feature.
> >
>
>|||I have a set of power point slides and a sample project. In the sample I
add Spanish to one of the RS sample reports.
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
>I am not understanding how you did this exactly .. I am trying to get this
> going too. I added custom code which is of course NOT the way to go. I
> am
> trying to change over to a class. I am not following this that you wrote.
> I
> have never set up resource files and I am very new to that. I have
> written
> custom code for MRS and used that. But I need something that can be
> shared
> across all report instances as you say and the method you suggest will
> work.
> I just need the steps to create the resource files and class. Thanks.
> "Steve Munson" wrote:
>> Rather than use a hidden textbox to set the property, used the following:
>> <Code>Protected Overrides Sub OnInit()
>> cust.SetLanguage(Report.Parameters!Language.Value)
>> End Sub</Code>
>>
>> "Steve Munson" <smunson@.clearwire.net> wrote in message
>> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> > We wanted to add a language parameter to a report and allow the user to
>> > output the report to multiple languages. We did this using three
>> > techniques:
>> >
>> > Static resource strings:
>> > We created a custom assembly with resource files. We have one property
>> > and one method. Each report has a textbox as it's first item that
>> > passes
>> > the report parameter and sets the culture property. Each textbox then
>> > uses the method and passes in a stringID. We configured the component
>> > to
>> > use an instance in the report. I believe this will hurt scalability,
>> > to
>> > what degree, uncertain, and will need to test. Looking into ways to
>> > set a
>> > property on the report and use that instead so that the component
>> > method
>> > could be static and shared across all report instances.
>> >
>> > Strings from queries:
>> > We used a Translation web service found on CodeProject to access
>> > BabelFish
>> > and perform translations. We plan on using dual columns in the db, but
>> > for current proof of concept this seemed like an easy approach. We may
>> > plan on using this in the future for text fields input from the user
>> > where
>> > they do not want to translate. We are not sure on this item yet.
>> > Either
>> > we add a drop down and they can enter the text in multiple languages,
>> > we
>> > translate at time of data input, we translate at time of report
>> > generation.
>> >
>> > Strings from queries (incomplete):
>> > We need to modify the source data to accomodate multiple languages, and
>> > add support to our stored procedures for this. This will eliminate or
>> > greatly reduce the dependency on BabelFish which will speed up the
>> > report
>> > generation and scale.
>> >
>> > I'd appreciate feedback, or what others have done to accomplish the
>> > same
>> > feature.
>> >
>>|||The attachments for source code are too large (200 kb), I'll try and figure
out where I can post it.
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
>I am not understanding how you did this exactly .. I am trying to get this
> going too. I added custom code which is of course NOT the way to go. I
> am
> trying to change over to a class. I am not following this that you wrote.
> I
> have never set up resource files and I am very new to that. I have
> written
> custom code for MRS and used that. But I need something that can be
> shared
> across all report instances as you say and the method you suggest will
> work.
> I just need the steps to create the resource files and class. Thanks.
> "Steve Munson" wrote:
>> Rather than use a hidden textbox to set the property, used the following:
>> <Code>Protected Overrides Sub OnInit()
>> cust.SetLanguage(Report.Parameters!Language.Value)
>> End Sub</Code>
>>
>> "Steve Munson" <smunson@.clearwire.net> wrote in message
>> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> > We wanted to add a language parameter to a report and allow the user to
>> > output the report to multiple languages. We did this using three
>> > techniques:
>> >
>> > Static resource strings:
>> > We created a custom assembly with resource files. We have one property
>> > and one method. Each report has a textbox as it's first item that
>> > passes
>> > the report parameter and sets the culture property. Each textbox then
>> > uses the method and passes in a stringID. We configured the component
>> > to
>> > use an instance in the report. I believe this will hurt scalability,
>> > to
>> > what degree, uncertain, and will need to test. Looking into ways to
>> > set a
>> > property on the report and use that instead so that the component
>> > method
>> > could be static and shared across all report instances.
>> >
>> > Strings from queries:
>> > We used a Translation web service found on CodeProject to access
>> > BabelFish
>> > and perform translations. We plan on using dual columns in the db, but
>> > for current proof of concept this seemed like an easy approach. We may
>> > plan on using this in the future for text fields input from the user
>> > where
>> > they do not want to translate. We are not sure on this item yet.
>> > Either
>> > we add a drop down and they can enter the text in multiple languages,
>> > we
>> > translate at time of data input, we translate at time of report
>> > generation.
>> >
>> > Strings from queries (incomplete):
>> > We need to modify the source data to accomodate multiple languages, and
>> > add support to our stored procedures for this. This will eliminate or
>> > greatly reduce the dependency on BabelFish which will speed up the
>> > report
>> > generation and scale.
>> >
>> > I'd appreciate feedback, or what others have done to accomplish the
>> > same
>> > feature.
>> >
>>|||If you are able to post the example you have ... maybe is someone's blog ...
then please post the location. Thanks.
"Steve MunLeeuw" wrote:
> The attachments for source code are too large (200 kb), I'll try and figure
> out where I can post it.
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
> >I am not understanding how you did this exactly .. I am trying to get this
> > going too. I added custom code which is of course NOT the way to go. I
> > am
> > trying to change over to a class. I am not following this that you wrote.
> > I
> > have never set up resource files and I am very new to that. I have
> > written
> > custom code for MRS and used that. But I need something that can be
> > shared
> > across all report instances as you say and the method you suggest will
> > work.
> > I just need the steps to create the resource files and class. Thanks.
> >
> > "Steve Munson" wrote:
> >
> >> Rather than use a hidden textbox to set the property, used the following:
> >> <Code>Protected Overrides Sub OnInit()
> >>
> >> cust.SetLanguage(Report.Parameters!Language.Value)
> >>
> >> End Sub</Code>
> >>
> >>
> >> "Steve Munson" <smunson@.clearwire.net> wrote in message
> >> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> >> > We wanted to add a language parameter to a report and allow the user to
> >> > output the report to multiple languages. We did this using three
> >> > techniques:
> >> >
> >> > Static resource strings:
> >> > We created a custom assembly with resource files. We have one property
> >> > and one method. Each report has a textbox as it's first item that
> >> > passes
> >> > the report parameter and sets the culture property. Each textbox then
> >> > uses the method and passes in a stringID. We configured the component
> >> > to
> >> > use an instance in the report. I believe this will hurt scalability,
> >> > to
> >> > what degree, uncertain, and will need to test. Looking into ways to
> >> > set a
> >> > property on the report and use that instead so that the component
> >> > method
> >> > could be static and shared across all report instances.
> >> >
> >> > Strings from queries:
> >> > We used a Translation web service found on CodeProject to access
> >> > BabelFish
> >> > and perform translations. We plan on using dual columns in the db, but
> >> > for current proof of concept this seemed like an easy approach. We may
> >> > plan on using this in the future for text fields input from the user
> >> > where
> >> > they do not want to translate. We are not sure on this item yet.
> >> > Either
> >> > we add a drop down and they can enter the text in multiple languages,
> >> > we
> >> > translate at time of data input, we translate at time of report
> >> > generation.
> >> >
> >> > Strings from queries (incomplete):
> >> > We need to modify the source data to accomodate multiple languages, and
> >> > add support to our stored procedures for this. This will eliminate or
> >> > greatly reduce the dependency on BabelFish which will speed up the
> >> > report
> >> > generation and scale.
> >> >
> >> > I'd appreciate feedback, or what others have done to accomplish the
> >> > same
> >> > feature.
> >> >
> >>
> >>
> >>
>
>|||Sorry for the delay, and thanks for the info on the other post with respect
to the custom assembly, preview tab.
http://smunson.jot.com/WikiHome/Reporting+Services
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:0E5DE435-1C2A-4AC8-88A7-6EB22EC20D2C@.microsoft.com...
> If you are able to post the example you have ... maybe is someone's blog
> ...
> then please post the location. Thanks.
> "Steve MunLeeuw" wrote:
>> The attachments for source code are too large (200 kb), I'll try and
>> figure
>> out where I can post it.
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
>> >I am not understanding how you did this exactly .. I am trying to get
>> >this
>> > going too. I added custom code which is of course NOT the way to go.
>> > I
>> > am
>> > trying to change over to a class. I am not following this that you
>> > wrote.
>> > I
>> > have never set up resource files and I am very new to that. I have
>> > written
>> > custom code for MRS and used that. But I need something that can be
>> > shared
>> > across all report instances as you say and the method you suggest will
>> > work.
>> > I just need the steps to create the resource files and class. Thanks.
>> >
>> > "Steve Munson" wrote:
>> >
>> >> Rather than use a hidden textbox to set the property, used the
>> >> following:
>> >> <Code>Protected Overrides Sub OnInit()
>> >>
>> >> cust.SetLanguage(Report.Parameters!Language.Value)
>> >>
>> >> End Sub</Code>
>> >>
>> >>
>> >> "Steve Munson" <smunson@.clearwire.net> wrote in message
>> >> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> >> > We wanted to add a language parameter to a report and allow the user
>> >> > to
>> >> > output the report to multiple languages. We did this using three
>> >> > techniques:
>> >> >
>> >> > Static resource strings:
>> >> > We created a custom assembly with resource files. We have one
>> >> > property
>> >> > and one method. Each report has a textbox as it's first item that
>> >> > passes
>> >> > the report parameter and sets the culture property. Each textbox
>> >> > then
>> >> > uses the method and passes in a stringID. We configured the
>> >> > component
>> >> > to
>> >> > use an instance in the report. I believe this will hurt
>> >> > scalability,
>> >> > to
>> >> > what degree, uncertain, and will need to test. Looking into ways to
>> >> > set a
>> >> > property on the report and use that instead so that the component
>> >> > method
>> >> > could be static and shared across all report instances.
>> >> >
>> >> > Strings from queries:
>> >> > We used a Translation web service found on CodeProject to access
>> >> > BabelFish
>> >> > and perform translations. We plan on using dual columns in the db,
>> >> > but
>> >> > for current proof of concept this seemed like an easy approach. We
>> >> > may
>> >> > plan on using this in the future for text fields input from the user
>> >> > where
>> >> > they do not want to translate. We are not sure on this item yet.
>> >> > Either
>> >> > we add a drop down and they can enter the text in multiple
>> >> > languages,
>> >> > we
>> >> > translate at time of data input, we translate at time of report
>> >> > generation.
>> >> >
>> >> > Strings from queries (incomplete):
>> >> > We need to modify the source data to accomodate multiple languages,
>> >> > and
>> >> > add support to our stored procedures for this. This will eliminate
>> >> > or
>> >> > greatly reduce the dependency on BabelFish which will speed up the
>> >> > report
>> >> > generation and scale.
>> >> >
>> >> > I'd appreciate feedback, or what others have done to accomplish the
>> >> > same
>> >> > feature.
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks Steve for the link, however I tried to access it and get an error
Insufficient Privileges.
"Steve MunLeeuw" wrote:
> Sorry for the delay, and thanks for the info on the other post with respect
> to the custom assembly, preview tab.
> http://smunson.jot.com/WikiHome/Reporting+Services
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:0E5DE435-1C2A-4AC8-88A7-6EB22EC20D2C@.microsoft.com...
> > If you are able to post the example you have ... maybe is someone's blog
> > ...
> > then please post the location. Thanks.
> >
> > "Steve MunLeeuw" wrote:
> >
> >> The attachments for source code are too large (200 kb), I'll try and
> >> figure
> >> out where I can post it.
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
> >> >I am not understanding how you did this exactly .. I am trying to get
> >> >this
> >> > going too. I added custom code which is of course NOT the way to go.
> >> > I
> >> > am
> >> > trying to change over to a class. I am not following this that you
> >> > wrote.
> >> > I
> >> > have never set up resource files and I am very new to that. I have
> >> > written
> >> > custom code for MRS and used that. But I need something that can be
> >> > shared
> >> > across all report instances as you say and the method you suggest will
> >> > work.
> >> > I just need the steps to create the resource files and class. Thanks.
> >> >
> >> > "Steve Munson" wrote:
> >> >
> >> >> Rather than use a hidden textbox to set the property, used the
> >> >> following:
> >> >> <Code>Protected Overrides Sub OnInit()
> >> >>
> >> >> cust.SetLanguage(Report.Parameters!Language.Value)
> >> >>
> >> >> End Sub</Code>
> >> >>
> >> >>
> >> >> "Steve Munson" <smunson@.clearwire.net> wrote in message
> >> >> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> >> >> > We wanted to add a language parameter to a report and allow the user
> >> >> > to
> >> >> > output the report to multiple languages. We did this using three
> >> >> > techniques:
> >> >> >
> >> >> > Static resource strings:
> >> >> > We created a custom assembly with resource files. We have one
> >> >> > property
> >> >> > and one method. Each report has a textbox as it's first item that
> >> >> > passes
> >> >> > the report parameter and sets the culture property. Each textbox
> >> >> > then
> >> >> > uses the method and passes in a stringID. We configured the
> >> >> > component
> >> >> > to
> >> >> > use an instance in the report. I believe this will hurt
> >> >> > scalability,
> >> >> > to
> >> >> > what degree, uncertain, and will need to test. Looking into ways to
> >> >> > set a
> >> >> > property on the report and use that instead so that the component
> >> >> > method
> >> >> > could be static and shared across all report instances.
> >> >> >
> >> >> > Strings from queries:
> >> >> > We used a Translation web service found on CodeProject to access
> >> >> > BabelFish
> >> >> > and perform translations. We plan on using dual columns in the db,
> >> >> > but
> >> >> > for current proof of concept this seemed like an easy approach. We
> >> >> > may
> >> >> > plan on using this in the future for text fields input from the user
> >> >> > where
> >> >> > they do not want to translate. We are not sure on this item yet.
> >> >> > Either
> >> >> > we add a drop down and they can enter the text in multiple
> >> >> > languages,
> >> >> > we
> >> >> > translate at time of data input, we translate at time of report
> >> >> > generation.
> >> >> >
> >> >> > Strings from queries (incomplete):
> >> >> > We need to modify the source data to accomodate multiple languages,
> >> >> > and
> >> >> > add support to our stored procedures for this. This will eliminate
> >> >> > or
> >> >> > greatly reduce the dependency on BabelFish which will speed up the
> >> >> > report
> >> >> > generation and scale.
> >> >> >
> >> >> > I'd appreciate feedback, or what others have done to accomplish the
> >> >> > same
> >> >> > feature.
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Can you try the following link, and if that doesn't work I can email them.
http://smunson.jot.com/Reporting+Services
The two files are at the bottom.
SSRS 05 multicultural parameterized reports.ppt
SSRS 05 multicultural parameterized reports.zip
Any feedback on the code or ppt is appreciated.
Steve MunLeeuw
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:C77162ED-AD12-4956-B458-31DA2EFA25B1@.microsoft.com...
> Thanks Steve for the link, however I tried to access it and get an error
> Insufficient Privileges.
>
> "Steve MunLeeuw" wrote:
>> Sorry for the delay, and thanks for the info on the other post with
>> respect
>> to the custom assembly, preview tab.
>> http://smunson.jot.com/WikiHome/Reporting+Services
>>
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:0E5DE435-1C2A-4AC8-88A7-6EB22EC20D2C@.microsoft.com...
>> > If you are able to post the example you have ... maybe is someone's
>> > blog
>> > ...
>> > then please post the location. Thanks.
>> >
>> > "Steve MunLeeuw" wrote:
>> >
>> >> The attachments for source code are too large (200 kb), I'll try and
>> >> figure
>> >> out where I can post it.
>> >>
>> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> >> news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
>> >> >I am not understanding how you did this exactly .. I am trying to get
>> >> >this
>> >> > going too. I added custom code which is of course NOT the way to
>> >> > go.
>> >> > I
>> >> > am
>> >> > trying to change over to a class. I am not following this that you
>> >> > wrote.
>> >> > I
>> >> > have never set up resource files and I am very new to that. I
>> >> > have
>> >> > written
>> >> > custom code for MRS and used that. But I need something that can be
>> >> > shared
>> >> > across all report instances as you say and the method you suggest
>> >> > will
>> >> > work.
>> >> > I just need the steps to create the resource files and class.
>> >> > Thanks.
>> >> >
>> >> > "Steve Munson" wrote:
>> >> >
>> >> >> Rather than use a hidden textbox to set the property, used the
>> >> >> following:
>> >> >> <Code>Protected Overrides Sub OnInit()
>> >> >>
>> >> >> cust.SetLanguage(Report.Parameters!Language.Value)
>> >> >>
>> >> >> End Sub</Code>
>> >> >>
>> >> >>
>> >> >> "Steve Munson" <smunson@.clearwire.net> wrote in message
>> >> >> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> >> >> > We wanted to add a language parameter to a report and allow the
>> >> >> > user
>> >> >> > to
>> >> >> > output the report to multiple languages. We did this using three
>> >> >> > techniques:
>> >> >> >
>> >> >> > Static resource strings:
>> >> >> > We created a custom assembly with resource files. We have one
>> >> >> > property
>> >> >> > and one method. Each report has a textbox as it's first item
>> >> >> > that
>> >> >> > passes
>> >> >> > the report parameter and sets the culture property. Each textbox
>> >> >> > then
>> >> >> > uses the method and passes in a stringID. We configured the
>> >> >> > component
>> >> >> > to
>> >> >> > use an instance in the report. I believe this will hurt
>> >> >> > scalability,
>> >> >> > to
>> >> >> > what degree, uncertain, and will need to test. Looking into ways
>> >> >> > to
>> >> >> > set a
>> >> >> > property on the report and use that instead so that the component
>> >> >> > method
>> >> >> > could be static and shared across all report instances.
>> >> >> >
>> >> >> > Strings from queries:
>> >> >> > We used a Translation web service found on CodeProject to access
>> >> >> > BabelFish
>> >> >> > and perform translations. We plan on using dual columns in the
>> >> >> > db,
>> >> >> > but
>> >> >> > for current proof of concept this seemed like an easy approach.
>> >> >> > We
>> >> >> > may
>> >> >> > plan on using this in the future for text fields input from the
>> >> >> > user
>> >> >> > where
>> >> >> > they do not want to translate. We are not sure on this item yet.
>> >> >> > Either
>> >> >> > we add a drop down and they can enter the text in multiple
>> >> >> > languages,
>> >> >> > we
>> >> >> > translate at time of data input, we translate at time of report
>> >> >> > generation.
>> >> >> >
>> >> >> > Strings from queries (incomplete):
>> >> >> > We need to modify the source data to accomodate multiple
>> >> >> > languages,
>> >> >> > and
>> >> >> > add support to our stored procedures for this. This will
>> >> >> > eliminate
>> >> >> > or
>> >> >> > greatly reduce the dependency on BabelFish which will speed up
>> >> >> > the
>> >> >> > report
>> >> >> > generation and scale.
>> >> >> >
>> >> >> > I'd appreciate feedback, or what others have done to accomplish
>> >> >> > the
>> >> >> > same
>> >> >> > feature.
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Best way to filter out noise words from user entered search string

Hi there,
I'm implemeting a full text search on my company's site and am trying to get
around the 'Query contained only noise words' error. What's the best way of
stripping the text from the search string and then notifying the user that
the words were removed? (kind of like a google search). Is there a way to
compare the search string with the noise word file and remove all instances
of matching words?
Thanks!
-- are we all computer monkeys?
The best way to do this is to stop mssearch, replace the contents of your
noise word list with a single space and then restart MSSearch and rebuild
your catalog.
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
"Mr. Roundhill" <MrRoundhill@.discussions.microsoft.com> wrote in message
news:21EE62C6-74C2-4E01-B3A7-79D3612E5584@.microsoft.com...
> Hi there,
> I'm implemeting a full text search on my company's site and am trying to
get
> around the 'Query contained only noise words' error. What's the best way
of
> stripping the text from the search string and then notifying the user that
> the words were removed? (kind of like a google search). Is there a way
to
> compare the search string with the noise word file and remove all
instances
> of matching words?
> Thanks!
> --
> -- are we all computer monkeys?

Best way to do a free text search

HiI have a .net 2 website that works from a sql 2000 db. I am building a form that will allow the user to type in any search criteria and based on their input I need to produce some results (that's the simplest way to put it)I will need to search various parts of my db to find similar sounding information, I was just wondering what is the best way to do this. I had the following thoughts1) Search columns using Soundex tsql function (but not sure how good this is?)2) Remove all noise words from user input (eg, and, or, the etc...) and then use a regular expression to search the fields in the dbAlternatively are their some third party components to do can do this for me Many thanks in advance

You can full text search enable the database, and create a full text index on the column in the table you want to search. Then you will be able to search any word that appears inside that column. Here are a couple of links that might be helpful to you.

http://www.databasejournal.com/features/mssql/article.php/3441981

http://www.wherecanibuyit.co.uk/ASP/full-text-search.html

http://msdn2.microsoft.com/en-us/library/ms177652.aspx

|||

Hi

Thanks for that, it was a great help

I have noticed that their is a RowNumber method in SQL 2005, is their any way to do this functionality in SQL 2000

One method I thought of was to put the data in temp table and loop through each record and give it a number, and then pull out 10 records at a time as the user pages through.

Are their any other ways I could do this.

Many thanks in advance

|||

The short answer is yes. You may want to search for articles on Custom Paging with SQL Server 2000. There are ways to do it, but they are not as simple or clean as the ROW_NUMBER() function. Here is an article to get you started.

http://www.4guysfromrolla.com/webtech/042606-1.shtml

Best way to create a sql server role

I have an application that connects to SQL using SQL security. I would like
to give this user the permission to select data from all user tables and
execute all stored procedures.
I have accomplished this by creating a role and manually assigning the
"execute" permission to every stored proc to this role. Then I added the use
r
to that role. I have also assigned the user the db_datawriter and
db_datareader permissions.
My question is this - what happens when a new stored proc is added to the
system? Do I have to manually give that role execute rights to the new proc?
Is there a better way to accomplish this. To sumarize the requirements, the
user should be able to execute all stored procs and select data from any
table. Thanks!
- JohnnyHi
I hope you have already denied EXECUTE permission to public role.
You can generate dyamic script to grant an execute permissions and when a
new SP is added just reran the script and it will added to the role .
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:0346DA03-0106-47A7-937B-CFA32A5E345C@.microsoft.com...
>I have an application that connects to SQL using SQL security. I would like
> to give this user the permission to select data from all user tables and
> execute all stored procedures.
> I have accomplished this by creating a role and manually assigning the
> "execute" permission to every stored proc to this role. Then I added the
> user
> to that role. I have also assigned the user the db_datawriter and
> db_datareader permissions.
> My question is this - what happens when a new stored proc is added to the
> system? Do I have to manually give that role execute rights to the new
> proc?
> Is there a better way to accomplish this. To sumarize the requirements,
> the
> user should be able to execute all stored procs and select data from any
> table. Thanks!
> - Johnny|||Below is a script like the one Uri mentioned. Just change 'SpExecuteRule'
to your role(s).
--grant
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:0346DA03-0106-47A7-937B-CFA32A5E345C@.microsoft.com...
>I have an application that connects to SQL using SQL security. I would like
> to give this user the permission to select data from all user tables and
> execute all stored procedures.
> I have accomplished this by creating a role and manually assigning the
> "execute" permission to every stored proc to this role. Then I added the
> user
> to that role. I have also assigned the user the db_datawriter and
> db_datareader permissions.
> My question is this - what happens when a new stored proc is added to the
> system? Do I have to manually give that role execute rights to the new
> proc?
> Is there a better way to accomplish this. To sumarize the requirements,
> the
> user should be able to execute all stored procs and select data from any
> table. Thanks!
> - Johnny

best way to compare last row with new insert

Hi !
for MS SQL 2000

I need an UsersHistory Table where i cannot INSERT 2 rows one after the other with the same user.name

I can get

Bob
David
Bob

but not

Bob
Bob
David

something like an INDEX on rows
or
INSERT INTO UsersHistory (name) VALUES ('bob') IF MAX(name) <> 'bob' ?

what is the best way to do it ?

thank youYou need some other column to order by, say a datetime. it could store when the user last logged in/logged out/whatever. Why? because the physical order of rows in a table has no meaning. Since you are storing a history, the thing you want to order by is a time, so you need a datetime column.

Then you could do something like this:

-- assumes you already have a username in a @.name variable
declare @.time datetime
select @.time = max(LoggedInAt) from UsersHistory

if not exists(select * from UsersHistory where LoggedInAt=@.time and UserName=@.name)
begin
insert into UsersHistory (UserName, LoggedInAt) values (@.name, getdate())
end|||I have allready that datetime column = getdate()

but i dont understand the meaning of your query , if the last user row is Bob the next row cannot be Bob, it doesnt depend on the DateTime it can be 10 minutes ... one year
I must have an history of LastUsers for certains events in the database, the LastUser is the Last one as long as nobody do something and replace the last one (I know my english is terrific ! :-))

the last inserted row is also the MAX(id)

thanks a lot Jezemine|||Do something like:

INSERT INTO UsersHistory (name) VALUES ('bob') IF (SELECT fname from UsersHistory where id = max(id)) <> 'bob'

Note: not actual code