Creating a job from a trigger

Hello, All
I have a trigger on a table, which when called, creates a job to run in n
minutes. The trigger runs fine and the job is created, however with a small
issue.
The username that is used as the Job Owner, ends up to be the name of the
user that triggered the trigger eventhough the code that creates the job
specifies a different name.
What am I doing wrong? How can I resolve this?
Here is the SP that is run in the trigger:
exec msdb..sp_add_job @.job_name = 'TEST',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 0,
@.delete_level = 1
The code above says user SA but the job ends up running as TESTUSER and it
makes the job fail because a that user is not a SA nor able to run a CMD
job.
Please help!
Thank you.slamm wrote:
> Hello, All
> I have a trigger on a table, which when called, creates a job to run
> in n minutes. The trigger runs fine and the job is created, however
> with a small issue.
> The username that is used as the Job Owner, ends up to be the name of
> the user that triggered the trigger eventhough the code that creates
> the job specifies a different name.
> What am I doing wrong? How can I resolve this?
> Here is the SP that is run in the trigger:
> exec msdb..sp_add_job @.job_name = 'TEST',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 0,
> @.delete_level = 1
> The code above says user SA but the job ends up running as TESTUSER
> and it makes the job fail because a that user is not a SA nor able to
> run a CMD job.
> Please help!
> Thank you.
I don't think that's going to work. If it did, then any user who had job
creation rights could alias a job as the system administrator, giving
them more rights in the process. You can grant the user rights to do
what needs to run or you can do the following:
Instead of creating a job directly from the trigger, insert the
necessary job criteria into a custom table of application jobs (to be
created). Create a recurring job on the server that monitors this table
at specified intervals and creates the jobs itself. Then you don't even
have to grant users job creation rights. They only need rights to your
application job table.
David Gugick
Imceda Software
www.imceda.com

Creating a Connection to Database that Does Not Yet Exist

Hello All!
I am in the process of developing a DTS package. My
package will behave as follows:
1. Backup my live database.
2. Using the backup from Step 1., the backup will be
restored using a DIFFERENT name (NewDatabase).
3. Manipulate NewDatabase database.
How can I create a connection in my DTS Package when, at
the time I am creating the package, the NewDatabase
database does not exist? Do I have to use Disconnected
Edits? If so, how? Or, do I use Microsoft Data Link?
If so, how? Or, do I use something else. (As you can
tell from my questions, I am not an expert at this :-)
Any suggestions are greatly appreciated!!You should be able to connect to the master database to do what you need to
do initially.
Ray Higdon MCSE, MCDBA, CCNA
--
"ERR" <anonymous@.discussions.microsoft.com> wrote in message
news:ef7b01c3f1b0$3f8eff20$a301280a@.phx.gbl...
> Hello All!
> I am in the process of developing a DTS package. My
> package will behave as follows:
> 1. Backup my live database.
> 2. Using the backup from Step 1., the backup will be
> restored using a DIFFERENT name (NewDatabase).
> 3. Manipulate NewDatabase database.
> How can I create a connection in my DTS Package when, at
> the time I am creating the package, the NewDatabase
> database does not exist? Do I have to use Disconnected
> Edits? If so, how? Or, do I use Microsoft Data Link?
> If so, how? Or, do I use something else. (As you can
> tell from my questions, I am not an expert at this :-)
> Any suggestions are greatly appreciated!!|||I forgot to mention that I do have an initial connection,
the one to the original database. However, I need my DTS
package to create a connection to the NewDatabase
database once the task of restoring with a new name is
complete.
Thanks in advance!

>--Original Message--
>You should be able to connect to the master database to
do what you need to
>do initially.
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"ERR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ef7b01c3f1b0$3f8eff20$a301280a@.phx.gbl...
at
>
>.
>|||To do what? If it's only to run a sql script you can use workflow and
connect to the master database and then do the "use yournewdb" command after
you have created it
Ray Higdon MCSE, MCDBA, CCNA
--
"ERR" <anonymous@.discussions.microsoft.com> wrote in message
news:f43101c3f22e$9ff978d0$a301280a@.phx.gbl...
> I forgot to mention that I do have an initial connection,
> the one to the original database. However, I need my DTS
> package to create a connection to the NewDatabase
> database once the task of restoring with a new name is
> complete.
> Thanks in advance!
>
> do what you need to
> message
> at|||what you need to do as per Ray's suggestion is to create the dts package and
set it to connect to master, then create a dynamic task properties and a
global variable <dbname> to change the value of catalog property in
Disconnect Edit DE at run time.
then when you execute the dts package need to execute with the /A switch to
pass the <db> variable which u proide at run time see example below
dtsrun /S <servername> /E /N<packagename> /A <db>:8=newdatabase
this way u can connect to a database at runtime.
--
Olu Adedeji
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:eR5zJ8y8DHA.3880@.tk2msftngp13.phx.gbl...
> To do what? If it's only to run a sql script you can use workflow and
> connect to the master database and then do the "use yournewdb" command
after
> you have created it
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "ERR" <anonymous@.discussions.microsoft.com> wrote in message
> news:f43101c3f22e$9ff978d0$a301280a@.phx.gbl...
>

Createa table like a existing table?

Hai all

I am new to SLQ server. Can anyone tell me how to create a table that
should look like a existing table fields...

thanxSSG (ssg14j@.gmail.com) writes:
> I am new to SLQ server. Can anyone tell me how to create a table that
> should look like a existing table fields...

You can do "SELECT * INTO newtbl FROM tbl WHERE 1 = 0". Beware though
that constraints, indexes and triggers are not copied. If you want that
you are better off scripting the table, which you can do by right-clicking
the table in the Object Broswer in Query Analyzer. Even better, have all
your source code under version control. Then copying is even easier.

--
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|||On Tue, 27 Dec 2005 08:13:25 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>SSG (ssg14j@.gmail.com) writes:
>> I am new to SLQ server. Can anyone tell me how to create a table that
>> should look like a existing table fields...
>You can do "SELECT * INTO newtbl FROM tbl WHERE 1 = 0". Beware though
>that constraints, indexes and triggers are not copied. If you want that
>you are better off scripting the table, which you can do by right-clicking
>the table in the Object Broswer in Query Analyzer. Even better, have all
>your source code under version control. Then copying is even easier.

Also note that in most cases where you would want to copy a table's design
within the same database, you'd probably be better off doing more
normalization instead.

Create view using SMO

Hi All

i m building an application in which i have to create database view using SMO.

The problem i m facing is that when i use the View.Columns.Add function to add a particular column to the view i am getting an error .

"Parent property of object [Name] does not match the collection's parent to which it is added."

The following is the piece of code i have written

Server server = new Server(Environment.MachineName);

Database db = server.Databases["AdventureWorks"];

Microsoft.SqlServer.Management.Smo.Table tab1 = db.Tables["ProductModel", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab2 = db.Tables["Product", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab3 = db.Tables["ProductModelProductDescriptionCulture", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab4 = db.Tables["ProductDescription", "Production"];

Column c1=tab1.Columns["Name"];

Column c2 = tab2.Columns["ProductID"];

Column c3 = tab2.Columns["Name"];

Column c4 = tab3.Columns["CultureID"];

Column c5 = tab4.Columns["Description"];

Microsoft.SqlServer.Management.Smo.View dataview = new Microsoft.SqlServer.Management.Smo.View(db, "Trial", "Production");

dataview.TextMode = false;

dataview.Columns.Add(c1);

dataview.Columns.Add(c2);

dataview.Columns.Add(c3);

dataview.Columns.Add(c4);

dataview.Columns.Add(c5);

dataview.Create();

The other way to create view i.e using the Textheader and the TextBody properties is working fine but this one giving some error

Can anyone help me with this

Thanls in advance.

MItesh

Mitesh, the error you're getting is because those columns belong to the parent tables, and you can't just assign them to the view. The TextHeader and TextBody properties are the correct way to build a view, just as they're the correct properties to use to build a stored procedure in SMO.|||

Hey Allen

thanks for the reply

the problems is that i m building an administrative application in which i have to provide a GUI to the user, through which the user can select some columns of a table (using checkboxes) just as they do it in Management Studio.

Now the point here is that it becomes dificult for a naive user to write the create view query for View creation

so the GUI is a must

Can u suggest some other way i can use the View class

Also if i cant use the View.Columns.Add method wats the point in providing such methods

Do help me out with this

Thanks again

Keep posting

|||

Mitesh,

The View object inherits from the TableViewBase object, so the view has the same objects and methods as the table has. That doesn't mean they are always appropriate given the context. You can evaluate the columns in an existing view using the Columns objects but I haven't found a way to define the source table or tables, or the where clause containing your limiting conditions, using SMO objects.

You should be able to build a SQL string, however, from the selected columns and limiting conditions presented in your GUI, and send that string to the TextBody property to build the view, as you require.

Create view using SMO

Hi All

i m building an application in which i have to create database view using SMO.

The problem i m facing is that when i use the View.Columns.Add function to add a particular column to the view i am getting an error .

"Parent property of object [Name] does not match the collection's parent to which it is added."

The following is the piece of code i have written

Server server = new Server(Environment.MachineName);

Database db = server.Databases["AdventureWorks"];

Microsoft.SqlServer.Management.Smo.Table tab1 = db.Tables["ProductModel", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab2 = db.Tables["Product", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab3 = db.Tables["ProductModelProductDescriptionCulture", "Production"];

Microsoft.SqlServer.Management.Smo.Table tab4 = db.Tables["ProductDescription", "Production"];

Column c1=tab1.Columns["Name"];

Column c2 = tab2.Columns["ProductID"];

Column c3 = tab2.Columns["Name"];

Column c4 = tab3.Columns["CultureID"];

Column c5 = tab4.Columns["Description"];

Microsoft.SqlServer.Management.Smo.View dataview = new Microsoft.SqlServer.Management.Smo.View(db, "Trial", "Production");

dataview.TextMode = false;

dataview.Columns.Add(c1);

dataview.Columns.Add(c2);

dataview.Columns.Add(c3);

dataview.Columns.Add(c4);

dataview.Columns.Add(c5);

dataview.Create();

The other way to create view i.e using the Textheader and the TextBody properties is working fine but this one giving some error

Can anyone help me with this

Thanls in advance.

MItesh

Mitesh, the error you're getting is because those columns belong to the parent tables, and you can't just assign them to the view. The TextHeader and TextBody properties are the correct way to build a view, just as they're the correct properties to use to build a stored procedure in SMO.|||

Hey Allen

thanks for the reply

the problems is that i m building an administrative application in which i have to provide a GUI to the user, through which the user can select some columns of a table (using checkboxes) just as they do it in Management Studio.

Now the point here is that it becomes dificult for a naive user to write the create view query for View creation

so the GUI is a must

Can u suggest some other way i can use the View class

Also if i cant use the View.Columns.Add method wats the point in providing such methods

Do help me out with this

Thanks again

Keep posting

|||

Mitesh,

The View object inherits from the TableViewBase object, so the view has the same objects and methods as the table has. That doesn't mean they are always appropriate given the context. You can evaluate the columns in an existing view using the Columns objects but I haven't found a way to define the source table or tables, or the where clause containing your limiting conditions, using SMO objects.

You should be able to build a SQL string, however, from the selected columns and limiting conditions presented in your GUI, and send that string to the TextBody property to build the view, as you require.