Easy but Im new...

Hi All,

So I'm only a beginner at Crystal but here it goes: I am writing a report for the Helpdesk that tells them the amount of issues they need to escalate out of their group. I had to do a TrimRight({Column}20 to 35) and also added a TRIM to cut out any leading or ending spaces.

This part worked great, exactly what I needed, but now I need to match that value from the formula with a column in a database. The value gives me back a login name such as TBARLOW. I now need to match that value to the loginid column and see if it matches anything in that column.

IF {@.Trim} = {Support_Staff.Login ID} then
{Incident_Details.From Group:}

There are no errors in the formula...so basically I need it to say: take @.trim and find that value in the {Support_Staff.Login ID} field.

Any help would be great,
Thanks so much,
Timwell 2 things - do you know if {Support_Staff.Login ID} has any leading or ending spaces? As common practice (espcecially if comparing) I always use TRIM on each field. I.E.

If {@.Trim} = trim({Support_Staff.Login ID}) then ...

the second thing - and maybe this is just a typo - Is {Support_Staff.Login ID} supposed to be {Support_Staff.LoginID} or maybe {Support_Staff.Login_ID}? If you just double clicked on the field in the Report Fields, then it should be right, if you just typed it in thought there might be a problem. As far as I know, most databases don't like spaces in the field names.sql

Dynamically Writing & Rendering Report

I have a report which will change almost everytime it is requested. I am
building the query and writing the RDL on the fly. I don't want to save the
RDL to the report server as it will be of no future use. Is there a way to
pass the RDL as perhaps a string or something similar and ask the
ReportServer to render that instead of a saved report.
Many thanks in advance for any help.
SimonThis functionality is not supported in the current release but is on wish
list for a future release. For now, you'll need to publish the report in
order to render it, i.e., call CreateReport() and then call Render(). You
can always delete it once you're done.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote in message
news:%23VX8qckbEHA.2544@.TK2MSFTNGP10.phx.gbl...
> I have a report which will change almost everytime it is requested. I am
> building the query and writing the RDL on the fly. I don't want to save
the
> RDL to the report server as it will be of no future use. Is there a way to
> pass the RDL as perhaps a string or something similar and ask the
> ReportServer to render that instead of a saved report.
> Many thanks in advance for any help.
> Simon
>|||Thanks Ravi,
Thats a bit of pain as I could have 100's if not 1000's of stray reports on
the server that would only ever be used once. I now need to write a cleanup
routine to peridoically remove the unused reports from the server. A majorly
inefficient process but looks like the only option.
Do you have any idea when the next iteration of SQL Reporting is planned for
that will include this functionality?
Simon
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote
> This functionality is not supported in the current release but is on wish
> list for a future release. For now, you'll need to publish the report in
> order to render it, i.e., call CreateReport() and then call Render(). You
> can always delete it once you're done.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services|||The next release of reporting services is in SQL Server 2005. This feature
is on wish list for that release.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote in message
news:ee0IXHxbEHA.796@.TK2MSFTNGP09.phx.gbl...
> Thanks Ravi,
> Thats a bit of pain as I could have 100's if not 1000's of stray reports
on
> the server that would only ever be used once. I now need to write a
cleanup
> routine to peridoically remove the unused reports from the server. A
majorly
> inefficient process but looks like the only option.
> Do you have any idea when the next iteration of SQL Reporting is planned
for
> that will include this functionality?
> Simon
>
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote
> > This functionality is not supported in the current release but is on
wish
> > list for a future release. For now, you'll need to publish the report in
> > order to render it, i.e., call CreateReport() and then call Render().
You
> > can always delete it once you're done.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
>

Dynamically Writing & Rendering Report

Is there a way to Dynamically Writing & Rendering Report for SQL
Server Reporting Services? If so an example would be good.Until there is a render control that does not require the server this
difficult to do. When you publish the report it is there for everyone, so
they can easily step on each other. MS has a document that totally specifies
the xml syntax for RDL. Also, you can open up the report.rdl into an editor
to see what it looks like.
Bruce L-C
"Bila" <bakpan@.teckit.com> wrote in message
news:d26cf8a6.0407291113.6103048@.posting.google.com...
> Is there a way to Dynamically Writing & Rendering Report for SQL
> Server Reporting Services? If so an example would be good.|||A method may be do infer an XSD document from the document MSFT provides and
this will create an object model which you can access just like any other
object model. You can then create your report / content using the object
model, serialize back into XML, and then deploy the report to a server and
invoke the render command.
I'm pretty sure you can pull the RDL specification into a XSD document
(which will create a .vb or .c# file for you), but I haven't tried this
befoe.
-Joel
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OqsarIadEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Until there is a render control that does not require the server this
> difficult to do. When you publish the report it is there for everyone, so
> they can easily step on each other. MS has a document that totally
specifies
> the xml syntax for RDL. Also, you can open up the report.rdl into an
editor
> to see what it looks like.
> Bruce L-C
> "Bila" <bakpan@.teckit.com> wrote in message
> news:d26cf8a6.0407291113.6103048@.posting.google.com...
> > Is there a way to Dynamically Writing & Rendering Report for SQL
> > Server Reporting Services? If so an example would be good.
>|||Still, when you deploy that report is there for everyone. I guess you could
deploy with a unique name just for that particular user but still, not easy,
not fast.
Bruce L-C
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:u15I6lcdEHA.720@.TK2MSFTNGP11.phx.gbl...
> A method may be do infer an XSD document from the document MSFT provides
and
> this will create an object model which you can access just like any other
> object model. You can then create your report / content using the object
> model, serialize back into XML, and then deploy the report to a server and
> invoke the render command.
> I'm pretty sure you can pull the RDL specification into a XSD document
> (which will create a .vb or .c# file for you), but I haven't tried this
> befoe.
> -Joel
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OqsarIadEHA.3420@.TK2MSFTNGP12.phx.gbl...
> > Until there is a render control that does not require the server this
> > difficult to do. When you publish the report it is there for everyone,
so
> > they can easily step on each other. MS has a document that totally
> specifies
> > the xml syntax for RDL. Also, you can open up the report.rdl into an
> editor
> > to see what it looks like.
> >
> > Bruce L-C
> >
> > "Bila" <bakpan@.teckit.com> wrote in message
> > news:d26cf8a6.0407291113.6103048@.posting.google.com...
> > > Is there a way to Dynamically Writing & Rendering Report for SQL
> > > Server Reporting Services? If so an example would be good.
> >
> >
>

Dynamically specify server and database in Stored Procedure

I am writing Stored Procedures on our SQL 2005 server that will link with data from an external SQL 2000 server. I have the linked server set up properly, and I have the Stored Procedures working properly. My problem is that to get this to work I am hardcoding the server.database names. I need to know how to dynamically specify the server.database so that when I go live I don't have to recompile all of my stored procedures with the production server and database name. Does anyone have any idea how to do this?
EXAMPLE:
SELECT field1, field2 FROM mytable LEFT OUTER JOIN otherserver.otherdatabase.dbo.othertable

OBJECTIVE:

Replace 'otherserver.otherdatabase.dbo.othertable' with some other process (dbo.fnGetTable('dbo.othertable')?)

Thanks for any help

Hi,

that is not (yet) parameterizable. You would have to use dynamic sql here.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Dynamically select tables

Hi,
I am writing a stored procedure which needs to select different tables
based on different parameters. I used to use 'CASE' to select
different columns, so I tried to use following statement like "select
* from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
What i need to achieve is dynamically select tables based on
parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
'ORDER' table.
Could anyone help me with this issue?
ThanksYOu have to use dynamic SQL for this. You can stuck you sql coe
together and execute it then with EXEC or sp_executesql. Dynamic sql
has some limitations and may be the nail to your coffin, the best would
be to read Erlands article first before implementing this:
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.|||Ron (rzhou@.mettle.biz) writes:
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
Sounds ugly. Maybe there is reason for a table redesign? Then again,
it could make sense.
Anyway, dynamic SQL is what you need to do this. I have a general
article on dynamic SQL on my web site, and then there is another which
discusses dynamic search conditions in particular.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Guys,
Thanks for your help! great articles
Ron|||>> I am writing a stored procedure which needs to select different tables ba
sed on different parameters. <<
Have you thought about what that means in terms of your design?
Assuming that you have a relational schema, each table is a TOTALLY
DIFFERENT KIND OF ENTITY , what will meaningful name will you give this
nightmare? I propose that you use
" Get_squids_or_automobiles_or_Britney_Spe
ars" as the name. It sounds
pretty vague and stupid when you think about it.
Gee, sure sounds like it violates coupling and cohesion -- remember
those fundamentals of programming from your freshman year in Comp Sci?
That is FAR more fundamental than SQL.
You have never read a book on SQL. Not even half a book! The CASE
expression returns a value of a known data type, just like any other
expression. SQL is compiled; you are not writing BASIC.
The stinking, dirty, unmaintainable kludge that you will get on a
Newsgroup is dynamic SQL. That way you can avoid RDBMS and fake 1960's
BASIC code on the fly.
Why won't anyone else tell you this? If we give you that quick answer
or a few links, you will go away. But if someone yells at you for
your lack of fundamentals, then your feeling might be hurt (we assume
you are child, not an adult) or that you will ask questions that will
require serious study and we don't want to post a few quarters of
college level work on a newsgroup.
If you want a REAL answer, we need DDL, a good spec, sample data, etc.
And you might have a horrible schema that needs to be re-done, the
queries might be really hard, etc. Welcome to the real world!!|||Don't be intimidated,... Dynamic sql will do what you wish...That is the
answer to your question.
However, you might wish to ensure you have a good design, and that you are
not making a problem for yourself later... Dynamic SQL does help us solve
problems, and we use it when we need to -
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Ron" wrote:

> Hi,
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
> Thanks
>|||IMO Dynamic sql is possible.
BOL states that you can not use can not us parameters with openRowset and fr
om a
pure technical sense, I guess it's a valid statement. But where there is wi
ll
there is a way.
You can see I build a variable @.SQL based in part on parameters passed to th
e
procedure. Is this not dynamic SQL?
CREATE Procedure usp_GetPeriodLabor @.bp as char(5),@.ep as nvarchar(5) as
Declare @.sql nvarchar(500)
SET @.SQL = 'Select * into tPeriodLabor_tmp from OPENROWSET(''MSDAORA'',
''oralcleinstance'';''user'';''password'
',
''select detail_Date,employee_sys_id,pay_period,L
D_CODE1 as
CostCenter,ld_code2,ld_Code3 as account,
stop_time,start_time, (stop_time-start_time)/60
from easp.timecard_detail
where (pay_Period >= ' +@.bp+' and
detail_date <= ' +@.ep+') and (timecode_sys_id = 128 or timecode_sys_id = 136
or timecode_sys_id = 142 or timecode_sys_id = 163 or timecode_sys_id = 166)'
')'
Exec (@.sql)
GO
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenet

Dynamic Update with a sub select

Hi I need some help writing a dynamic Update with a sub select.

I am trying to execute this query and retrieve a variable.The update and select work separately but when I put them together I get the following error,

incorrect syntax near’= ‘

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.SQL = 'UPDATEdbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set@.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID ='

Set@.SQL = @.SQL +' (Select @.XWorkingSheduleID = (Max (WorkingSheduleID)'

Set@.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set@.SQL = @.SQL +'@.XDeliveryDate'

Set@.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT,@.DeliveryDate

Any Help appreciated

Mr Tumnus wrote:

Hi I need some help writing a dynamic Update with a sub select.

I am trying to execute this query and retrieve a variable. The update and select work separately but when I put them together I get the following error,

incorrect syntax near’= ‘

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.SQL = 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID ='

Set @.SQL = @.SQL +' (Select @.XWorkingSheduleID = (Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT, @.DeliveryDate

Any Help appreciated

I think that the indicated (red) bracket is wrong as this is bracketing the SELECT away from (at a different level to) the other parts of the query (FROM, WHERE). I am less certain about which corresponding bracket to remove but I think it is the indicated one (blue).

|||

No change, I still get the same error

I have tried to use @.@.Identity to retrieve the variable, but I keep getting the identity of a query I run earlier in the SP (don’t sure I am using @.@.Identity properly).

I am fairly new to SQL and would appreciate any advice.

|||

Have you tried capturing the value of @.SQL and running that interactively with correct surrouding code (the DECLAREs, SETs, and a SELECT to inspect the final value). If you can find a version that works like that then you should only need to build it.

Another option is to split the operation into a batch of 2 steps like:

Code Snippet

SET @.SQL = 'SELECT @.XWorkingSheduleID = Max (WorkingSheduleID)'
SET @.SQL = @.SQL +' From dbo.['+ @.TableName +']'
SET @.SQL = @.SQL + ' Where (DeliveryDate = CAST(@.XDeliveryDate'
SET @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0); '
SET @.SQL = @.SQL + 'UPDATE dbo.['+ @.TableName +'] SET UID = '
SET @.SQL = @.SQL + '@.XCustomerID, SlotClosed = 1'
SET @.SQL = @.SQL +' WHERE (WorkingSheduleID = @.XWorkingSheduleID)'

For your @.SQL setting code.

|||Hi,

try this:

Code Snippet

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.TABLENAME VARCHAR(100)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.TableName = 'SomeTable'

SET @.SQL = 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID = '

Set @.SQL = @.SQL +' (Select Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

PRINT @.SQL

UPDATE dbo.[SomeTable]

SET

UID =@.XCustomerID ,

SlotClosed=1

where WorkingSheduleID =

(

Select Max (WorkingSheduleID) From dbo.[SomeTable]

Where DeliveryDate =(CAST(@.XDeliveryDate AS datetime)) And (SlotClosed=0))

)

Don′t know why you did the thing with the @.XcustomerId in the brackets, but you wither leave that out or put it somewhere in there where-clause instead.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Try to change the query as follows :

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

Set @.SQL = 'Select @.XWorkingSheduleID = Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0);'

set @.SQL = @.SQL + 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID = @.XWorkingSheduleID '

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT, @.DeliveryDate

SELECT @.XWorkingSheduleID

Dynamic SQL statements in a stored procedure

Hi

I have a small problem writing a stored procedure in a SQL Server 2000 database.

I would like to generate som part of the SQL inside this stored procedure that is used in an IN expression of my WHERE clause. There is no problem for me to generate a string containing my expression, the problem is that SQL-Server dont generate a resulting SQL-statement.

Example:

CREATE PROCEDURE spDynStatement
AS

DECLARE @.sPartOfSQLStatement NVARCHAR(100)

-- Some T-SQL that generates the dynamic part of the SQL-statement
-- .
-- .
-- .

-- As substitute I insert the string expression
SET @.sPartOfSQLStatement = '''1''' + ', ' + '''1.5'''

-- SELECT @.sPartOfSQLStatement results in: '1' , '1.5'

SELECT * FROM BBNOrganization WHERE OrgStructureID IN( @.sPartOfSQLStatement ) -- does not work

SELECT * FROM BBNOrganization WHERE OrgStructureID IN( '1', '1.5' ) -- works!!!
GO

Thankfull for ideas on how to solve my problem,

PeterTry this

DECLARE @.sSQLStatement VARCHAR(4000)

SET @.sSQLStatement = 'SELECT * FROM BBNOrganization WHERE OrgStructureID IN(' + '''1''' + ', ' + '''1.5''' + ')'

EXEC @.sSQLStatement