Can unpivot help me here?

HI, I have a source that looks like this:

Col1 Code1 Amt1 Col<n>.....Code2 Amt2....
ABC XY 10 FR 345

What I would like to have is this:

Col1 Code Amt Col<n> ...
ABC XY 10
ABC FR 345

I know I could achieve this by using SQL and UNION ALL:
SELECT Col1, Code1 as Code, Amt1 as Amt, Col<n>...
FROM <mySource>
UNION ALL
SELECT Col1, Code2 as Code, Amt2 as Amt, Col<n>...
FROM <mySource>

But I was wondering if the built-in unpivot transform could do the job here.

Thank you
Ccote

No, it won't. UNPIVOT would help if your data looked like this:

Col1 Col<n> XY_amt FR_amt

ABC .... 10 345

Sorry!

-Jamie

|||

All right! I think that I will stick with the good old SQL ! :-)

Thanks Jamie!

Can u pls help with the trasaction isolation levels

SET TRANSACTION ISOLATION LEVEL issue:
which one i can use in a vb code which uses openrow set to
acesss data in sqlsever and as such it is blocking other
process so which trnsaction isolation level i can specify
so that it doesnt block others and its a read only procees
thanksHi Saradhi,
You can try READ UNCOMMITED. This isolation level allows readers of data to
read uncommited (durty) records from the database.
HTH
Karl Gram
http://www.gramonline.com
"saradhi" <anonymous@.discussions.microsoft.com> wrote in message
news:dc7c01c40b3c$a1e8ac20$a501280a@.phx.gbl...
> SET TRANSACTION ISOLATION LEVEL issue:
> which one i can use in a vb code which uses openrow set to
> acesss data in sqlsever and as such it is blocking other
> process so which trnsaction isolation level i can specify
> so that it doesnt block others and its a read only procees
> thanks|||sradhi
If you can provide a little bit more info about what are you trying to
accomlish so it helps us to solve the problem
Let me say you have a SELECT statement and later you decided to update a
selected rows. You have to make sure that others users could not change the
data while you select it. Specify UPDLOCK hint which dont block others users
from reading the selected rows but you can be assured that data has not
changed since last you read it.
"saradhi" <anonymous@.discussions.microsoft.com> wrote in message
news:dc7c01c40b3c$a1e8ac20$a501280a@.phx.gbl...
> SET TRANSACTION ISOLATION LEVEL issue:
> which one i can use in a vb code which uses openrow set to
> acesss data in sqlsever and as such it is blocking other
> process so which trnsaction isolation level i can specify
> so that it doesnt block others and its a read only procees
> thanks|||thanks i will go for read uncommited
OK the problem is we have a Dell PowerEdge 2650 Server
with win2k and sql server 2003 installed and the server
has all the default configuraion settings in sql server
i am facing problem with openrow set queries used by some
of the applications which is blocking other process i
check for why it is happenning i got the bug was with
the fiber mode option which i had enabled in sql server
settings later i had removed it as my ram is 2 gb and its
bogging u p my server now its on thread mode but still
i have problems in openrowset quiroes some times blocking
other process they go into ana infinite loop even we kill
the procees the rollback goes on and on it s bug with
fiber mode option as said in one of the tech docs in tech
net ,later i have gone for reinstalling th mdac but still
now has any one faced this problem and can anyone suggest
me how to resolve it pls
thanks
saradhi

>--Original Message--
>sradhi
>If you can provide a little bit more info about what are
you trying to
>accomlish so it helps us to solve the problem
>Let me say you have a SELECT statement and later you
decided to update a
>selected rows. You have to make sure that others users
could not change the
>data while you select it. Specify UPDLOCK hint which dont
block others users
>from reading the selected rows but you can be assured
that data has not
>changed since last you read it.
>
>"saradhi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:dc7c01c40b3c$a1e8ac20$a501280a@.phx.gbl...
to
specify
procees
>
>.
>|||Saradi
<http://support.microsoft.com/direct...B;EN-US;Q224453>--
-- INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems (Q224453)
Also I would not recommend you to change the default setting. Are you aware
that your users can get dirty data which may not written to disk at all?
I'd go with reviewing your application , how does it work, how does it
access to the objects. Pehaps you have long-run queries which cause the
bloking.

"saradhi" <anonymous@.discussions.microsoft.com> wrote in message
news:b05e01c40b42$d63f8290$a601280a@.phx.gbl...
> thanks i will go for read uncommited
> OK the problem is we have a Dell PowerEdge 2650 Server
> with win2k and sql server 2003 installed and the server
> has all the default configuraion settings in sql server
> i am facing problem with openrow set queries used by some
> of the applications which is blocking other process i
> check for why it is happenning i got the bug was with
> the fiber mode option which i had enabled in sql server
> settings later i had removed it as my ram is 2 gb and its
> bogging u p my server now its on thread mode but still
> i have problems in openrowset quiroes some times blocking
> other process they go into ana infinite loop even we kill
> the procees the rollback goes on and on it s bug with
> fiber mode option as said in one of the tech docs in tech
> net ,later i have gone for reinstalling th mdac but still
> now has any one faced this problem and can anyone suggest
> me how to resolve it pls
> thanks
> saradhi
>
> you trying to
> decided to update a
> could not change the
> block others users
> that data has not
> message
> to
> specify
> procees

Can this stored procedure be optimised?

Hi there,
I hoping someone can help me reduce the number of line of code I'm
using, as these IF's are nested inside a bigger one.
The main problem I have is I need to add another variable to the IF and
don't want to copy and paste and make this statement even larger.
I have tried playing about with EXEC but with no joy. As far as I'm
aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
CSPerson > 0
I'm using SQL Server 2000 SP4.
Hopefully I'm missing the obvious, although any help or suggestions are
welcome.
-- snippet --
IF @.CSOrder > 0 AND @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE IF @.CSOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
ORDER BY DateToArrive, Status
ELSE IF @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
ORDER BY DateToArrive, StatusWhat you can do is something like this.
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = CSAE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
END
AND SalesPerson = CASE @.SalesOrder WHEN 0 THEN SalesPerson ELSE
@.SalesOrder END
AND ... -- add more
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status|||Mia,
Try something like this
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder AND SalesPerson = @.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
@.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
EXEC (@.sSQL)
Hope this assists,
Tony
"mia_cid@.hotmail.com" wrote:

> Hi there,
> I hoping someone can help me reduce the number of line of code I'm
> using, as these IF's are nested inside a bigger one.
> The main problem I have is I need to add another variable to the IF and
> don't want to copy and paste and make this statement even larger.
> I have tried playing about with EXEC but with no joy. As far as I'm
> aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
> CSPerson > 0
> I'm using SQL Server 2000 SP4.
> Hopefully I'm missing the obvious, although any help or suggestions are
> welcome.
> -- snippet --
> IF @.CSOrder > 0 AND @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.CSOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> ORDER BY DateToArrive, Status
>|||Ooops,
take the following line out of the code:::
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
it was an oversight on my part, sorry,
Tony
"Tony Scott" wrote:
> Mia,
> Try something like this
>
> DECLARE @.strWHERE varchar(200)
> SET @.strWHERE = ''
> IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder AND SalesPerson = @.SalesOrder'
> IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder'
> IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
> @.SalesOrder'
> DECLARE @.sSQL varchar(2000)
> SET @.sSQL = ''
> SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
> SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
> SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
> SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
> SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
> SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
> SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
> SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
> SET @.sSQL = @.sSQL + @.strWHERE
> SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
> SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
> EXEC (@.sSQL)
>
> Hope this assists,
> Tony
> "mia_cid@.hotmail.com" wrote:
>|||Thanks for the help guys,
Patrik I liked your idea, only I need:
AND CSPerson = CASE @.CSOrder WHEN > 0 THEN CSPerson ELSE @.CSOrder
Not
AND CSPerson = CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
Or
AND CSPerson >= CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
As if @.CSOrder = 0, then I want to select everything, so CSPerson needs
to be greater than 0.
Tony,
I had a couple of errors with your original code, so now have:
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder AND SalesPerson =
@.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND SalesPerson = @.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] =
AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] =
MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' ORDER BY DateToArrive, Status'
EXEC (@.sSQL)
Only I've getting a syntax error near the keyword 'ELSE'. If I comment
this code out and put a standard SELECT in it works okay.
Any ideas?|||You can do that.
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN CSPerson ELSE @.CSOrder END
Although, it would probably be more efficient with dynamic sql in this
case.
I suggest that you use sp_executesql instaead of exec.
Read sommarskogs really good article about using dynamic sql.
http://www.sommarskog.se/dynamic_sql.html|||Thanks for that Partrik,
I just needed it the other way round:
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN @.CSOrder ELSE CSPerson END
It works a treat, for the time being I'm going to stick with it as
it'll be running over a LAN, have bookmarked the site you recommended
and will have a read when I get the chance.
Thanks again to Tony for his help as well.

Can this be done with a single query?

Colin,
Pivot operations is exactly what I needed. I applied the pivot type code aga
inst my real
SQL 2000 table and it is doing what I was looking for.
Thanks you very much.
Gaetan
On Sun, 26 Mar 2006 13:09:12 GMT, "Colin Dawson" <newsgroups@.cjdawson.com> w
rote:

>This is a simple pivot operation.
>In SQL 2000 it'll work like this.
>Select
>s.student
>From (
>Select
>student,
>max( case when subject = 'math' then grade end ) math,
>max( case when subject = 'physics' then grade end ) physics,
>max( case when subject = 'chemistry' then grade end ) chemistry
>From #student
>Group by student
> ) s
>where s.math >= 85
>and s.physics >= 85
>and s.chemistry >= 85
>
>and in SQL 2005 it works like this.
>Select
>student
>From #student s
>Pivot ( Max( s.grade ) For s.subject in ( [math], [physics], [chemistry] ) )
>as pvt
>where pvt.math >= 85
>and pvt.physics >= 85
>and pvt.chemistry >= 85
>
>The execution plan for the SQL2005 example is slightly more efficient, but
>only be removing one compute scaler from the plan. All in all, that's not
>an issue.
>
>Regards
>Colin Dawson
>www.cjdawson.com
>
>"Gaetan" <me@.somewhere.com> wrote in message
> news:m9sb221pmojvn8t9m368gnopdshmjti023@.
4ax.com...
>thx.
Colin.

can this be done with a check constraint?

i have a zip code table downloaded from the usps in excel and imported
into a sql database table.
zipcode is not a primary key in the table, as it lists all the cities
and aliases for cities that include each zipcode.
however there is a rule in the data:
for any given zipcode, only ONE row of data can have a citytype value
of 'D'
what i am wondering is how i can turn that rule into a database level
constraint. i can't create a unique constraint on zipcode + citytype,
because any number of rows for the same zipcode can have citytype N and
A, for example. it is only the D value that can appear only once per
zipcode.
is this something a check constraint can accomplish? i'm new to check
constraints so i'm not sure what that constraint rule would look like.
it seems more complicated than "thisfield > thatfield" type logic.
thanks,
jasonI think this is beyond the capabilities of a check constraint.
A Check constraint is an expression which is used to check that the contents
of a column conform to a given rule. The constraint cannot reference
information on a different row of data than the one which is being checked.
You may be able to accomplish your scenario using a trigger. There are
times however, then the integrity checking would be so expensive that it's
simply more efficient to push said rule to a different business logic layer.
Colin.
"jason" <iaesun@.yahoo.com> wrote in message
news:1142884753.165122.99270@.u72g2000cwu.googlegroups.com...
>i have a zip code table downloaded from the usps in excel and imported
> into a sql database table.
> zipcode is not a primary key in the table, as it lists all the cities
> and aliases for cities that include each zipcode.
> however there is a rule in the data:
> for any given zipcode, only ONE row of data can have a citytype value
> of 'D'
> what i am wondering is how i can turn that rule into a database level
> constraint. i can't create a unique constraint on zipcode + citytype,
> because any number of rows for the same zipcode can have citytype N and
> A, for example. it is only the D value that can appear only once per
> zipcode.
> is this something a check constraint can accomplish? i'm new to check
> constraints so i'm not sure what that constraint rule would look like.
> it seems more complicated than "thisfield > thatfield" type logic.
> thanks,
> jason
>|||Jason,
2 ways to accomplish that:
1. google up "nullbuster" and create an index on computed columns
2. create an indexed view for
select zipcode from mytable where citytype = 'D'
Good luck!sql

Can the .mdb file (include VBA code) be indexed?

Hi GURU, I wonder if the code inside the .mdb or .adp file can be indexed? if
yes, what iFilter can do that? Thanks.
Raymond,
If memory servers me correctly, both of those file extensions are Microsoft
Access related. Assuming so, I do not believe that there are any known /
public IFilters for this files types. However, the IFilter API is well
known, and if you know the internal structure of these MS propriety file
formats, then by all means write your own! <g>
-- John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Raymond" <Raymond@.discussions.microsoft.com> wrote in message
news:015B1CD4-7874-464E-B154-CAA1201583B4@.microsoft.com...
> Hi GURU, I wonder if the code inside the .mdb or .adp file can be indexed?
> if
> yes, what iFilter can do that? Thanks.

Can stored procs run after handle is closed?

I have written a stored proceedure for MSSQL that needs to run for hours at
a time. I need to execute it from C++ code. The current code does:

nRet = SQLDIRECTEXEC(hstmt, "exec stored_proc", SQL_NTS)

followed shortly after by a

Free_Stmt_Handle(hstmt) //roughly

The stored proc currently dies with the statement handle, not fully
populating the table I need it to.

I need to either know when the proc finishes so I can close the handle after
that, or allow the proc to run independently on the server no matter what
the program is doing (is exited, etc), either of these is fine.

Please Help! Thanks in advance!
JosephI know nothing about C++, but if the proc runs for a very long time, it
might be better to implement it as a scheduled job. The client could
set a flag or insert a row into a 'queue' table, then you have a job
which runs every few minutes or whatever, and if the flag is set, it
then starts the stored proc.

Simon|||That is an interesting approach, ideally I would like to stay as far away
from the database as I can but it sounds like this could be the best way...
my stored procedure is running for the exact same number of instructions and
then dying, whereas if I run it via Query Analyzer it runs to completion.

I finally caved and just copy-pasted from Q.Analyzer into code to confirm
this. I will investigate a little further before taking that plunge.

Thanks
Joseph

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:1112950699.762977.19700@.o13g2000cwo.googlegro ups.com...
>I know nothing about C++, but if the proc runs for a very long time, it
> might be better to implement it as a scheduled job. The client could
> set a flag or insert a row into a 'queue' table, then you have a job
> which runs every few minutes or whatever, and if the flag is set, it
> then starts the stored proc.
> Simon

Can someone tell me whats wrong with this code?

Hi All

I keep getting back VINET and not Lilas...can someone point me in the
right direction?

Thanks a lot

DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT
<CustomerID>VINET </CustomerID
<CustomerID>Lilas</CustomerID
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT',2)
WITH (

CustomerID varchar(100) )I'm not an expert on SQL Server and XML, but it seems to me that
OPENXML is very picky about the XML form that it will accept; can you
modify your XML statement to read like this?

SET @.doc ='
<ROOT>
<Customer>
<CustomerID>VINET </CustomerID>
</Customer>
<Customer>
<CustomerID>Lilas</CustomerID>
</Customer>
</ROOT>'

and then your OPENXML to statement to read like this:

SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',2)
WITH (
CustomerID varchar(100) )

Don't know why it works, but it does.

Stu|||Try this...

DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT
<CustomerID>VINET </CustomerID
<CustomerID>Lilas</CustomerID
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/CustomerID',2)
WITH (

CustomerID varchar(100) '.')

EXEC sp_xml_removedocument @.iDoc|||Thank you Mark that works great!!!!
Can you explain to my why '.' needed to be included? I can't seem to
locate it in Books online...

Thanks again|||(reezaali@.gmail.com) writes:
> Thank you Mark that works great!!!!
> Can you explain to my why '.' needed to be included? I can't seem to
> locate it in Books online...

Books Online says about col-pattern:

Is an optional, general XPath pattern that describes how the XML nodes
should be mapped to the columns. If the ColPattern is not specified, the
default mapping (attribute-centric or element-centric mapping as
specified by flags) takes place.

'.' maps to the current node, which is /Root/CustomerID.

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

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

can someone please tell me what this dql code does.

DECODE
(measureid,
'TOTD', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
'CDL', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT NVL (SUM (0.5), 0)
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
AND ele.empl_trt_id IN ('PH', 'WEND')),
'DNR', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT COUNT (empl_id) / 2
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_origin_id = 'T'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
measurevalue
) measurevalueOracle's DECODE is similar in functionality to the ANSI-standard CASE
expression used in SQL Server. Different values are returned depending on
if the measureid value is 'TODT', 'CDL' or 'DNR'.
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:7067dc28f849e@.uwe...
> DECODE
> (measureid,
> 'TOTD', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
> 'CDL', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
> 'DNR', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT COUNT (empl_id) / 2
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_origin_id = 'T'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
> measurevalue
> ) measurevalue
>|||dwynenelson880 wrote:

> 'CDL', ...
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
>
my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
correct?
because i think that the result will aways be 0.5
if someone can explain this to me it would be very helpfull.
thank you|||> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
return NULL. I think the purpose of NVL here is to return 0 instead of
NULL. In SQL Server, you can use the ANSI standard COALESCE function for
this purpose.
For questions are specific to Oracle, you are better off posting to an
Oracle forum. However, we might be able to help you convert Oracle code to
SQL Server here.
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:70683db28c33e@.uwe...
> dwynenelson880 wrote:
>
> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
> if someone can explain this to me it would be very helpfull.
> thank you
>|||>If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
>return NULL. I think the purpose of NVL here is to return 0 instead of
>NULL. In SQL Server, you can use the ANSI standard COALESCE function for
>this purpose.
>
thank you very much Dan
i finaly understand it now. men that was really helpfull. surry for posting
in the sql forum. next time i will know better.

can someone please tell me what this dql code does.

DECODE
(measureid,
'TOTD', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
'CDL', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT NVL (SUM (0.5), 0)
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
AND ele.empl_trt_id IN ('PH', 'WEND')),
'DNR', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT COUNT (empl_id) / 2
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_origin_id = 'T'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
measurevalue
) measurevalue
Oracle's DECODE is similar in functionality to the ANSI-standard CASE
expression used in SQL Server. Different values are returned depending on
if the measureid value is 'TODT', 'CDL' or 'DNR'.
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:7067dc28f849e@.uwe...
> DECODE
> (measureid,
> 'TOTD', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
> 'CDL', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
> 'DNR', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT COUNT (empl_id) / 2
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_origin_id = 'T'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
> measurevalue
> ) measurevalue
>
|||dwynenelson880 wrote:

> 'CDL', ...
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
>
my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
correct?
because i think that the result will aways be 0.5
if someone can explain this to me it would be very helpfull.
thank you
|||> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
return NULL. I think the purpose of NVL here is to return 0 instead of
NULL. In SQL Server, you can use the ANSI standard COALESCE function for
this purpose.
For questions are specific to Oracle, you are better off posting to an
Oracle forum. However, we might be able to help you convert Oracle code to
SQL Server here.
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:70683db28c33e@.uwe...
> dwynenelson880 wrote:
>
> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
> if someone can explain this to me it would be very helpfull.
> thank you
>
|||>If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
>return NULL. I think the purpose of NVL here is to return 0 instead of
>NULL. In SQL Server, you can use the ANSI standard COALESCE function for
>this purpose.
>
thank you very much Dan
i finaly understand it now. men that was really helpfull. surry for posting
in the sql forum. next time i will know better.

can someone please tell me what this dql code does.

DECODE
(measureid,
'TOTD', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
'CDL', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT NVL (SUM (0.5), 0)
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
AND ele.empl_trt_id IN ('PH', 'WEND')),
'DNR', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT COUNT (empl_id) / 2
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_origin_id = 'T'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
measurevalue
) measurevalueOracle's DECODE is similar in functionality to the ANSI-standard CASE
expression used in SQL Server. Different values are returned depending on
if the measureid value is 'TODT', 'CDL' or 'DNR'.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:7067dc28f849e@.uwe...
> DECODE
> (measureid,
> 'TOTD', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
> 'CDL', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
> 'DNR', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT COUNT (empl_id) / 2
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_origin_id = 'T'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
> measurevalue
> ) measurevalue
>|||dwynenelson880 wrote:
> 'CDL', ...
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
>
my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
correct?
because i think that the result will aways be 0.5
if someone can explain this to me it would be very helpfull.
thank you|||> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
return NULL. I think the purpose of NVL here is to return 0 instead of
NULL. In SQL Server, you can use the ANSI standard COALESCE function for
this purpose.
For questions are specific to Oracle, you are better off posting to an
Oracle forum. However, we might be able to help you convert Oracle code to
SQL Server here.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:70683db28c33e@.uwe...
> dwynenelson880 wrote:
>
>> 'CDL', ...
>> - (SELECT NVL (SUM (0.5), 0)
>> FROM emp_log_entries ele
>> WHERE empl_emp_id = employeeid
>> AND ele.empl_status = 'A'
>> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
>> AND ele.empl_trt_id IN ('PH', 'WEND')),
> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
> if someone can explain this to me it would be very helpfull.
> thank you
>|||>If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
>return NULL. I think the purpose of NVL here is to return 0 instead of
>NULL. In SQL Server, you can use the ANSI standard COALESCE function for
>this purpose.
>
thank you very much Dan
i finaly understand it now. men that was really helpfull. surry for posting
in the sql forum. next time i will know better.

can someone check this code for a nOOb

I've managed to get the following code to do 99% of what I want it to:

SELECT tbl3.col1, tbl3.col11, tbl3.col2, tbl3.col3, tbl3.col4, tbl3.col5, tbl3.col6, tbl3.col7, tbl3.col8, tbl3.col13
FROM tbl3
WHERE ((tbl3.col13 = 1) AND ((tbl3.col11 = colcheck) AND (tbl3.col2 LIKE '%colname%' AND tbl3.col6 LIKE '%colcity%' AND tbl3.col7 LIKE '%colstate%' AND tbl3.col4 LIKE '%coladdress%' AND tbl3.col8 LIKE '%colzip%')))
ORDER BY tbl3.col2 ASC

It for an advanced search form. col13 is set manually in the code, col11 is a radio button, and the rest of the fields are optional. It's for finding contacts in a table. I have colname which is the company name, but also a want to check it against col3 (not only col2). Same with coladdress, I'd like to check it against col5 (not only col4). Does anyone know how to accomplish this?can you use better column names and resend

:)

can somebody help my optimise my code please

Hi

I have the following SQL, which I am using to query our mainframe (some kind of IBM DB2 type thing)
Basically there are two tables joined by account number. One contains info about the customer, and the other contains details about transactions that have happened for each month. I am trying to get the total spend for last month and the month before. (I know I am not using anything from the CUS_ACC table at the mo, but I will need to)

SELECT PROD.CUST_ACC_NUM, Sum(SPEND) AS SUMSPEND, STMT_MONTH, STMT_YEAR
FROM PROD INNER JOIN CUST_ACC ON PROD.CUST_ACC_NUM=CUST_ACC.CUST_ACC_NUM

WHERE PROD.CUST_ACC_NUM=630822
And
(
(STMT_MONTH=Month(DateAdd("m",-1,Date())) And STMT_YEAR=Year(DateAdd("m",-1,Date())))
Or
(STMT_MONTH=Month(DateAdd("m",-2,Date())) And STMT_YEAR=Year(DateAdd("m",-2,Date())))
)
GROUP BY PROD.CUST_ACC_NUM, STMT_MONTH, STMT_YEAR;

now, as the query is at the moment, it takes tens of minutes to run for a single account (I will have to do it for about 3,000 accounts). However, if remove the line that starts with 'OR' and the line below it (i.e. only get last months sales) it runs in seconds.

Any ideas why?
Thanks
Kevinforget it, its working at full speed. hmmmmmm, very very strange

Can report parameter type be determined in code?

SSRS 2005
OK, I almost have this figured out.
I have a custom assembly. In the OnInit() method of the report I instantiate
my class and pass a reference to the report's Parameters collection to my
custom class.
In my custom class I then access the Parameters collection to determine the
report parameter values entered by the user. I can then output the parameter
values to a textbox in my report using an expression like
=Code.RptLib.GetParamValues().
The problem I have now is I need to be able to figure out the data type of
each report parameter so I can format the values properly. For example Dates
need to be formatted differently from Floats.
So, how do I figure out the data type of each report parameter by inspecting
the Parameters collection?
I am guessing the answer is that I can't and that I should use the web
service, but I don't want to jump through those hoops and I thought it was
worth asking if there is an easier way.
-- Chris
--
Chris, SSSIHello Chris,
Since the ReportObjectModel does not expose the interface of datatype, you
could not access it.
I would like to know whether your application could access the DOM object
of your report. If so, then you could access the Datatype.
I will also send your feedback to the product team to check whether they
will consider to expose more interface for developer to access the DataType.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hey Chris - out of curiousity, why do you need to go this route to show the
parameters on the report since obviously, you can just =@.param1 in the
textbox expression on the report itself?
=-Chris
"Chris G." <ChrisG@.nospam.nospam> wrote in message
news:FE87C610-CF3A-4FE8-8247-B7338F4C9DA4@.microsoft.com...
> SSRS 2005
> OK, I almost have this figured out.
> I have a custom assembly. In the OnInit() method of the report I
> instantiate
> my class and pass a reference to the report's Parameters collection to my
> custom class.
> In my custom class I then access the Parameters collection to determine
> the
> report parameter values entered by the user. I can then output the
> parameter
> values to a textbox in my report using an expression like
> =Code.RptLib.GetParamValues().
> The problem I have now is I need to be able to figure out the data type of
> each report parameter so I can format the values properly. For example
> Dates
> need to be formatted differently from Floats.
> So, how do I figure out the data type of each report parameter by
> inspecting
> the Parameters collection?
> I am guessing the answer is that I can't and that I should use the web
> service, but I don't want to jump through those hoops and I thought it was
> worth asking if there is an easier way.
> -- Chris
>
> --
> Chris, SSSI|||Hey Chris,
If you look in the reporting services database, in the Catalog table,
there's a column called Parameters. That column contains an XML
formatted expression describing each of the parameters attached to a
report. Probably not the best method, but you could extract the
parameter datatype from that column.
Evan
Chris G. wrote:
> SSRS 2005
> OK, I almost have this figured out.
> I have a custom assembly. In the OnInit() method of the report I instantiate
> my class and pass a reference to the report's Parameters collection to my
> custom class.
> In my custom class I then access the Parameters collection to determine the
> report parameter values entered by the user. I can then output the parameter
> values to a textbox in my report using an expression like
> =Code.RptLib.GetParamValues().
> The problem I have now is I need to be able to figure out the data type of
> each report parameter so I can format the values properly. For example Dates
> need to be formatted differently from Floats.
> So, how do I figure out the data type of each report parameter by inspecting
> the Parameters collection?
> I am guessing the answer is that I can't and that I should use the web
> service, but I don't want to jump through those hoops and I thought it was
> worth asking if there is an easier way.
> -- Chris
>
> --
> Chris, SSSI|||Hi Chris!
Thank you for replying to one of my posts again. I appreciate the input!
>>why do you need to go this route to show the parameters on the report since
>>obviously, you can just =@.param1 in the textbox expression on the report itself?
What I am trying to do is develop a generic approach to output the
parameters for ANY report. My report template for new reports will have all
the logic built into it to automatically output the parameters for the
report. Here is my approach so far:
1. OnInit() in my report instantiates a class in my custom assembly and
passes it a reference to the Parameters global collection. That way my custom
assembly can access the Parameters collection.
2. I have a table in my report which uses an XML data source. The XML
dataset is provided by a function in my custom assembly:
=Code.RptLib.ReportParametersXML. ReportParametersXML loops through the
Parameters collection and builds XML containing the parameter prompts and
values (this also requires defining the parameter prompts in a hidden report
parameter since they are not accessible from the object model) which is
output by the table. So I have two columns in my report. Left column has the
parameter prompts. Right column has the parameter values. ReportParametersXML
automatically handles formatting Single Value and MultiValue parameters (you
can figure that out from the object model). What I can't to is get the
parameter type to know if I am formatting a Date, Integer, Float, etc.
Eventually we will be building custom report parameter pages for our
reports. When we get to that I will be using the web service to get the
parameter definitions and then will have access to the parameter data types
and will be able to pass that information into the report.
However for this release of our project, we are relying on Reporting
Services to generate the report parameter controls. So I was looking for a
short term way to figure out the report parameter types from within the
report (which to be honest I think is a reasonable thing to want to do).
Looks like it is not possible. So since I have to tell the report the
parameter prompts anyway (eventually this will come from the web service
anyway) I can also just define the parameter types.
Hope that made sense.
-- Chris
Chris, SSSI
"Chris Conner" wrote:
> Hey Chris - out of curiousity, why do you need to go this route to show the
> parameters on the report since obviously, you can just =@.param1 in the
> textbox expression on the report itself?
> =-Chris
>
> "Chris G." <ChrisG@.nospam.nospam> wrote in message
> news:FE87C610-CF3A-4FE8-8247-B7338F4C9DA4@.microsoft.com...
> > SSRS 2005
> >
> > OK, I almost have this figured out.
> >
> > I have a custom assembly. In the OnInit() method of the report I
> > instantiate
> > my class and pass a reference to the report's Parameters collection to my
> > custom class.
> >
> > In my custom class I then access the Parameters collection to determine
> > the
> > report parameter values entered by the user. I can then output the
> > parameter
> > values to a textbox in my report using an expression like
> > =Code.RptLib.GetParamValues().
> >
> > The problem I have now is I need to be able to figure out the data type of
> > each report parameter so I can format the values properly. For example
> > Dates
> > need to be formatted differently from Floats.
> >
> > So, how do I figure out the data type of each report parameter by
> > inspecting
> > the Parameters collection?
> >
> > I am guessing the answer is that I can't and that I should use the web
> > service, but I don't want to jump through those hoops and I thought it was
> > worth asking if there is an easier way.
> >
> > -- Chris
> >
> >
> >
> > --
> > Chris, SSSI
>
>|||Chris,
I have seen you use this syntax in another post also:
=@.param1
Is this your way of indicating a parameter from the Parameters collection?
The SSRS documentation mentions these supported syntaxes:
Collection!ObjectName
=User!Language
Collection.Item("ObjectName")
=User.Item("Language")
Collection("ObjectName")
=User("Language")
But I have never seen =@.param1 as a supported syntax.
Is that a 4th alternative or is that just your own shorthand?
-- Chris
--
Chris, SSSI
"Chris Conner" wrote:
> Hey Chris - out of curiousity, why do you need to go this route to show the
> parameters on the report since obviously, you can just =@.param1 in the
> textbox expression on the report itself?
> =-Chris
>
> "Chris G." <ChrisG@.nospam.nospam> wrote in message
> news:FE87C610-CF3A-4FE8-8247-B7338F4C9DA4@.microsoft.com...
> > SSRS 2005
> >
> > OK, I almost have this figured out.
> >
> > I have a custom assembly. In the OnInit() method of the report I
> > instantiate
> > my class and pass a reference to the report's Parameters collection to my
> > custom class.
> >
> > In my custom class I then access the Parameters collection to determine
> > the
> > report parameter values entered by the user. I can then output the
> > parameter
> > values to a textbox in my report using an expression like
> > =Code.RptLib.GetParamValues().
> >
> > The problem I have now is I need to be able to figure out the data type of
> > each report parameter so I can format the values properly. For example
> > Dates
> > need to be formatted differently from Floats.
> >
> > So, how do I figure out the data type of each report parameter by
> > inspecting
> > the Parameters collection?
> >
> > I am guessing the answer is that I can't and that I should use the web
> > service, but I don't want to jump through those hoops and I thought it was
> > worth asking if there is an easier way.
> >
> > -- Chris
> >
> >
> >
> > --
> > Chris, SSSI
>
>|||Hi Evan,
Interesting suggestion. :-)
My only concern is, per Microsoft, you are not supposed to access the DB
directly because the DB schema is subject to change (without notice) in
future releases.
Still, a creative solution.
Overall, the thing is, I am looking for a high performance solution. I could
also use the web service to get the parameter definitions, or inspect the
.rdl file for the report. Both have also been suggested to me. It just seems
silly to me to have to use one of those more complex approaches so that the
report can find out about itself! ;-) Follow what I am saying? Because of
current limitations in the report object model, the report has to "query
itself" via an external approach (the web service or .rdl file from which it
was instantiated). Seems like jumping through hoops to me.
The problem ;-) is I have been spoiled by the Actuate reporting system in
which you work with a full object and event driven programming model...I am
trying to replicate functionality in SSRS that is trivial to build using
Actuate (though I won't get into how much more $$$ Actuate costs over SSRS).
Anyway, I guess I am just still trying to learn how to think like an SSRS
developer. The paradigm shift is a little rough. ;-)
-- Chris
Chris, SSSI
"emorgoch" wrote:
> Hey Chris,
> If you look in the reporting services database, in the Catalog table,
> there's a column called Parameters. That column contains an XML
> formatted expression describing each of the parameters attached to a
> report. Probably not the best method, but you could extract the
> parameter datatype from that column.
> Evan
> Chris G. wrote:
> > SSRS 2005
> >
> > OK, I almost have this figured out.
> >
> > I have a custom assembly. In the OnInit() method of the report I instantiate
> > my class and pass a reference to the report's Parameters collection to my
> > custom class.
> >
> > In my custom class I then access the Parameters collection to determine the
> > report parameter values entered by the user. I can then output the parameter
> > values to a textbox in my report using an expression like
> > =Code.RptLib.GetParamValues().
> >
> > The problem I have now is I need to be able to figure out the data type of
> > each report parameter so I can format the values properly. For example Dates
> > need to be formatted differently from Floats.
> >
> > So, how do I figure out the data type of each report parameter by inspecting
> > the Parameters collection?
> >
> > I am guessing the answer is that I can't and that I should use the web
> > service, but I don't want to jump through those hoops and I thought it was
> > worth asking if there is an easier way.
> >
> > -- Chris
> >
> >
> >
> > --
> > Chris, SSSI
>|||Wei Lu,
As always, thank you for your quick reply! :-)
>>Since the ReportObjectModel does not expose the interface of datatype, you
>>could not access it.
OK that is what I thought. I just wanted to make sure I was not overlooking
something.
>>I would like to know whether your application could access the DOM object
>>of your report. If so, then you could access the Datatype.
Do you mean loading the .rdl file and accessing the parameters node?
>>I will also send your feedback to the product team to check whether they
>>will consider to expose more interface for developer to access the DataType.
Thanks!
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chris,
> Since the ReportObjectModel does not expose the interface of datatype, you
> could not access it.
> I would like to know whether your application could access the DOM object
> of your report. If so, then you could access the Datatype.
> I will also send your feedback to the product team to check whether they
> will consider to expose more interface for developer to access the DataType.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Ack I apologize - I was getting lazy!
I am used to using @.param1 in the data tab... but when you access it via the
layout tab you should use the options you have already mentioned. :)
I.e. =Parameters!param1.value
Anyways, I wanted to say that we have a wizard tool we have build that takes
all the parameters for ANY report and prompts the user, for those values -
but we used the reporting services web service to get this information.
this also allowed us to create specialized parameters that signified whether
we wanted our wizard to show the parameter as a multi-selection list as
opposed to just a combo drop down box, or dates that automatically have the
beginning year or beginning month, or beginning day auto filled - the same
principle for an end date parameter as well.
I find it "funny" how we are doing the same thing.
Mine is for windows based applications (thick clients) - but you are using
it for web forms.
Anyways, you will succeed in your endeavor - but you won't be able to get
the parameter type until you get to the web service unfortunately. :)
=-Chris
"Chris G." <ChrisG@.nospam.nospam> wrote in message
news:F444CEE3-AB58-4F41-9C18-3BEF66525E1A@.microsoft.com...
> Chris,
> I have seen you use this syntax in another post also:
> =@.param1
> Is this your way of indicating a parameter from the Parameters collection?
> The SSRS documentation mentions these supported syntaxes:
> Collection!ObjectName
> =User!Language
> Collection.Item("ObjectName")
> =User.Item("Language")
> Collection("ObjectName")
> =User("Language")
> But I have never seen =@.param1 as a supported syntax.
> Is that a 4th alternative or is that just your own shorthand?
> -- Chris
> --
> Chris, SSSI
>
> "Chris Conner" wrote:
>> Hey Chris - out of curiousity, why do you need to go this route to show
>> the
>> parameters on the report since obviously, you can just =@.param1 in the
>> textbox expression on the report itself?
>> =-Chris
>>
>> "Chris G." <ChrisG@.nospam.nospam> wrote in message
>> news:FE87C610-CF3A-4FE8-8247-B7338F4C9DA4@.microsoft.com...
>> > SSRS 2005
>> >
>> > OK, I almost have this figured out.
>> >
>> > I have a custom assembly. In the OnInit() method of the report I
>> > instantiate
>> > my class and pass a reference to the report's Parameters collection to
>> > my
>> > custom class.
>> >
>> > In my custom class I then access the Parameters collection to determine
>> > the
>> > report parameter values entered by the user. I can then output the
>> > parameter
>> > values to a textbox in my report using an expression like
>> > =Code.RptLib.GetParamValues().
>> >
>> > The problem I have now is I need to be able to figure out the data type
>> > of
>> > each report parameter so I can format the values properly. For example
>> > Dates
>> > need to be formatted differently from Floats.
>> >
>> > So, how do I figure out the data type of each report parameter by
>> > inspecting
>> > the Parameters collection?
>> >
>> > I am guessing the answer is that I can't and that I should use the web
>> > service, but I don't want to jump through those hoops and I thought it
>> > was
>> > worth asking if there is an easier way.
>> >
>> > -- Chris
>> >
>> >
>> >
>> > --
>> > Chris, SSSI
>>|||The only downside to this approach - you will have to also know the path
that your report was executed from from the report server - because if you
have two reports with the same name, they would more than likely have
different parameters.
I.e.
/Custom/Year To Date
/My Reports/Testing/Year To Date
Above are two reports on the report server, I would see in the catalog table
two rows for "Year To Date". When I execute this report, in order for me to
get the right parameter list from the catalog table, I would have to know
which path as well - not just the name of my own report that is executing.
You CAN do it this way, but you should also get the Path.
Chris - I know Microsoft says the schema is subject to change - so use a
view - if they change the schema, you can always update the view.
Better option: The web service... then you won't care if they change the
schema.
=-Chris
"emorgoch" <emorgoch.public@.gmail.com> wrote in message
news:1163777316.972748.135970@.f16g2000cwb.googlegroups.com...
> Hey Chris,
> If you look in the reporting services database, in the Catalog table,
> there's a column called Parameters. That column contains an XML
> formatted expression describing each of the parameters attached to a
> report. Probably not the best method, but you could extract the
> parameter datatype from that column.
> Evan
> Chris G. wrote:
>> SSRS 2005
>> OK, I almost have this figured out.
>> I have a custom assembly. In the OnInit() method of the report I
>> instantiate
>> my class and pass a reference to the report's Parameters collection to my
>> custom class.
>> In my custom class I then access the Parameters collection to determine
>> the
>> report parameter values entered by the user. I can then output the
>> parameter
>> values to a textbox in my report using an expression like
>> =Code.RptLib.GetParamValues().
>> The problem I have now is I need to be able to figure out the data type
>> of
>> each report parameter so I can format the values properly. For example
>> Dates
>> need to be formatted differently from Floats.
>> So, how do I figure out the data type of each report parameter by
>> inspecting
>> the Parameters collection?
>> I am guessing the answer is that I can't and that I should use the web
>> service, but I don't want to jump through those hoops and I thought it
>> was
>> worth asking if there is an easier way.
>> -- Chris
>>
>> --
>> Chris, SSSI
>|||Hello Chris,
Yes, I mean you need to load the rdl file and access the paramenters node.
I understand that this may be more complex than the object model but for
now this is the most usable approach in your project.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Chris,
>>Ack I apologize - I was getting lazy!
No problemo. I just wanted to be sure that I wasn't missing something. :-)
>>Anyways, I wanted to say that we have a wizard tool we have build...
Sounds pretty cool! I think in another post you mentioned that for ownership
reasons you would not be able to share that code. Any thoughts about
commercializing it? ;-)
>>I find it "funny" how we are doing the same thing.
I agree. It would also be great if Microsoft would just build this kind of
capability into the product! :-) I am sure we are not the only developers
facing and solving this problem.
>>Anyways, you will succeed in your endeavor - but you won't be able to get
>>the parameter type until you get to the web service unfortunately. :)
I am with you on that! Eventually...
-- Chris
--
Chris, SSSI
"Chris Conner" wrote:
> Ack I apologize - I was getting lazy!
> I am used to using @.param1 in the data tab... but when you access it via the
> layout tab you should use the options you have already mentioned. :)
> I.e. =Parameters!param1.value
> Anyways, I wanted to say that we have a wizard tool we have build that takes
> all the parameters for ANY report and prompts the user, for those values -
> but we used the reporting services web service to get this information.
> this also allowed us to create specialized parameters that signified whether
> we wanted our wizard to show the parameter as a multi-selection list as
> opposed to just a combo drop down box, or dates that automatically have the
> beginning year or beginning month, or beginning day auto filled - the same
> principle for an end date parameter as well.
> I find it "funny" how we are doing the same thing.
> Mine is for windows based applications (thick clients) - but you are using
> it for web forms.
> Anyways, you will succeed in your endeavor - but you won't be able to get
> the parameter type until you get to the web service unfortunately. :)
> =-Chris
>
> "Chris G." <ChrisG@.nospam.nospam> wrote in message
> news:F444CEE3-AB58-4F41-9C18-3BEF66525E1A@.microsoft.com...
> > Chris,
> >
> > I have seen you use this syntax in another post also:
> > =@.param1
> >
> > Is this your way of indicating a parameter from the Parameters collection?
> >
> > The SSRS documentation mentions these supported syntaxes:
> >
> > Collection!ObjectName
> > =User!Language
> >
> > Collection.Item("ObjectName")
> > =User.Item("Language")
> >
> > Collection("ObjectName")
> > =User("Language")
> >
> > But I have never seen =@.param1 as a supported syntax.
> >
> > Is that a 4th alternative or is that just your own shorthand?
> >
> > -- Chris
> >
> > --
> > Chris, SSSI
> >
> >
> > "Chris Conner" wrote:
> >
> >> Hey Chris - out of curiousity, why do you need to go this route to show
> >> the
> >> parameters on the report since obviously, you can just =@.param1 in the
> >> textbox expression on the report itself?
> >>
> >> =-Chris
> >>
> >>
> >>
> >> "Chris G." <ChrisG@.nospam.nospam> wrote in message
> >> news:FE87C610-CF3A-4FE8-8247-B7338F4C9DA4@.microsoft.com...
> >> > SSRS 2005
> >> >
> >> > OK, I almost have this figured out.
> >> >
> >> > I have a custom assembly. In the OnInit() method of the report I
> >> > instantiate
> >> > my class and pass a reference to the report's Parameters collection to
> >> > my
> >> > custom class.
> >> >
> >> > In my custom class I then access the Parameters collection to determine
> >> > the
> >> > report parameter values entered by the user. I can then output the
> >> > parameter
> >> > values to a textbox in my report using an expression like
> >> > =Code.RptLib.GetParamValues().
> >> >
> >> > The problem I have now is I need to be able to figure out the data type
> >> > of
> >> > each report parameter so I can format the values properly. For example
> >> > Dates
> >> > need to be formatted differently from Floats.
> >> >
> >> > So, how do I figure out the data type of each report parameter by
> >> > inspecting
> >> > the Parameters collection?
> >> >
> >> > I am guessing the answer is that I can't and that I should use the web
> >> > service, but I don't want to jump through those hoops and I thought it
> >> > was
> >> > worth asking if there is an easier way.
> >> >
> >> > -- Chris
> >> >
> >> >
> >> >
> >> > --
> >> > Chris, SSSI
> >>
> >>
> >>
>
>|||>>The only downside to this approach - you will have to also know the path
Not to mention, that you also have to know the URL of the Report Server! We
have a staged release environment. Development, Test and Production. Each has
a different report server (and the report servers are different than the
application web servers) and each stage can have different report versions.
So the production web server would have to access the reports on the
production Report Server to get the correct parameter definitions. I have
already taken care of this capability for other reasons, but my point is it
gets somewhat complicated.
>>Chris - I know Microsoft says the schema is subject to change - so use a
>>view - if they change the schema, you can always update the view.
Agreed.
>>Better option: The web service... then you won't care if they change the
>>schema.
You are absolutely right...and I think I will have to go there sooner than I
expected!
;-)
--
Chris, SSSI
"Chris Conner" wrote:
> The only downside to this approach - you will have to also know the path
> that your report was executed from from the report server - because if you
> have two reports with the same name, they would more than likely have
> different parameters.
> I.e.
> /Custom/Year To Date
> /My Reports/Testing/Year To Date
> Above are two reports on the report server, I would see in the catalog table
> two rows for "Year To Date". When I execute this report, in order for me to
> get the right parameter list from the catalog table, I would have to know
> which path as well - not just the name of my own report that is executing.
> You CAN do it this way, but you should also get the Path.
> Chris - I know Microsoft says the schema is subject to change - so use a
> view - if they change the schema, you can always update the view.
> Better option: The web service... then you won't care if they change the
> schema.
> =-Chris
> "emorgoch" <emorgoch.public@.gmail.com> wrote in message
> news:1163777316.972748.135970@.f16g2000cwb.googlegroups.com...
> > Hey Chris,
> >
> > If you look in the reporting services database, in the Catalog table,
> > there's a column called Parameters. That column contains an XML
> > formatted expression describing each of the parameters attached to a
> > report. Probably not the best method, but you could extract the
> > parameter datatype from that column.
> >
> > Evan
> >
> > Chris G. wrote:
> >> SSRS 2005
> >>
> >> OK, I almost have this figured out.
> >>
> >> I have a custom assembly. In the OnInit() method of the report I
> >> instantiate
> >> my class and pass a reference to the report's Parameters collection to my
> >> custom class.
> >>
> >> In my custom class I then access the Parameters collection to determine
> >> the
> >> report parameter values entered by the user. I can then output the
> >> parameter
> >> values to a textbox in my report using an expression like
> >> =Code.RptLib.GetParamValues().
> >>
> >> The problem I have now is I need to be able to figure out the data type
> >> of
> >> each report parameter so I can format the values properly. For example
> >> Dates
> >> need to be formatted differently from Floats.
> >>
> >> So, how do I figure out the data type of each report parameter by
> >> inspecting
> >> the Parameters collection?
> >>
> >> I am guessing the answer is that I can't and that I should use the web
> >> service, but I don't want to jump through those hoops and I thought it
> >> was
> >> worth asking if there is an easier way.
> >>
> >> -- Chris
> >>
> >>
> >>
> >> --
> >> Chris, SSSI
> >
>
>

Can report content be generated dynamically?

SSRS 2005
I have figured out how to use a custom code assembly to dynamically control
the content of the textboxes in the footer of my report.
For example, I have an expression like this in one of the textboxes:
=Code.OakRptLib.BuildPageFooterLeft()
This approach assumes that the textboxes already exist in the report footer.
I am trying to build custom code assemblies that allow my reports to be
dynamically configured in a consistent, standard manner at run time.
Ideally when the report starts up I would like to dynamically create these
text boxes in the report footer so that I can make sure they all use the same
font settings, positioning, content, etc.
Is there a way that I can dynamically create these text boxes in the report
footer when the report first starts processing? For example with code in the
OnInit() method?
--
Chris, SSSIHello Chris,
Based on my research, you could not dynamically create a report item in the
code.
The only thing you may do is using a program to dynamically create a rdl
file.
Since the RDL file is a XML format, you could use the .NET program to
generate a RDL file.
Hope this will be some help for you.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thanks Wei!
Did Steven Cheng have any ideas about this?
-- Chris
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chris,
> Based on my research, you could not dynamically create a report item in the
> code.
> The only thing you may do is using a program to dynamically create a rdl
> file.
> Since the RDL file is a XML format, you could use the .NET program to
> generate a RDL file.
> Hope this will be some help for you.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Chris,
I have discussed with Steven, and he also confirmed this.
I suggest you may try some suggestion from Chris Conner in other post: "Can
I obtain a reference to a report item?"
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Can not Update in DataGrid - C#

Here is my code :

string connstring = System.Configuration.ConfigurationSettings.AppSettings["myconn"];
string selectquery = "Select * from nhacungcap";
protected System.Web.UI.WebControls.DataGrid DataGrid1;
string insertquery = "Insert into nhacungcap(mancc,tenncc,diachi,dienthoai) values(@.mancc1,@.tenncc1,@.diachi1,@.dienthoai1)";

string updatequery = "Update nhacungcap set mancc=@.mancc, tenncc=@.tenncc, diachi=@.diachi, dienthoai=@.dienthoai where (mancc=@.mancc)";
myconnection.Open();

SqlCommand updatecommand = new SqlCommand(updatequery,myconnection);
// sua truong mancc
updatecommand.Parameters.Add(new SqlParameter("@.mancc",SqlDbType.VarChar,10));
updatecommand.Parameters["@.mancc"].Value = DataGrid1.DataKeys[e.Item.ItemIndex];
// sua truong tenncc
updatecommand.Parameters.Add(new SqlParameter("@.tenncc",SqlDbType.NVarChar,50));
updatecommand.Parameters["@.tenncc"].Value = ((TextBox) e.Item.Cells[3].Controls[0]).Text;
// sua truong diachi
updatecommand.Parameters.Add(new SqlParameter("@.diachi",SqlDbType.NVarChar,200));
updatecommand.Parameters["@.diachi"].Value = ((TextBox) e.Item.Cells[4].Controls[0]).Text;
// sua truong dienthoai
updatecommand.Parameters.Add(new SqlParameter("@.dienthoai",SqlDbType.Char,10));
updatecommand.Parameters["@.dienthoai"].Value = ((TextBox) e.Item.Cells[5].Controls[0]).Text;
// kiem tra lenh thuc thi
int result1 = updatecommand.ExecuteNonQuery();
myconnection.Close();
// dieu kien kiem tra
if (result1 > 0 )
{
lbcheck.Text = "C?p Nh?t Thành công !";
}
// hien thi du lieu
hienthidulieu();

And my error appear, when I edit value in datagrid, I only update all value fields, without value@.mancc .Hu hu hu, I don't know what i must do with it.

help me please

can not retrive @@identity value

I use select @.@.identity to return @.@.identity from my store procedure,
but I could not retrive it from my Visual basic code, like variable=
oRS.fields.item(0).value, it always says item can not be found...Hi

At a guess it is probably because it is not the first recordset being
returned.

Instead it may be best to use an output parameter to do this.

Also if you are using SQL 2000 then use the SCOPE_IDENTITY() function.

John

"Allan" <hlang121@.yahoo.com> wrote in message
news:436e7a2d.0308271236.3bd2edd2@.posting.google.c om...
> I use select @.@.identity to return @.@.identity from my store procedure,
> but I could not retrive it from my Visual basic code, like variable=
> oRS.fields.item(0).value, it always says item can not be found...|||Allan (hlang121@.yahoo.com) writes:
> I use select @.@.identity to return @.@.identity from my store procedure,
> but I could not retrive it from my Visual basic code, like variable=
> oRS.fields.item(0).value, it always says item can not be found...

So how does the VB code look like? And how does the stored procedure
look like?

Your chances to precise assistance increases if you care to share
the code you are having problem.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

One of the easiest way to get the identity value from sqlserver7.0, is

rs.open"select @.@.identity as id from tab1",..,..
box1 = rs!id

With Thanks
Raghu