Generating script for existing sql jobs

we have a database and we are planning to deploy the database on another Box. I have database tables,stored procedures,views,user defined functions,triggers and some jobs on this database. So instead of restoring a back of this database we are planning to create the whole environment by using the sql scrtipts. So I know how to generate the sql script for all the objects(using generate sql script at database level) but how can I get the script for my Sql Jobs. How can I copy all the sql jobs onto the the new Box.

Thanks.In Enterprise Manager, if you right click on the job and select "all tasks" you will be able to generate a script for that job.

If you have many jobs to move, you could do a restore of the msdb database on your new server since the SQL Server Agent jobs are maintained in the msdb database.

Generating reports on fly

Hello,

SRS 2005 provides functionality for loading and rendering reports on fly using LoadReportDefinition and Render methods defined in ReportExecutionService webservice.

I was wondering if the same/similar behaviour can be accomplished in Reporting Services 2000.

Thanks in advance,

Kobi

LoadReportDefinition API was introduced in 2005 for the Report Builder. With SSRS 2000 you have to explicitly upload the report defnition.

Generating complicate WHERE clause via parameters

Hi,
I have a large number of parameters defined which can be null and I have to
generate WHERE clause based on valid parameters, this makes things complicated
becuse I have to add "AND" to the WHERE clause depending on availability of
the parameters, the number of validation I have to do increases gradually
because the 10th parametrs should check for availability of 10 previous
parameters
before it can add "AND" infront of it. Is there any slick ways to handle
this problem?
Thank you.COALESCE will do.
"JC" wrote:
> Hi,
> I have a large number of parameters defined which can be null and I have to
> generate WHERE clause based on valid parameters, this makes things complicated
> becuse I have to add "AND" to the WHERE clause depending on availability of
> the parameters, the number of validation I have to do increases gradually
> because the 10th parametrs should check for availability of 10 previous
> parameters
> before it can add "AND" infront of it. Is there any slick ways to handle
> this problem?
> Thank you.|||Thank you for you reply, I still do not see how CAOESECE can solve my problem.
My query looks like this.
SELECT column1, column2 FROM table1
WHERE
column3 = val1
AND
column4 = val2
with out val1 parameter supplied it should be:
SELECT column1, column2 FROM table1
WHERE
column4 = val2
So printing "AND" is the problem and only way to do it for me right now is
to check
if val1 was supplied or not.
can COALESCE help in this situation?
Thank you.
"Bing Bing Yu" wrote:
> COALESCE will do.
>
> "JC" wrote:
> > Hi,
> >
> > I have a large number of parameters defined which can be null and I have to
> > generate WHERE clause based on valid parameters, this makes things complicated
> > becuse I have to add "AND" to the WHERE clause depending on availability of
> > the parameters, the number of validation I have to do increases gradually
> > because the 10th parametrs should check for availability of 10 previous
> > parameters
> > before it can add "AND" infront of it. Is there any slick ways to handle
> > this problem?
> >
> > Thank you.|||WHERE column3 = COALESCE(val1, column3)
AND column4 = COALESCE(val2, column4)
"JC" wrote:
> Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> My query looks like this.
> SELECT column1, column2 FROM table1
> WHERE
> column3 = val1
> AND
> column4 = val2
> with out val1 parameter supplied it should be:
> SELECT column1, column2 FROM table1
> WHERE
> column4 = val2
> So printing "AND" is the problem and only way to do it for me right now is
> to check
> if val1 was supplied or not.
> can COALESCE help in this situation?
> Thank you.
>
> "Bing Bing Yu" wrote:
> >
> > COALESCE will do.
> >
> >
> > "JC" wrote:
> >
> > > Hi,
> > >
> > > I have a large number of parameters defined which can be null and I have to
> > > generate WHERE clause based on valid parameters, this makes things complicated
> > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > the parameters, the number of validation I have to do increases gradually
> > > because the 10th parametrs should check for availability of 10 previous
> > > parameters
> > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > this problem?
> > >
> > > Thank you.|||I eneded up doing it like this (using AdventureWorks) can this be enhanced?
(I have credited you by the way, thank you)
="DECLARE @.ContactID INT, " &
"@.FirstName NVARCHAR(50) " &
Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
" SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
"WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName =COALESCE(@.FirstName, FirstName)"
"Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
>
> WHERE column3 = COALESCE(val1, column3)
> AND column4 = COALESCE(val2, column4)
>
> "JC" wrote:
> >
> > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> >
> > My query looks like this.
> >
> > SELECT column1, column2 FROM table1
> > WHERE
> > column3 = val1
> > AND
> > column4 = val2
> >
> > with out val1 parameter supplied it should be:
> >
> > SELECT column1, column2 FROM table1
> > WHERE
> > column4 = val2
> > So printing "AND" is the problem and only way to do it for me right now is
> > to check
> > if val1 was supplied or not.
> >
> > can COALESCE help in this situation?
> >
> > Thank you.
> >
> >
> > "Bing Bing Yu" wrote:
> >
> > >
> > > COALESCE will do.
> > >
> > >
> > > "JC" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a large number of parameters defined which can be null and I have to
> > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > the parameters, the number of validation I have to do increases gradually
> > > > because the 10th parametrs should check for availability of 10 previous
> > > > parameters
> > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > this problem?
> > > >
> > > > Thank you.|||Hi,
I think you need to build the query something like this.
declare @.str as nvarchar(1000)
set @.str = ''
set @.str = @.str + "select * from "...
if @.val is null
set @.str = @.str + "val = .."
exec sp_executesql @.str
you can use if conditions where ever required.
Amarnath
"JC" wrote:
> I eneded up doing it like this (using AdventureWorks) can this be enhanced?
> (I have credited you by the way, thank you)
> ="DECLARE @.ContactID INT, " &
> "@.FirstName NVARCHAR(50) " &
> Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
> Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
> Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
> Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
> " SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
> "WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName => COALESCE(@.FirstName, FirstName)"
>
> "Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
> >
> >
> > WHERE column3 = COALESCE(val1, column3)
> > AND column4 = COALESCE(val2, column4)
> >
> >
> > "JC" wrote:
> >
> > >
> > > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> > >
> > > My query looks like this.
> > >
> > > SELECT column1, column2 FROM table1
> > > WHERE
> > > column3 = val1
> > > AND
> > > column4 = val2
> > >
> > > with out val1 parameter supplied it should be:
> > >
> > > SELECT column1, column2 FROM table1
> > > WHERE
> > > column4 = val2
> > > So printing "AND" is the problem and only way to do it for me right now is
> > > to check
> > > if val1 was supplied or not.
> > >
> > > can COALESCE help in this situation?
> > >
> > > Thank you.
> > >
> > >
> > > "Bing Bing Yu" wrote:
> > >
> > > >
> > > > COALESCE will do.
> > > >
> > > >
> > > > "JC" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a large number of parameters defined which can be null and I have to
> > > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > > the parameters, the number of validation I have to do increases gradually
> > > > > because the 10th parametrs should check for availability of 10 previous
> > > > > parameters
> > > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > > this problem?
> > > > >
> > > > > Thank you.|||Well, I'm pretty sure the dynamic query works just fine. One concern would
be since it's dynamic, SQL server won't be able to optimize the query.
"Amarnath" wrote:
> Hi,
> I think you need to build the query something like this.
> declare @.str as nvarchar(1000)
> set @.str = ''
> set @.str = @.str + "select * from "...
> if @.val is null
> set @.str = @.str + "val = .."
> exec sp_executesql @.str
> you can use if conditions where ever required.
> Amarnath
>
> "JC" wrote:
> >
> > I eneded up doing it like this (using AdventureWorks) can this be enhanced?
> > (I have credited you by the way, thank you)
> >
> > ="DECLARE @.ContactID INT, " &
> > "@.FirstName NVARCHAR(50) " &
> > Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
> > Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
> > Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
> > Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
> > " SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
> > "WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName => > COALESCE(@.FirstName, FirstName)"
> >
> >
> >
> > "Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
> >
> > >
> > >
> > > WHERE column3 = COALESCE(val1, column3)
> > > AND column4 = COALESCE(val2, column4)
> > >
> > >
> > > "JC" wrote:
> > >
> > > >
> > > > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> > > >
> > > > My query looks like this.
> > > >
> > > > SELECT column1, column2 FROM table1
> > > > WHERE
> > > > column3 = val1
> > > > AND
> > > > column4 = val2
> > > >
> > > > with out val1 parameter supplied it should be:
> > > >
> > > > SELECT column1, column2 FROM table1
> > > > WHERE
> > > > column4 = val2
> > > > So printing "AND" is the problem and only way to do it for me right now is
> > > > to check
> > > > if val1 was supplied or not.
> > > >
> > > > can COALESCE help in this situation?
> > > >
> > > > Thank you.
> > > >
> > > >
> > > > "Bing Bing Yu" wrote:
> > > >
> > > > >
> > > > > COALESCE will do.
> > > > >
> > > > >
> > > > > "JC" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have a large number of parameters defined which can be null and I have to
> > > > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > > > the parameters, the number of validation I have to do increases gradually
> > > > > > because the 10th parametrs should check for availability of 10 previous
> > > > > > parameters
> > > > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > > > this problem?
> > > > > >
> > > > > > Thank you.|||One easy way would be to use 1=1 and
'Select col1 from table 1 where 1=1 and' + @.str_where
@. str_where can be populated from the parameters you accept in
"JC" wrote:
> Hi,
> I have a large number of parameters defined which can be null and I have to
> generate WHERE clause based on valid parameters, this makes things complicated
> becuse I have to add "AND" to the WHERE clause depending on availability of
> the parameters, the number of validation I have to do increases gradually
> because the 10th parametrs should check for availability of 10 previous
> parameters
> before it can add "AND" infront of it. Is there any slick ways to handle
> this problem?
> Thank you.|||Although you can do dynamic SQL I find this technique easier:
select somefields from sometable where (somefield = @.Param1 or @.Param1 ='All') and (anotherfield = @.Param2 or @.Param1 = 'All')
Note that if a number field you can check for a number that doesn't exist in
your database whether it is -1 or -99999 or whatever as long as you know it
doesn't exist in real life.
I prefer to have an All selection, however, you can use Null or Empty string
as well.
select somefields from sometable where (somefield = @.Param1 or
isnull(@.Param1)) and (anotherfield = @.Param2 or isnull(@.Param1))
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sunil" <Sunil@.discussions.microsoft.com> wrote in message
news:2666D2EB-03EA-47CF-A958-52EC722567FC@.microsoft.com...
> One easy way would be to use 1=1 and
> 'Select col1 from table 1 where 1=1 and' + @.str_where
> @. str_where can be populated from the parameters you accept in
>
> "JC" wrote:
>> Hi,
>> I have a large number of parameters defined which can be null and I have
>> to
>> generate WHERE clause based on valid parameters, this makes things
>> complicated
>> becuse I have to add "AND" to the WHERE clause depending on availability
>> of
>> the parameters, the number of validation I have to do increases gradually
>> because the 10th parametrs should check for availability of 10 previous
>> parameters
>> before it can add "AND" infront of it. Is there any slick ways to handle
>> this problem?
>> Thank you.