Create and Administer SQL Server Agent Jobs via Sprocs?

Hi,

Does anyone know if it is possible to set up stored procedures which will create, modify, and disable SQL Server Agent Jobs?

I have a web based application which I need to enable an administrator to change the frequency which a job runs and encapsulating the modifications in a stored procedure would be the easiest way for me to integrate this, if it's possbible.

Regards, MattHi,
You may use sp_update_jobschedule, sp_update_job etc or if you create your own sp and use tables such as sysjobschedules, sysjobs etc.
Tables and procs are located in MSDB.
Ex for updating scheduled time:
UPDATE msdb.dbo.sysjobschedules
SET active_start_time = 164000
WHERE (job_id = '8A0F1080-D22A-4F82-AE13-68F789989D1D')
AND (name = 'Once')

Try this and let us know how it work

Regards|||Thanks Tommy,

Going to give this a shot this afternoon. I'll let you know how it turns out.

Create a Named Instance on top of a Default Instance

Hi

I've never had to do this, but when I downloaded the Web Workflow Approvals Starter Kit, it requested that I install the database into a User Instance of .\SQLEXPRESS.

Now the problem is, I've installed it onto a default instance, so I was wondering whether you can create a named instance on top of a default instance... and if so, how would you do that?

Cheers

Chris

hi Chris,

you can not create one as you intend, but you can install an additional named instance for that.. or, you can modify the connection strings in the application code to connect to

Data Source=(Local); .....; User Instance=true;

instead of

Data Source=(Local)\SQLExpress; .....; User Instance=true;

regards

|||Hi Andrea

Thanks for the tip... I always wondered what user instances were for...

Now I just have to uninstall & reinstall SQL and enable it.

Cheers

Chris
sql

Create 3 users tables

Hello,
In my web site I have 2 types of users: students and professors.
All users need to login but the personal information for each user type
has different fields.
So I create 3 tables: professors, students and authentication,
"professors" will have all the personal data from professors.
"students" will have all the personal data from students.
authentication will have the username and password from all users.
How can i link authentication table to both professors and students
tables so when the user logins i get the data from that user, either a
professor or student?
And is the right way to do this?
I am using SQL 2005 and Asp.Net 2.0
Thanks,
MiguelOn Wed, 21 Dec 2005 23:10:00 +0000, Miguel Dias Moura wrote:

>Hello,
>In my web site I have 2 types of users: students and professors.
>All users need to login but the personal information for each user type
>has different fields.
>So I create 3 tables: professors, students and authentication,
>"professors" will have all the personal data from professors.
>"students" will have all the personal data from students.
>authentication will have the username and password from all users.
>How can i link authentication table to both professors and students
>tables so when the user logins i get the data from that user, either a
>professor or student?
>And is the right way to do this?
>I am using SQL 2005 and Asp.Net 2.0
>Thanks,
>Miguel
Hi Miguel,
I would use one Persons table to hold information that applies to both
professors and students. Specific information then goes in the students
and the professors tables.
Login information could go in the Persons table, but is probably better
placed in a seperate table (for reasons of security, but also for
flexibility - maybe, someone will someday need two seperate accounts,
then you'll be ready for it).
The tables Persons, Students and Professors all have the same primary
key; this same column is also foreign key in the Students and Professors
table, in both cases referencing the Persons table.
The Logins table has userid as primary key (of course) and has also a
foreign key that referenced the Persons table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

CR11 and ASP.NET (VS2003)

Hi there,
While running the report on the web we expect full error message to be displayed but instead whenever report fails CrystalReportViewer always say

Failed to retrieve data from the database. Details: [Database Vendor Code: 17001 ] Failed to retrieve data from the database

When I run this report in design mode I get correct error message that is returned from Sybase Stored procedure. "Invalid Parameter Value passed"

Is there anyway I can make CrystalReportViewer to display exact error message returned from SP?

Thanks a lot
GirishAnybody Know ? Please help
Girish

CR10 web viewer width 100%

Hello,
I'm trying to set my CrystalReportViewer to 100% of page width in ASP.NET. When I set the Width property on the control in the ASPX page itself, the viewer renders itself correctly when the page first loads, but on each postback the "%" is changed to "px" causing the viewer to be only 100 pixels wide.

I can apparently get around that by setting the width to Unit.Percentage(100) on every Page_Load event. Then the viewer appears as I expect with 100% width everytime I navigate pages, search, or export.

My problem is this workaround doesn't seem to work for the group tree. When I toggle the group tree, the two divs that get rendered are of width 14px and 86px (which add up to 100px).

Any ideas how I can get around this bug?

Thanks,
JamesI have a lousy solution but it seems to work (this is C# code but same should apply to VB):
public class MyCrystalReportViewer : CrystalDecisions.Web.CrystalReportViewer
{
protected override void Render(HtmlTextWriter output)
{
string renderedControl = string.Empty;

// Render the control to a string builder
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
HtmlTextWriter htw = new HtmlTextWriter(sw);
base.Render(htw);

// Extract the string for processing
renderedControl = sb.ToString();

// Replace certain strings in the group tree div style tag
// Be sure to check for all indices along the way and just do nothing if things aren't found.
int groupTreeIndex = renderedControl.IndexOf("<div class=\"crgrptr\"");
if ( groupTreeIndex != -1 )
{
int groupTreeStyle = renderedControl.IndexOf("style=\"",groupTreeIndex);
if ( groupTreeStyle != -1 )
{
int groupTreeStyleEnd = renderedControl.IndexOf("\"",groupTreeStyle+7);
if ( groupTreeStyleEnd != -1 )
{
string groupTreeStyleTag = renderedControl.Substring(groupTreeStyle+7,groupTreeStyleEnd-groupTreeStyle-7);
renderedControl = renderedControl.Substring(0,groupTreeStyle+7)
+ "display:inline;" + groupTreeStyleTag.Replace("width:14px","width:14%").Replace("position:absolute","position:static")
+ renderedControl.Substring(groupTreeStyleEnd);
}
}
}

// Replace certain strings in the page div style tag
// Be sure to check for all indices along the way and just do nothing if things aren't found.
int pageIndex = renderedControl.IndexOf("<div class=\"crystalstyle\"");
if ( pageIndex != -1 )
{
int pageStyle = renderedControl.IndexOf("style=\"",pageIndex);
if ( pageStyle != -1 )
{
int pageStyleEnd = renderedControl.IndexOf("\"",pageStyle+7);
if ( pageStyleEnd != -1 )
{
string pageStyleTag = renderedControl.Substring(pageStyle+7,pageStyleEnd-pageStyle-7);
renderedControl = renderedControl.Substring(0,pageStyle+7)
+ "display:inline;" + pageStyleTag.Replace("width:86px","width:86%").Replace("position:absolute","position:static")
+ renderedControl.Substring(pageStyleEnd);
}
}
}

// Now output the reformatted HTML of the control
output.Write(renderedControl);
}
}

CR interactivity in question..

I have a web application. When the user presses a button a crystal report comes on screen.

What i need to know is, if there is a possibility to implement this:

"When the user clicks on a certain field on a certain row, i want to run function A in my web application, with parameters the row clicked and the field clicked".

i just wanna add on-click code in field level (or perhaps add in my report a button to do sth), can i do that??

ThanksYou can use parameters to pass the information from your app to Crystal. I don't know that proper syntax though.

Try searching Crystal's website:
http://support.businessobjects.com/search/advsearch.asp

Or Crystal's Forum:
http://support.businessobjects.com/forums/default.asp|||What are you trying to do?
1. Do you want to select a report based on data in selected row?
2. Or do you want to see more data related to the row you selected?

If your option is 1, then use a DataGrid and populate it with records, and then when user clicks on selected row, trap the click event and pass the value of that row to a Crystal Report as the parameter for that report.
For some help with parameters and how to use them in .NET, try this link:
http://support.businessobjects.com/library/kbase/articles/c2010247.asp

If it's 2, design the report so that it has an "On-Demand" subreport, so that user can click the link which will appear in your Details section, which will trigger the subreport based on the data in that report line.

It's helpful if your question details the implementation, "I have a web application." really doesn't tell us enough, so I've presumed it's .NET, because .NET incorporates basic Crystal functionality.

Dave|||I need to put a control, say a button inside the report. When the user presses the button i want something else, irrelevant of the report to happen (e.g. navigate to a certain page).

1) How can i add a button in my report?
2) How do i trap the on-click event?

Thanks

CPU 100%

Hi,

I have sql server on a web server.
The machine grinds almost to a halt at peak times, as the asp pages are
causing sql server to use most of the cpu.
Looking at the graph it is at 100% for about 20 seconds at a time, then
drops, then goes back up.

Is there anything I can do to make sql server use less of the cpu? (Without
causing the asp pages to hand.)

Thanks

JJJJ (nospam) writes:
> I have sql server on a web server.
> The machine grinds almost to a halt at peak times, as the asp pages are
> causing sql server to use most of the cpu.
> Looking at the graph it is at 100% for about 20 seconds at a time, then
> drops, then goes back up.
> Is there anything I can do to make sql server use less of the cpu?

Probably. But exactly what I cannot tell. I can however give some
suggestions of what you can do to track down the problem. The best
approach is to use the Profiler. With this tool you can track commands
send to SQL Server, and you can get information about them. Assuming
that the source of the problem is a resource-intensive query, you
can use the Profiler to narrow down which query/ies it may be. Once
you have located one or more queries, you can look into how to improve
them, for instance by adding indexes.

Depending on your budget, you may also consider putting SQL Server on a
separate box, so that the web server and SQL Server does not have to
compete for resources.

--
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,
Many Thanks for the reply.
I am on the verge of investing in a new server anyway, as it seems easier to
throw more hardware at it, although I appreciate this is not necessarily the
best solution.
However, if I can get sql server on a separate box, hopefully my problems
will be solved!

JJ
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94ECF2D31D7D6Yazorman@.127.0.0.1...
> JJ (nospam) writes:
> > I have sql server on a web server.
> > The machine grinds almost to a halt at peak times, as the asp pages are
> > causing sql server to use most of the cpu.
> > Looking at the graph it is at 100% for about 20 seconds at a time, then
> > drops, then goes back up.
> > Is there anything I can do to make sql server use less of the cpu?
> Probably. But exactly what I cannot tell. I can however give some
> suggestions of what you can do to track down the problem. The best
> approach is to use the Profiler. With this tool you can track commands
> send to SQL Server, and you can get information about them. Assuming
> that the source of the problem is a resource-intensive query, you
> can use the Profiler to narrow down which query/ies it may be. Once
> you have located one or more queries, you can look into how to improve
> them, for instance by adding indexes.
> Depending on your budget, you may also consider putting SQL Server on a
> separate box, so that the web server and SQL Server does not have to
> compete for resources.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||JJ (nospam) writes:
> I am on the verge of investing in a new server anyway, as it seems
> easier to throw more hardware at it, although I appreciate this is not
> necessarily the best solution.

Hey, if a new server costs 30000 crowns (a modest and cheap one), and
a consultant costs 1000 crowns an hour, and it takes him 40 hours to
track down the problem and fix it, throwing hardware on it may even
be a better solution.

Although judging from your description it sounds like you will still
have performance problems even with a new box, but hopefully less
severe - and maybe you can live with them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Coverting Access data to SQL query

Hi There
im try to find out if there is a way, of converting an Access db to a .SQL extension file.

I have some web space with a MySQL db attached, and i use phpMyAdmin to update, drop etc the database (i have no direct contact with it). I want all the data in my Access db onto my MySQL db without having to enter each row in. phpMyAdmin can use .SQL files to populate db, but i need to export the Access data into an SQL file format.
CAN ANYONE HELP?

AzureI'd suggest that you use an ETL tool. That makes this process much easier, although it assumes that you can get progmatic access to both of the databases.

If you can get ODBC access to both databases (MS-Access is a "gimme", the MySQL database could be easy or a challenge), then you have lots of choices at very low cost.

You can generate an SQL script from an Access database progmatcially, but it is often quite a bit of work. I'd strongly suggest that you investigate other choices first.

-PatP