Rights running Exec (SQL)

Hi all.
It looks like I need READ rights on the involved tables using Exec (SQL)
within a prosedure instead of SELECT TableName and so on.It is not enough to
have EXEC rights on the prosedure itself. Is there any way around this or do
I need go grant read rights on all the tables in the SQL string.
If so, is there a easy way within the prosedure to check if the user have
this rights and if not grant it on the fly?
Thanx all.
gh> It looks like I need READ rights on the involved tables using Exec (SQL) within a prosedu
re
> instead of SELECT TableName and so on.
I assume that you wanted to say that you need SELECT permissions on the tabl
e, that EXEC procname
isn't enough. Yes, this is one of the drawbacks of using dynamic SQL. First
step is to see if you
really need that dynamic SQL. Then see what the new features in 2005 can do
for you:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/grantperm.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geir Holme" <geir@.multicase.no> wrote in message news:%23YXZ7i0mGHA.3928@.TK2MSFTNGP03.phx.
gbl...
> Hi all.
> It looks like I need READ rights on the involved tables using Exec (SQL) w
ithin a prosedure
> instead of SELECT TableName and so on.It is not enough to have EXEC rights
on the prosedure
> itself. Is there any way around this or do I need go grant read rights on
all the tables in the
> SQL string.
> If so, is there a easy way within the prosedure to check if the user have
this rights and if not
> grant it on the fly?
> Thanx all.
> gh
>|||Hi Tibor.
Thanx for the document. We are close to SQL2005, but not yet there.
I ended up with creating av view with the tables in question and runned the
EXEC on this view. That worked and the tables are still "safe" and hidden
for this user.
Thank you again for your interest in this.
-gh
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23YXZ7i0mGHA.3928@.TK2MSFTNGP03.phx.gbl...
> Hi all.
> It looks like I need READ rights on the involved tables using Exec (SQL)
> within a prosedure instead of SELECT TableName and so on.It is not enough
> to have EXEC rights on the prosedure itself. Is there any way around this
> or do I need go grant read rights on all the tables in the SQL string.
> If so, is there a easy way within the prosedure to check if the user have
> this rights and if not grant it on the fly?
> Thanx all.
> gh
>

RGB background color problem in Report Designer

Dear Report Designer Developers,
FYI - when I am using (e.g in a table) a RGB background color (e.g.
="#003399â' = blue) instead of a color of the selectbox there is always a
white background displayed in the Report Designer (layout view). Together
with a white font color I do not see anything in the Designer (white font on
displayed white background - instead the correct RGB background color) :-(
maybe you can change this in one of the next versions...
thx a lot
WolfgangYou can always send bugs/wishes to the following places just to make sure
your concern gets heard:
mswish@.microsoft.com
http://www.microsoft.com/mswish
--
Adrian M.
MCP
"Wolfgang Himmelsbach" <WolfgangHimmelsbach@.discussions.microsoft.com> wrote
in message news:C5710EBA-7379-4155-ADBE-3C8F69DC406E@.microsoft.com...
> Dear Report Designer Developers,
> FYI - when I am using (e.g in a table) a RGB background color (e.g.
> ="#003399" = blue) instead of a color of the selectbox there is always a
> white background displayed in the Report Designer (layout view). Together
> with a white font color I do not see anything in the Designer (white font
> on
> displayed white background - instead the correct RGB background color) :-(
> maybe you can change this in one of the next versions...
> thx a lot
> Wolfgang|||ok! thx. :-)
"Adrian M." wrote:
> You can always send bugs/wishes to the following places just to make sure
> your concern gets heard:
> mswish@.microsoft.com
> http://www.microsoft.com/mswish
> --
> Adrian M.
> MCP
>
> "Wolfgang Himmelsbach" <WolfgangHimmelsbach@.discussions.microsoft.com> wrote
> in message news:C5710EBA-7379-4155-ADBE-3C8F69DC406E@.microsoft.com...
> > Dear Report Designer Developers,
> >
> > FYI - when I am using (e.g in a table) a RGB background color (e.g.
> > ="#003399" = blue) instead of a color of the selectbox there is always a
> > white background displayed in the Report Designer (layout view). Together
> > with a white font color I do not see anything in the Designer (white font
> > on
> > displayed white background - instead the correct RGB background color) :-(
> >
> > maybe you can change this in one of the next versions...
> >
> > thx a lot
> > Wolfgang
>
>|||Instead of using an expression-based color ="#003399", you should just use a
constant #003399.
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wolfgang Himmelsbach" <WolfgangHimmelsbach@.discussions.microsoft.com> wrote
in message news:C5710EBA-7379-4155-ADBE-3C8F69DC406E@.microsoft.com...
> Dear Report Designer Developers,
> FYI - when I am using (e.g in a table) a RGB background color (e.g.
> ="#003399" = blue) instead of a color of the selectbox there is always a
> white background displayed in the Report Designer (layout view). Together
> with a white font color I do not see anything in the Designer (white font
> on
> displayed white background - instead the correct RGB background color) :-(
> maybe you can change this in one of the next versions...
> thx a lot
> Wolfgang|||thx for your information
"Robert Bruckner [MSFT]" wrote:
> Instead of using an expression-based color ="#003399", you should just use a
> constant #003399.
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Wolfgang Himmelsbach" <WolfgangHimmelsbach@.discussions.microsoft.com> wrote
> in message news:C5710EBA-7379-4155-ADBE-3C8F69DC406E@.microsoft.com...
> > Dear Report Designer Developers,
> >
> > FYI - when I am using (e.g in a table) a RGB background color (e.g.
> > ="#003399" = blue) instead of a color of the selectbox there is always a
> > white background displayed in the Report Designer (layout view). Together
> > with a white font color I do not see anything in the Designer (white font
> > on
> > displayed white background - instead the correct RGB background color) :-(
> >
> > maybe you can change this in one of the next versions...
> >
> > thx a lot
> > Wolfgang
>
>

Reuse reports for diference datatbase

Instead of use report manager(http://localhost/Reports) and go to data source
then change the connection string. Can I do it programmatically? And what
class or reference I have to declare'
My goal is do all this in custom code (reference) so base on the pass from
web app, reports will decide which database to connect to.Add the Reporting Services web service to your project as a web reference.
(http://YourServer/ReportServer/ReportService.asmx). There is a DataSource
class. All the members (methods) of the class are listed there.
Charles Kangai, MCT, MCDBA
"vbui" wrote:
> Instead of use report manager(http://localhost/Reports) and go to data source
> then change the connection string. Can I do it programmatically? And what
> class or reference I have to declare'
> My goal is do all this in custom code (reference) so base on the pass from
> web app, reports will decide which database to connect to.
>|||Thanks, do you see anyone do that way or do you have a example of how to use
the reportingservice web reference. It'll help me a lot if you have one.
"Charles Kangai" wrote:
> Add the Reporting Services web service to your project as a web reference.
> (http://YourServer/ReportServer/ReportService.asmx). There is a DataSource
> class. All the members (methods) of the class are listed there.
> Charles Kangai, MCT, MCDBA
> "vbui" wrote:
> > Instead of use report manager(http://localhost/Reports) and go to data source
> > then change the connection string. Can I do it programmatically? And what
> > class or reference I have to declare'
> >
> > My goal is do all this in custom code (reference) so base on the pass from
> > web app, reports will decide which database to connect to.
> >|||Follow this link on Microsoft's web site
http://www.microsoft.com/downloads/details.aspx?FamilyID=e03e1a26-ff8c-4f30-b68c-08c9b27c3d54&DisplayLang=en
Charles Kangai, MCT, MCDBA
"vbui" wrote:
> Thanks, do you see anyone do that way or do you have a example of how to use
> the reportingservice web reference. It'll help me a lot if you have one.
> "Charles Kangai" wrote:
> > Add the Reporting Services web service to your project as a web reference.
> > (http://YourServer/ReportServer/ReportService.asmx). There is a DataSource
> > class. All the members (methods) of the class are listed there.
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "vbui" wrote:
> >
> > > Instead of use report manager(http://localhost/Reports) and go to data source
> > > then change the connection string. Can I do it programmatically? And what
> > > class or reference I have to declare'
> > >
> > > My goal is do all this in custom code (reference) so base on the pass from
> > > web app, reports will decide which database to connect to.
> > >

Returning only the Header Row instead of all the relevant rows.

Hi All:

My below sub in application is returning only the Header Row instead of the relevant rows I guess therez problem in my "str" Syntax. However I fail to understand where exactly is it faultering.

PrivateSub btnSearch_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnSearch.Click

Try

con.Open()

Dim empIDAsString = txtEmpID.Text

Dim FNameAsString = txtFName.Text

Dim LNameAsString = txtLName.Text

Dim strAsString = "Select Emp_ID, Emp_FName,Emp_LName,Emp_Address1,Emp_HNo,Emp_MNo from Emp_Details where Emp_ID='" & empID & " ' Or Emp_LName='" & LName & " ' Or Emp_FName=' " & FName & "'"""Dim da1AsNew SqlDataAdapter(str, con)

da1.Fill(ds, "Emp_Details")

dgEmp.DataSource = ds

dgEmp.DataMember = "Emp_Details"

dgEmp.DataBind()

Finally

con.Close()

EndTry

EndSub

Thanks in Advance for your quick help.

Regards,

Brandy

It looks like you might have one too many extra spaces in your statement. Try this instead:

Dim strAs String =String.Format("SELECT Emp_ID, Emp_FName, Emp_LName, Emp_Address1, Emp_HNo, Emp_MNo FROM Emp_Details WHERE Emp_ID ='{0}' OR Emp_LName = '{1}' OR Emp_FName = '{2}'", empID, LName, FName)

|||

This works... Appreciate your kind help.

returning one single record rather than multiples

Hi

Is it possible to return the results of a query so that instead of
having say 10 rows its concatenated, eg

My query returns 'M' 10 times, can this be returned as 'M M M M M M M
M M M'?

Thanks
LeeLee (lee@.digital-interactive.com) writes:

Quote:

Originally Posted by

Is it possible to return the results of a query so that instead of
having say 10 rows its concatenated, eg
>
My query returns 'M' 10 times, can this be returned as 'M M M M M M M
M M M'?

In SQL 2005:

SELECT subtring(Ms, 1, datalength(Ms) / 2 - 1
FROM (SELECT col + ' ' AS [text()]
FROM tbl
FOR XML PATH('')) AS T(Ms)

This makes use of the XML functionality, and has the drawback that some
characters will be encoded.

In SQL 2000, you are unfortunately best of with a cursor.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hi

I've just tried that (in SQL 2005)

SELECT subtring(Ms, 1, datalength(Ms) / 2 - 1
FROM (SELECT [company size] + ' ' AS [text()]
FROM results
FOR XML PATH('')) AS T(Ms)

and its returns errors, is that a working example?

Thanks
Lee|||The example from Erland is good, he was just sketching quickly to give you
an idea of how to solve your problem (and something to work on, since there
was no DDL posted). Here is a copy and paste extension to that:

CREATE TABLE tbl (col char(1))

INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')

SELECT substring(Ms, 1, datalength(Ms) / 2 - 1)
FROM (SELECT col + ' ' AS [text()]
FROM tbl
FOR XML PATH('')) AS T(Ms)

DROP TABLE tbl

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Returning more than one of same acct instead of sum for both

Code not combining acct number results.
The code below returns for example:
74981995170968 1595.00 253.54 1341.46 940.40
74981995170968 1595.00 2281.86 3876.86 940.40
It should be:
74981995170968 1595.00 2535.4 1341.36 940.40
above values come from
2281.86 + 253.54 = 2535.4
2535.4 - 1595.00
When I need it to combine the information and it's not because of the CASE statement within my first INNER join where I choose to SUM rmstranamt based on if there are more than one record or not
.
Here's the query:
SELECT rm.rmsacctnum,
SUM(rf.rmstranamt) AS [Sum rmstranamt],
rf10.rmstranamt10 AS [Sum rmstranamt 10],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 > 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 > 0 Then
SUM(rf.rmstranamt) - rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
END
WHEN SUM(rf.rmstranamt) = 0 AND rf10.rmstranamt10 = 0 Then
0.00
WHEN SUM(rf.rmstranamt) = 0 AND rf10.rmstranamt10 <> 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) <> 0 AND rf10.rmstranamt10 = 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 < 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 > 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 > 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
INNER JOIN
(
SELECT DISTINCT RMSFILENUM,
CASE WHEN COUNT(*) > 1 THEN
SUM(rmstranamt) as rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
ELSE
rmstranamt as rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM, rmstranamt
END
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)
+(RMSASSCCST - RMSRCVDCST)
+(RMSACRDINT - RMSRCVDINT)
+(UDCCOSTS1 - UDCRECCS1)
+(UDCCOSTS2 - UDCRECCS2)
+(RMSCOST1 - RMSCOST1R)
+(RMSCOST2 - RMSCOST2R)
+(RMSCOST3 - RMSCOST3R)
+(RMSCOST4 - RMSCOST4R)
+(RMSCOST5 - RMSCOST5R)
+(RMSCOST6 - RMSCOST6R)
+(RMSCOST7 - RMSCOST7R)
+(RMSCOST8 - RMSCOST8R)
+(RMSCOST9 - RMSCOST9R)
+(RMSCOST10 - RMSCOST10R)
- RMSXCSRCVS
) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM
WHERE rf.rmstrancde IN ('10', '16','18','19','20','21','22','29','30','31','36','37','38','3A','3B','3C','3D','3E','3F','3M','3N','3O','3P','3Q','3R','3T',
'3U','3X','3Z','40','41','42','43','44','45','46','47','48','49','4A','4B','4D','4E','4H','4J','4X','4Z','50','51','52','53',
'55','56','57','58','5A','5B','5C','5P','5Q','5R','5X','5Z')
AND rm.rmsacctnum = '74981995170968'
GROUP BY rm.rmsacctnum, cb.CurrentBalance, rf10.rmstranamt10
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00

sorry I'm getting dizzy just looking at that. However, I think the following line might be flawed:
CASE WHEN COUNT(*) > 1 THEN

Basically this will always return true whenever the rfinanl table has more than one row.

Sorry I can't be of more assistance.

|||

finally, I got this figured out:

INNER JOIN
(
SELECT RMSFILENUM,
SUM(distinct rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

returning limited number of records!

I am using ORDER BY NEWID() to return random record from sql database. how do i go about returning only 5 random records instead of all records.

Thanks.Since SELECT * FROM Table ORDER BY NEWID() will return the rows in a random order, all you need to do is use the TOP keyword to limit the results for that particular query.

SELECT TOP 5 * FROM Table
ORDER BY NEWID()

If, for some reason, that doesn't work, there's the slightly less elegant solution of

SET ROWCOUNT = 5
SELECT * FROM Table
ORDER BY NEWID()
SET ROWCOUNT = 0

I hope this helps.|||it's working perfect.

thanks.