Deserialization failed error. Trying to automate an RDL file.

Whenever I try to change the SQL query on the RDL (which is an XML doc) and save it under a different name (to keep the original) I get the error...
"Deserialization failed: This is an unexpected token. The expected token is 'NAME'. Line 3325 position 28"

I am checking that line number and cannot fine any correlation.

One way to replicate this error is to create a new rdl file querying any number of fields. Save it and try to edit the sql using notepad inside the <CommmandText> section of the XML file. All I am doing is adding a union query so the fields stay the same, but I keep getting that error.

My purpose behind this is to generate rdl files with different sql criteria from an orignal template rdl.

Can any of you gurus figure this out?

No anwers on this yet?|||Still waiting...

Desapering rows

Hi.

I have an mdx query which return some rows and for some values there are null values for 1 column (it's ok). But when the report is rendered have disapeared all these rows. I need to show these rows because they have value for other columns.

Thanks in advance.I refresh the datasource by editing the query and refresh and that's all.

I don't know what happend.

Derived Tables

OK...I know how to write a query to return for example :

All the people that ordered X and Y

but how do I write one for:

All the people that ordered X but not Y?

Thanks,
TreyO yea...this is how i did the first part

SELECT DISTINCT c.Company
FROM Customers as c
JOIN
 (SELECT CustomerID
 FROM Orders o
 JOIN [Order Details] od
 ON o.OrderID = od.OrderID
 JOIN products p
 ON od.ProductID = p.ProductID
 WHERE p.ProductName = 'X') as temp1
 ON c.CustomerID = temp1.CustomerID
JOIN
 (SELECT CustomerID
 FROM Orders o
 JOIN [Order Details] od
 ON o.OrderID = od.OrderID
 JOIN products p
 ON od.ProductID = p.ProductID
 WHERE p.ProductName = 'Y') as temp2
 ON c.CustomerID = temp2.CustomerID

Derived table and adding another column problem

Hi
I have this query below, which I'm trying to add and group data on the w
number (ISO w, taken from Books online) fn_getISOW returns an integer
and works independently of this query. This query also works as I want when
there is no wno invloved (the 3 places)
The error I get is "Invalid column name 'wno'", I think what I'm doing is
a "copy" of what has been done in the count(case.....) as xx
Can anyone see what I'm doing wrong and how to fix it?
regards
Henry
declare @.ext char(4)
declare @.site int
declare @.calltype char(1)
SET DATEFORMAT mdy
set @.site = 1
set @.calltype = 'E'
set @.ext = '6810'
SELECT wno***, network, centre, center, team, total, besvaret, optaget,
opgivet, ubesvaret
FROM (
SELECT network, centre, center, team,
COUNT(*) AS total,
COUNT(CASE WHEN durationofconversation > 0 THEN 1
END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'OC' THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND
durationofcall >= 40 THEN 1 END) AS ubesvaret,
tcv2.dbo.fn_getISOw(DATECALLINITIATED) as wno
***
FROM V2tickets
INNER JOIN
(SELECT s.serie, a.nodename as network, cs.nodename as
centre, c.nodename as center, t.nodename as team
FROM areas a, centers cs, center c, teams t, series s
WHERE (t.nodeid = 1 or t.nodeid=8 or t.nodeid=2 or
t.nodeid=72 or t.nodeid=73) and a.nodeid=cs.parentid and
cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid) AS H
ON V2tickets.digits = H.serie
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
GROUP BY wno***, network, centre, center, team) AS D
ORDER BY 1Try this
SELECT wno***, network, centre, center, team, total, besvaret,
optaget,
opgivet, ubesvaret
FROM (
SELECT network, centre, center, team,
COUNT(*) AS total,
COUNT(CASE WHEN durationofconversation > 0
THEN 1
END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'OC' THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND
durationofcall >= 40 THEN 1 END) AS ubesvaret,
tcv2.dbo.fn_getISOw(DATECALLINITIATED) as
wno
***
FROM V2tickets
INNER JOIN
(SELECT s.serie, a.nodename as network, cs.nodename as
centre, c.nodename as center, t.nodename as team
FROM areas a, centers cs, center c, teams t, series s
WHERE (t.nodeid = 1 or t.nodeid=8 or t.nodeid=2 or
t.nodeid=72 or t.nodeid=73) and a.nodeid=cs.parentid and
cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid)
AS H
ON V2tickets.digits = H.serie
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
) AS D
GROUP BY wno***, network, centre, center, team ORDER BY 1
Madhivanan|||> Try this
> Madhivanan
>
Hi
No I'm sorry that doesn't do the trick either :(
regards
Henry|||> > Try this
> Hi
> No I'm sorry that doesn't do the trick either :(
> regards
> Henry
Solved... I had to use the same syntax in the group by part since wno is
not know at that time.
regards
Henrik

Derived Query

I'm trying to build a "derived" query from 2 seperate table a Build count
table and a defect table
I've done this before but never with the calculated columns as in
Year(TestDate) as 'Yr'. It fails and returns
Server: Msg 207, Level 16, State 1, Line 17
Invalid column name 'YR'.
Any help ?
Select Year(TestDate) as 'Yr',
DateName(mm,TestDate)as 'Mnth',
Category, SubCategory,
Sum(NumFailures) as 'Fails'
From ftc_testresults T1
INNER JOIN
(
Select Year(TestDate) as 'Yr',
DateName(mm,TestDate)as 'Mnth',
Sum(DropCount) as 'Dropped'
From FTC..FTC_DropCount_Results
Where DropCount > 0
And TestDate >= '09-01-2005'
Group By Year(TestDate),
DateName(mm,TestDate)
) CT
ON T1.YR = CT.YR
Group By '"Paul Ilacqua" <pilacqu2@.twcny.rr.com> wrote in message
news:OgAwBv2HGHA.1728@.TK2MSFTNGP09.phx.gbl...
> I'm trying to build a "derived" query from 2 seperate table a Build count
> table and a defect table
> I've done this before but never with the calculated columns as in
> Year(TestDate) as 'Yr'. It fails and returns
> Server: Msg 207, Level 16, State 1, Line 17
> Invalid column name 'YR'.
> Any help ?
>
> Select Year(TestDate) as 'Yr',
> DateName(mm,TestDate)as 'Mnth',
> Category, SubCategory,
> Sum(NumFailures) as 'Fails'
> From ftc_testresults T1
> INNER JOIN
> (
> Select Year(TestDate) as 'Yr',
> DateName(mm,TestDate)as 'Mnth',
> Sum(DropCount) as 'Dropped'
> From FTC..FTC_DropCount_Results
> Where DropCount > 0
> And TestDate >= '09-01-2005'
> Group By Year(TestDate),
> DateName(mm,TestDate)
> ) CT
> ON T1.YR = CT.YR
>
A column alias cannot be used in the same query in which it is assigned. So
in the outer query T1.YR is not legal. You must repeat the source
expression Year(T1.TestDate). And get rid of the single quotes around
column aliases, and add table aliases to all your column references.
Something like this:
Select Year(T1.TestDate) as Yr,
DateName(mm,T1.TestDate)as Mnth,
T1.Category,
T1.SubCategory,
Sum(T1.NumFailures) as Fails
From ftc_testresults T1
INNER JOIN
(
Select Year(TestDate) as Yr,
DateName(mm,TestDate)as Mnth,
Sum(DropCount) as Dropped
From FTC..FTC_DropCount_Results
Where DropCount > 0
And TestDate >= '09-01-2005'
Group By Year(TestDate),
DateName(mm,TestDate)
) CT
ON Year(T1.TestDate) = CT.YR
David|||Dave,
Thanks for the quick and accurate reply.....
Curious... what's the taboo about single quotes around column aliases?
Final working product....
Select Year(T1.TestDate) as Yr,
DateName(mm,T1.TestDate) as Mnth,
T1.Category, T1.SubCategory,
IsNull(CT.Dropped , 0) as Dropped,
Sum(T1.NumFailures) as Failures
From ftc_testresults T1
INNER JOIN
(
Select Year(TestDate) as Yr,
DateName(mm,TestDate)as Mnth,
Sum(DropCount) as Dropped
From FTC..FTC_DropCount_Results
Where DropCount > 0
Group By Year(TestDate),
DateName(mm,TestDate)
) CT
ON Year(T1.TestDate) = CT.YR
AND DateName(mm,TestDate) = CT.Mnth
Where T1.TestDate > = '09-01-2005'
Group By T1.TestDate,T1.Category,
T1.SubCategory, ct.dropped;
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uCN8P72HGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Paul Ilacqua" <pilacqu2@.twcny.rr.com> wrote in message
> news:OgAwBv2HGHA.1728@.TK2MSFTNGP09.phx.gbl...
> A column alias cannot be used in the same query in which it is assigned.
> So in the outer query T1.YR is not legal. You must repeat the source
> expression Year(T1.TestDate). And get rid of the single quotes around
> column aliases, and add table aliases to all your column references.
> Something like this:
>
> Select Year(T1.TestDate) as Yr,
> DateName(mm,T1.TestDate)as Mnth,
> T1.Category,
> T1.SubCategory,
> Sum(T1.NumFailures) as Fails
> From ftc_testresults T1
> INNER JOIN
> (
> Select Year(TestDate) as Yr,
> DateName(mm,TestDate)as Mnth,
> Sum(DropCount) as Dropped
> From FTC..FTC_DropCount_Results
> Where DropCount > 0
> And TestDate >= '09-01-2005'
> Group By Year(TestDate),
> DateName(mm,TestDate)
> ) CT
> ON Year(T1.TestDate) = CT.YR
> David
>|||"Paul Ilacqua" <pilacqu2@.twcny.rr.com> wrote in message
news:u9eSrD3HGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Dave,
> Thanks for the quick and accurate reply.....
> Curious... what's the taboo about single quotes around column aliases?
>
Not a taboo, just a style preference. A column alias is an identifier, a
name, and it should follow the same convention as other identifiers (tables,
columns, table aliases).
David|||>> Curious... what's the taboo about single quotes around column aliases? <<
In Standard SQL, strings are enclosed in single quotes and user created
names are in double quotes. Violating standards is never a good idea;
makes code harder to pport, harder to read and occassionally can scew
up things.
You might also want to look up some old postings of mine as to how a
SELECT statement works.

Derived Columns in one to many relationships

I'm trying to write a query that concatenates multiple records into one
derived column. Let's say I have an author (Joe Writer) who has written
three books (Book 1, Book2 and Book 3). The author is in tblAuthors, his
books are in the tblBooks and they are joined by the AuthorID field
(number). If I use a simple select query to give me the author name and the
title, I will get three records, one for each book written.

What I want is to have all three books combined into one derived column. So
if I do the select statement, I will get one column with the author name,
and the second column will put together all three names of the book
separated by a column. So it will look like:

Author Title

Joe Writer Book 1, Book 2, Book 3,

Rather than having it appear as 3 records:

Joe Writer Book 1
Joe Writer Book 2
Joe Writer Book 3

Could someone help me with the SQL involved in this?

Thanks for the help.

Cheers,

MikeOne approach is shown in http://www.mvps.org/access/modules/mdl0008.htm at
"The Access Web"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Big Time" <big-time-grizz@.remove-for-spam-hotmail.com> wrote in message
news:cfu2e7$18gm$1@.lettuce.bcit.ca...
> I'm trying to write a query that concatenates multiple records into one
> derived column. Let's say I have an author (Joe Writer) who has written
> three books (Book 1, Book2 and Book 3). The author is in tblAuthors, his
> books are in the tblBooks and they are joined by the AuthorID field
> (number). If I use a simple select query to give me the author name and
the
> title, I will get three records, one for each book written.
> What I want is to have all three books combined into one derived column.
So
> if I do the select statement, I will get one column with the author name,
> and the second column will put together all three names of the book
> separated by a column. So it will look like:
> Author Title
> Joe Writer Book 1, Book 2, Book 3,
> Rather than having it appear as 3 records:
> Joe Writer Book 1
> Joe Writer Book 2
> Joe Writer Book 3
> Could someone help me with the SQL involved in this?
> Thanks for the help.
> Cheers,
> Mike|||Mike,

read this article...

http://www.mvps.org/access/modules/mdl0004.htm

It has code that does this.|||Try this out
DECLARE @.BookNames varchar(1000)
SET @.BookNames = ''
SELECT @.BookNames = @.BookNames +Book + ', ' FROM Books
where author = 'Joe Writer'
SELECT 'Joe Writer',@.BookNames|||JK (jaikrishnan_nair@.hotmail.com) writes:
> Try this out
> DECLARE @.BookNames varchar(1000)
> SET @.BookNames = ''
> SELECT @.BookNames = @.BookNames +Book + ', ' FROM Books
> where author = 'Joe Writer'
> SELECT 'Joe Writer',@.BookNames

This may work. Or not work. The result of this sort of operation is
undefined in SQL Server. This is one of the few situations where
iterating over the data is a better option.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Derived column based on result of Oracle query

Hi,

I need to create a derived column for each row in a SQL dataset.

This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0).

One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ?

Thanks

Here is one way to do this...

-Have one source for the sql source and another source for the oracle source.

-Send them both to a merge join, choose a left outer join with the sql source as the left input, choose one column from the oracle source.

-After the merge join use a derived column transformation. Use an expression to say that if the field from the oracle source is null then 0 else 1.

edit: After re-reading your post, the date range makes things much more difficult. It may be best to send both sources to staging tables in sql server, and then write run a query to join them together perhaps a (correlated subquery with a case statement in the select clause).

|||

Hi,

I am not sure if this will solve your problem completely:

In a Data Flow Task pull the two values from the SQL dataset into the Recordset Destination.

Now, in the Control Flow Task, use a For Each Loop Container to store the Recordset into a variable (you will have to create variables for each field that you want to write to the destination as well as one for the recordset).

Loop through the recordset and for each pair of values, use a Excecute SQL Task to execute a SQL query or a Stored Procedure which will return matching record(s). Now based on whether a record was returned or not, write all the fields, plus the derived column value into the destination.

Regards,

B@.ns

|||

Thanks guys - I actually used a combination of your techniques to get the result.

|||I just wanted to point out that you can use a Lookup to do this. You just need to go the the advanced tab, enable memory restriction, and you can alter the query used for the lookup to include ranges.sql

Derive columns from cube member?

I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).

So I now have the data and I want to derive new columns based on that data using an if statement:

if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"

comptype is the new derived column I am creating.

But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis

I selected the cube element from the columns list in derived element, when it initially put it in it looked like:

[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]

and gave error:

The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Below is the statement I use to get data back from AS

select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')

So can some one help me on how I can do this derived colum.

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

To get <fieldname> expand the 'columns' folder in the derived column transformation editor.

|||Use a derived column to create new columns.

New Column Name: comptype
Expression: ISNULL([InputColumn]) ? "NAT" : "REG"|||

Anthony Martin wrote:

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

Anthony, just simplify that statement. ISNULL() returns a boolean value, so there's no need to doubly check for a true status or not. Just drop the "== true" part.|||

Thanks, good point. Just a habit I guess.

|||

I have figured out what I had to do, but I do not know why I had to do this way, but first while I saw after I posted that used used bad assignment of derived column comptype = <value>. that was one of my many different attempts around the question I posted about.

1st) I could never get the if else statment to work in derived field, I had to use conditional if (? logic for my ifs

2nd) I could not referenct the the MDX columns in my conditional if. It did not like all of the '[' and ']', so I had to convert all of the columns from MDX column name to a more standard sql column name, basically renamed column to not have '[', ']' in them

Does any one know why this does not work neatly, MDX is supposed to have been integrated with SSIS, but I had to do multiple tricks to get the data, right from start where I had to use ad hoc query though a sql database to execute the MDX.

This also results in fact that during selection of MDX it did not maintain any of the data types and converted everything to chararacter field of size 4000, which gave me trunctaion warnings in the selection of MDX into SSIS

Derive columns from cube member?

I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).

So I now have the data and I want to derive new columns based on that data using an if statement:

if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"

comptype is the new derived column I am creating.

But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis

I selected the cube element from the columns list in derived element, when it initially put it in it looked like:

[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]

and gave error:

The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Below is the statement I use to get data back from AS

select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')

So can some one help me on how I can do this derived colum.

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

To get <fieldname> expand the 'columns' folder in the derived column transformation editor.

|||Use a derived column to create new columns.

New Column Name: comptype
Expression: ISNULL([InputColumn]) ? "NAT" : "REG"|||

Anthony Martin wrote:

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

Anthony, just simplify that statement. ISNULL() returns a boolean value, so there's no need to doubly check for a true status or not. Just drop the "== true" part.|||

Thanks, good point. Just a habit I guess.

|||

I have figured out what I had to do, but I do not know why I had to do this way, but first while I saw after I posted that used used bad assignment of derived column comptype = <value>. that was one of my many different attempts around the question I posted about.

1st) I could never get the if else statment to work in derived field, I had to use conditional if (? logic for my ifs

2nd) I could not referenct the the MDX columns in my conditional if. It did not like all of the '[' and ']', so I had to convert all of the columns from MDX column name to a more standard sql column name, basically renamed column to not have '[', ']' in them

Does any one know why this does not work neatly, MDX is supposed to have been integrated with SSIS, but I had to do multiple tricks to get the data, right from start where I had to use ad hoc query though a sql database to execute the MDX.

This also results in fact that during selection of MDX it did not maintain any of the data types and converted everything to chararacter field of size 4000, which gave me trunctaion warnings in the selection of MDX into SSIS

deploying with default parameter value

I have an application with a parameter that has a single value drop down list from a query. My dataset has a union with "0","All". I set the default to non-queried, value "0". I go to preview and BI displays the label correctly as "All". When I deploy the app and view it in the web browser the default value is not displayed correctly, and it shows "Select a value". Why, why, why does it preview correctly yet deploys incorrectly, and how can I fix it
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comYou can try deleting the earlier deployed report from Report Manager
and deploy the modified report which is previewing fine with all
default values.
Thanks
Subhash

Deploying vb.net english query application

Hi,
I'm attempting to distribute a vb.net english query application to a
client, however, when the client runs the application, it crahses when
it tries to create an instance of the "Mseq.Session" object.
As far as I can tell, the interop and the original dll are being
included in the setup correctly - are there any special settings (such
as com registration, system etc), or other files that I should be
including? Or are there any other prerequisits for the client's pc
before I can expect the software to run?
Cheers,
Tinium.
you are still going to need to install the EQ engine on the target
platform.. There's an awful lot of work done by the engine before the query
is shipped off the Server. EQ is a client function..
Guy
"Tinium" <tin@.ium.com> wrote in message
news:eWfltpaQFHA.132@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm attempting to distribute a vb.net english query application to a
> client, however, when the client runs the application, it crahses when it
> tries to create an instance of the "Mseq.Session" object.
> As far as I can tell, the interop and the original dll are being included
> in the setup correctly - are there any special settings (such as com
> registration, system etc), or other files that I should be including? Or
> are there any other prerequisits for the client's pc before I can expect
> the software to run?
> Cheers,
> Tinium.

Dependencies within a transaction

If i were to have a dynamic SQL transaction, where i want to insert a
record, and use the ID generated for this record, in a subsequent query in
the same transaction , how do i do it ?
Regards,
Chak.How are you generating the id? Is that an IDENITITY? or GUID? See @.@.IDENTITY
and SCOPE_IDENTITY() in SQL Server Books Online. Also see:
http://vyaskn.tripod.com/retrieve_g...ke_identity.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chakra" <chakra@.smnetserv.com> wrote in message
news:OnfQHsgJFHA.2628@.tk2msftngp13.phx.gbl...
> If i were to have a dynamic SQL transaction, where i want to insert a
> record, and use the ID generated for this record, in a subsequent query in
> the same transaction , how do i do it ?
> Regards,
> Chak.
>

Deny user roles from a Stored Procedure.

Hello,

I am trying to deny a user from processing a query, however the user still seems to be able to see the procedure.

I have executed the following command on both the 'Public' and specific user name.

DENY EXECUTE ON [OBJECT_NAME] TO [PUBLIC]

However when i run the command 'sp_helprotect OBJECT_NAME' afterwards it shows that deny is set on 'Public', yet the user can still run the procedure from a web report... I have tried restarting IIS and this also does nothing, any ideas?

Thanks,

RichAre you using windows authentication? If so, make sure that a Windows group does not have execute authority on the procedure. I would also check to make sure the user acount that is the problem does not have an elevated role like db_owner or a system role like sysadmin. I'm assuming that you verified that the application is actually using the login you have been denying permissions on.|||

Quote:

Originally Posted by rob313

Are you using windows authentication? If so, make sure that a Windows group does not have execute authority on the procedure. I would also check to make sure the user acount that is the problem does not have an elevated role like db_owner or a system role like sysadmin. I'm assuming that you verified that the application is actually using the login you have been denying permissions on.

the user might be a "dbo".|||If the user is dbo then SQL Server does not check any other permissions (grant or deny), so that would be why you are seeing this behavior. You will need to remove the user from dbo and grant that account the needed permissions or role.

deny truncate?

What if I want to give users Query Analyzer-style access to my data, but
don't want them to delete it?
I can do
deny delete on dbo.mytable to public
which is great... assuming they don't know how to do 'truncate table'.
uh?
is there any way I can prevent users from truncating a table by permissions?Only members of the sysadmin server role and the db_owner and db_ddladmin
database roles and the table owner have permissions to Truncate the table.
Jacco Schalkwijk
SQL Server MVP
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:86930C7A-AC66-4E96-AB9C-487200CD223D@.microsoft.com...
> What if I want to give users Query Analyzer-style access to my data, but
> don't want them to delete it?
> I can do
> deny delete on dbo.mytable to public
> which is great... assuming they don't know how to do 'truncate table'.
> uh?
> is there any way I can prevent users from truncating a table by
> permissions?|||> is there any way I can prevent users from truncating a table by
permissions?
Yes, don't make them administrators or dbo.
http://www.aspfaq.com/
(Reverse address to reply.)|||From Books Online:
Permissions
TRUNCATE TABLE permissions default to the table owner, members of the
sysadmin fixed server role, and the db_owner and db_ddladmin fixed database
roles, and are not transferable.
The bottom line: Manage security properly and the users will not have
rights to do anything that you do not want them to.
Keith
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:86930C7A-AC66-4E96-AB9C-487200CD223D@.microsoft.com...
> What if I want to give users Query Analyzer-style access to my data, but
> don't want them to delete it?
> I can do
> deny delete on dbo.mytable to public
> which is great... assuming they don't know how to do 'truncate table'.
> uh?
> is there any way I can prevent users from truncating a table by
permissions?|||OK...
We have implemented a rule that any developer that needs to create tables in
a database has to be members of the db_owner role of the database as they
need to be able to create dbo. prefixed tables, as if they can't, then one
will create a table and it will be called, say
mydb.dave.davestable
and then when, say Julie, selects, she will effectively get the results from
mydb.julie.davestable
which either won't exist or else she won't get the same results as dave...
I don't need them to be db_owner for anything other than to be create and
run any existing dbo. prefixed SPs and also any future ones aswell without
the future ones being explicitly assigned to them.
e.g. I need to create a dbo. procedure, and I need to be able to create
another dbo.procedure, without having to spend time setting users permission
s
from it whenever I create a new procedure.
"Bonj" wrote:
[vbcol=seagreen]
> What if I want to give users Query Analyzer-style access to my data, but
> don't want them to delete it?
> I can do
> deny delete on dbo.mytable to public
> which is great... assuming they don't know how to do 'truncate table'.
> uh?
> is there any way I can prevent users from truncating a table by permissions?[/vbco
l]|||No problem as long as you have separate development and production
environments and the developers are not a member of the db_owner role on the
production environment?
Jacco Schalkwijk
SQL Server MVP
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:F3F68F4F-15C4-44DA-809C-B297C4F1AD84@.microsoft.com...[vbcol=seagreen]
> OK...
> We have implemented a rule that any developer that needs to create tables
> in
> a database has to be members of the db_owner role of the database as they
> need to be able to create dbo. prefixed tables, as if they can't, then one
> will create a table and it will be called, say
> mydb.dave.davestable
> and then when, say Julie, selects, she will effectively get the results
> from
> mydb.julie.davestable
> which either won't exist or else she won't get the same results as dave...
> I don't need them to be db_owner for anything other than to be create and
> run any existing dbo. prefixed SPs and also any future ones aswell without
> the future ones being explicitly assigned to them.
> e.g. I need to create a dbo. procedure, and I need to be able to create
> another dbo.procedure, without having to spend time setting users
> permissions
> from it whenever I create a new procedure.
>
> "Bonj" wrote:
>|||>>I don't need them to be db_owner for anything other than to be create and
run any existing dbo. prefixed SPs and also any future ones aswell without
the future ones being explicitly assigned to them.<<
They do not need to be dbo to execute a dbo owned stored procedure. Just
have dbo issue
GRANT EXECUTE ON dbo.YourProcName TO DeveloperNameGoesHere
You can easily create a stored procedure that issues the appropriate grant
statements to the appropriate users on all stored procedures or on a subset
of stored procedures based on creation date or name. You decide what you
need and create the supporting stored procedure to do it.
Giving users the ability to create tables...I would not give them dbo
rights for this. I would let them play in their own "sandbox" by creating
tables under their account. When their sand castle (or table) has been
approved then dbocan create the table within the development environment (as
dbo).
I have used the same techniques in the past with much success.
Keith
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:F3F68F4F-15C4-44DA-809C-B297C4F1AD84@.microsoft.com...
> OK...
> We have implemented a rule that any developer that needs to create tables
in
> a database has to be members of the db_owner role of the database as they
> need to be able to create dbo. prefixed tables, as if they can't, then one
> will create a table and it will be called, say
> mydb.dave.davestable
> and then when, say Julie, selects, she will effectively get the results
from
> mydb.julie.davestable
> which either won't exist or else she won't get the same results as dave...
> I don't need them to be db_owner for anything other than to be create and
> run any existing dbo. prefixed SPs and also any future ones aswell without
> the future ones being explicitly assigned to them.
> e.g. I need to create a dbo. procedure, and I need to be able to create
> another dbo.procedure, without having to spend time setting users
permissions[vbcol=seagreen]
> from it whenever I create a new procedure.
>
> "Bonj" wrote:
>
permissions?|||erm.... nah, that wouldn't really work.
You see, the thing is we *do* have separate development and production
environments... slightly (tries to keep a straight face). No, we do.
It's just that most things will need further development after they've been
put into production. In fact, I tend to rate a system as good if it needs
very little time spent developing it after it's been put into production,
although the contrary doesn't necessarily mean it's bad. However, I digress.
To put it diplomatically, when forming an expectation as to how long it will
be before the results of a 'requested improvement' will be seen, the
requester is likely to look on the optimistic end of the scale of how much
testing will be required. Consequently, code changes are made in production
environment - so the opportunity to use the results as real if the 'testing'
all passes, doesn't need to be missed. At the same time, if a production
server is busy with several jobs, other ad-hoc systems but that are
officially 'in production' will overflow onto one of the spare 'development'
servers.
Hence, it makes no sense to *force* a certain server to be either *always*
development or *always* production.
Besides, if a system is in production, the amount of use through Query
Analyzer it gets is less than one that's in development, so people are
unlikely to accidentally truncate the table anyway, but they are when in
development.
I think the most likely cause of the data getting deleted is the database
being recompiled. But then, that's a good thing, as it ensures one run
doesn't inherit the previous one's values. But I could go for miles down
that road - which is at the end of the day a lot like writing a load of
error handling code which might never be called.
but thanks for your help anyway
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23pmvt3TsEHA.1336@.tk2msftngp13.phx.gbl...
> No problem as long as you have separate development and production
> environments and the developers are not a member of the db_owner role on
the
> production environment?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:F3F68F4F-15C4-44DA-809C-B297C4F1AD84@.microsoft.com...
tables[vbcol=seagreen]
they[vbcol=seagreen]
one[vbcol=seagreen]
dave...[vbcol=seagreen]
and[vbcol=seagreen]
without[vbcol=seagreen]
but[vbcol=seagreen]
>|||> They do not need to be dbo to execute a dbo owned stored procedure. Just
> have dbo issue
> GRANT EXECUTE ON dbo.YourProcName TO DeveloperNameGoesHere
I know, the thing is though, typing out all those statements could be
regarded as work that isn't actually getting anybody anwhere that they
couldn't be just put there by flipping a switch once. There isn't a large
amount of developers, only 5 - in fact there's twice as many servers than
developers. I just thought if it was easy, then I could do it without too
much hassle.

> You can easily create a stored procedure that issues the appropriate grant
> statements to the appropriate users on all stored procedures or on a
subset
> of stored procedures based on creation date or name. You decide what you
> need and create the supporting stored procedure to do it.
That's an option - it does need repeatedly calling, i.e. every time the
database runs, though.

> Giving users the ability to create tables...I would not give them dbo
> rights for this. I would let them play in their own "sandbox" by creating
> tables under their account. When their sand castle (or table) has been
> approved then dbocan create the table within the development environment
(as
> dbo).
Don't get me wrong - I'm not the administrator. I'm a developer.
The 'sandbox' approach wouldn't really work though, because all too often
than not, we have to use the results of what another developer has done,
which is thus in his table. The 'users' of the system, as such, don't even
get to see SQL server. All they see are Excel sheets and graphs, and various
things such as crystal reports in pdf format, that are downloaded off the
servers.
Cheers|||> I know, the thing is though, typing out all those statements could be
> regarded as work that isn't actually getting anybody anwhere that they
> couldn't be just put there by flipping a switch once.
Do you use source control? Do you store your stored procedures within
script files within that source control system? If so just add the
appropriate command to the stored procedure script files. Each time the
stored procedure is compiled (created) from the script file the appropriate
grant statement(s) will be executed.

> That's an option - it does need repeatedly calling, i.e. every time the
> database runs, though.
No, it needs to be called every time a stored procedure is dropped and
created (or newly created).
The sandbox approach would only apply for a very short development cycle.
You develop a table and a stored procedure in your sandbox. You get it
working. You get your DBA to create the table and stored procedure(s) as
dbo and you are done.
Keith
"Bonj" <benjtaylor at hotpop d0t com> wrote in message
news:OqrBpjVsEHA.2128@.TK2MSFTNGP11.phx.gbl...
Just[vbcol=seagreen]
> I know, the thing is though, typing out all those statements could be
> regarded as work that isn't actually getting anybody anwhere that they
> couldn't be just put there by flipping a switch once. There isn't a large
> amount of developers, only 5 - in fact there's twice as many servers than
> developers. I just thought if it was easy, then I could do it without too
> much hassle.
>
grant[vbcol=seagreen]
> subset
you[vbcol=seagreen]
> That's an option - it does need repeatedly calling, i.e. every time the
> database runs, though.
>
creating[vbcol=seagreen]
> (as
> Don't get me wrong - I'm not the administrator. I'm a developer.
> The 'sandbox' approach wouldn't really work though, because all too often
> than not, we have to use the results of what another developer has done,
> which is thus in his table. The 'users' of the system, as such, don't even
> get to see SQL server. All they see are Excel sheets and graphs, and
various
> things such as crystal reports in pdf format, that are downloaded off the
> servers.
>
>
>
>
>
> Cheers
>

Deny permission to the object browser in query analizer

I have a sql server 2000 and i want that a user have only acess to a
determined table in a database. I don't want the user can browse the other
objects thru the object browser in the query analizer. Is that possible?
--
JLoboNo they can see objects. They can only access the objects
you grant permissions to though.
-Sue
On Wed, 15 Mar 2006 08:20:11 -0800, JLobo
<JLobo@.discussions.microsoft.com> wrote:

>I have a sql server 2000 and i want that a user have only acess to a
>determined table in a database. I don't want the user can browse the other
>objects thru the object browser in the query analizer. Is that possible?

Deny Create Database

User is an NT Administrator and has been made a member of Server Role sys
admin.
I'm not in the master database in query analyzer.
When I do this:
Deny Create Database
To User
I get the following message:
CREATE DATABASE PERMISSION CAN ONLY BE GRANTED IN THE MASTER DATABASE
How do I alter the SQL statement to refer to the master database?
Thanks for any help.Run:
use master
before you run the DENY.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Scotty" <scasti1@.cox.net> wrote in message
news:4B307CF8-97A3-41ED-9F3E-ABB130C8C525@.microsoft.com...
User is an NT Administrator and has been made a member of Server Role
sysadmin.
I'm not in the master database in query analyzer.
When I do this:
Deny Create Database
To User
I get the following message:
CREATE DATABASE PERMISSION CAN ONLY BE GRANTED IN THE MASTER DATABASE
How do I alter the SQL statement to refer to the master database?
Thanks for any help.|||Thanks, but trying to mimic a process all in one place, and the analyzer may
not be the best place to do this. I could do it from a test web page but f
amiliarizing myself with Analyzer environment.
In a web environment, I know I would have to connect to the master database
to issue this command.
However, is there an 'automated' way to switch to that database in the Query
Analyzer so that I can put the code
in the same test file rather than having to run another instance of the Anal
yzer just for that? I would rather comment out a few lines that I am not te
sting at that moment rather than switch back and forth from instance to inst
ance.
Is there a command that I can put in the body of the analyzer sql script to
switch? Sorry. Kind of new at this. All of the lines in this test file do
n't have to be run on Master anyway since they are adding users to specific
databases or they are addin
g server roles which can be added from anywhere, so if I used a separate tes
t file, it would have only these two lines in it anyway.
So is there a command to switch databases that I can place in the Analyzer s
cript? In the meantime, I'll go with your answer, and thanks very much for
your prior reply.|||If you are using Query Analyzer to run a script, then the only way to
guarantee that you are in a particular database is to issue a USE command.
So, if you are running a script from within QA, and you want to be sure you
are in master, then ensure you have the following:
use master
go
-- do something
I'm not sure I understand what the issue is. You don't need separate
instances of QA or anything like that.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Scotty" <scasti1@.cox.net> wrote in message
news:459B2889-43EB-4940-9D24-F74F7462ADCC@.microsoft.com...
Thanks, but trying to mimic a process all in one place, and the analyzer may
not be the best place to do this. I could do it from a test web page but
familiarizing myself with Analyzer environment.
In a web environment, I know I would have to connect to the master database
to issue this command.
However, is there an 'automated' way to switch to that database in the Query
Analyzer so that I can put the code
in the same test file rather than having to run another instance of the
Analyzer just for that? I would rather comment out a few lines that I am
not testing at that moment rather than switch back and forth from instance
to instance.
Is there a command that I can put in the body of the analyzer sql script to
switch? Sorry. Kind of new at this. All of the lines in this test file
don't have to be run on Master anyway since they are adding users to
specific databases or they are adding server roles which can be added from
anywhere, so if I used a separate test file, it would have only these two
lines in it anyway.
So is there a command to switch databases that I can place in the Analyzer
script? In the meantime, I'll go with your answer, and thanks very much for
your prior reply.|||I have been using separate instances of QA so I can use sa in one instanc
e to issue permissions and then see the effects of the issued permissions
by having a lower level user run sql commands in the second instance (i.e. s
econd instance logged in as
Low Level Windows user).
Thanks for the use Master go.
I'm sure it will work, and I appreciate it.
Scott|||Works great, but one thing I noticed:
Even though the commands take fine and the syspermission table record cou
nt changes as you would expect (master file), the command will not turn off
create database for a user in the sysadmin role or for a user who is not in
the sysadmin role but who i
s in the db_creator role.
So it doesn't seem to work at all. I know for a db_owner (non-sysadmin) you
have to turn on database creation after the fact, not turn it off, as I am
testing, so it does not apply in that instance.
So I'm trying to find a combination where Deny Create Database actually work
s where it would make sense. It makes sense that you might want to turn it
off for an administrator. Can't get it to work.|||Alternatively, you can send the default database to master.

>--Original Message--
>If you are using Query Analyzer to run a script, then the
only way to
>guarantee that you are in a particular database is to
issue a USE command.
>So, if you are running a script from within QA, and you
want to be sure you
>are in master, then ensure you have the following:
>use master
>go
>-- do something
>I'm not sure I understand what the issue is. You don't
need separate
>instances of QA or anything like that.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Scotty" <scasti1@.cox.net> wrote in message
>news:459B2889-43EB-4940-9D24-F74F7462ADCC@.microsoft.com...
>Thanks, but trying to mimic a process all in one place,
and the analyzer may
>not be the best place to do this. I could do it from a
test web page but
>familiarizing myself with Analyzer environment.
>In a web environment, I know I would have to connect to
the master database
>to issue this command.
>However, is there an 'automated' way to switch to that
database in the Query
>Analyzer so that I can put the code
>in the same test file rather than having to run another
instance of the
>Analyzer just for that? I would rather comment out a few
lines that I am
>not testing at that moment rather than switch back and
forth from instance
>to instance.
>Is there a command that I can put in the body of the
analyzer sql script to
>switch? Sorry. Kind of new at this. All of the lines
in this test file
>don't have to be run on Master anyway since they are
adding users to
>specific databases or they are adding server roles which
can be added from
>anywhere, so if I used a separate test file, it would
have only these two
>lines in it anyway.
>So is there a command to switch databases that I can
place in the Analyzer
>script? In the meantime, I'll go with your answer, and
thanks very much for
>your prior reply.
>|||Looks like you need to re-think your strategy. If you're in db_creator, you
can create a database. If you want to ensure they can't do it - keep them
out. A sysadmin role member can do anything, so a DENY isn't going to work
here. It's best to look at what they really need and give them only those
permissions or roles. That way, you won't need to use DENY.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Scotty" <scasti1@.cox.net> wrote in message
news:3E662297-270F-4F7B-AE8A-D0E9215E9E83@.microsoft.com...
Works great, but one thing I noticed:
Even though the commands take fine and the syspermission table record
count changes as you would expect (master file), the command will not turn
off create database for a user in the sysadmin role or for a user who is not
in the sysadmin role but who is in the db_creator role.
So it doesn't seem to work at all. I know for a db_owner (non-sysadmin) you
have to turn on database creation after the fact, not turn it off, as I am
testing, so it does not apply in that instance.
So I'm trying to find a combination where Deny Create Database actually
works where it would make sense. It makes sense that you might want to turn
it off for an administrator. Can't get it to work.