how to schedule sql profiler trace job

I would like to know how to schedule a sql profiler trace
job in batch mode. From what I can find, sql profiler
can only be started manually. Any clue will be
appreciated.Wen,
See if this helps..
--SQL 2000:
INF: Job to Monitor SQL Server 2000 Performance and Activity
http://www.support.microsoft.com/?id=283696
--SQL 7.0:
INF: Job to Monitor SQL Server 7.0 Performance and Activity
http://www.support.microsoft.com/?id=286191
--SQL 6.5:
INF: How to Automate SQL Trace by Means of Scheduled Tasks
http://www.support.microsoft.com/?id=194860
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Wen Chang" <wchang@.siac.com> wrote in message
news:034c01c3465c$ebc30cf0$a501280a@.phx.gbl...
> I would like to know how to schedule a sql profiler trace
> job in batch mode. From what I can find, sql profiler
> can only be started manually. Any clue will be
> appreciated.|||Dinesh,
Thank you very much for the info.
I will try it out.
Wen Chang
>--Original Message--
>Wen,
>See if this helps..
> --SQL 2000:
> INF: Job to Monitor SQL Server 2000 Performance and
Activity
> http://www.support.microsoft.com/?id=283696
>--SQL 7.0:
> INF: Job to Monitor SQL Server 7.0 Performance and
Activity
> http://www.support.microsoft.com/?id=286191
>
>--SQL 6.5:
> INF: How to Automate SQL Trace by Means of Scheduled
Tasks
> http://www.support.microsoft.com/?id=194860
>
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Wen Chang" <wchang@.siac.com> wrote in message
>news:034c01c3465c$ebc30cf0$a501280a@.phx.gbl...
>> I would like to know how to schedule a sql profiler
trace
>> job in batch mode. From what I can find, sql profiler
>> can only be started manually. Any clue will be
>> appreciated.
>
>.
>

how to run sql log shipping manually

Hi everyone

In my project, sometimes, I need to run log shipping manually rather that it runs automally when the schedule occured.

manually run log shipping means call the log shipping in code.

Thanks.

Are you using SQL2000 or SQL2005?

====================
For SQL2000, log shipping usesSqlmaint.exe to back up and to restore databases. When SQL Server creates a transaction log backup as part of a log shipping setup, Sqlmaint.exe connects to the monitor server and updates the log_shipping_primaries table with the last_backup_filename information. Similarly, when you run a Copy or a Restore job on a secondary server, Sqlmaint.exe connects to the monitor server and updates the log_shipping_secondaries table.

====================

Fro SQL2005,?after we enable and configure log shipping for the specific database in SQL Server 2005, several agent jobs are created to maintain a series of steps for log shipping. Generally, this includes a backup job for primary server, a copy and a restore job for secondary server and an alert job for monitor server.

While SQL Agent is running, the job invocation engine monitors the schedule and prepares to launch the job next time. Then, the job manager fetches the detailed steps of the active job from msdb tables and executes it. As for the backup/copy/restore jobs, they involve cmdshell subsystem to launch the external executable sqllogship.exe (SMO application) to finish the specific tasks. Sqllogship.exe will update the information in log_shipping_monitor_primary, log_shipping_monitor_secondary, log_shipping_monitor_history_detail, and log_shipping_monitor_error_detail tables on primary or secondary server. This is implemented by running some undocumented log shipping stored procedures in master database, including:

sp_MSadd_log_shipping_history_detail

sp_MSadd_log_shipping_error_detail

and directly modify those correlated columns in log_shipping_monitor_primary, log_shipping_monitor_secondary tables.

At the end of the specific task in each sqllogship.exe execution, SQL Server will issue a series of steps to synchronize the information between primary/secondary server and the monitor server. This is also implemented by running some undocumented log shipping stored procedures in master database, including:

sp_processlogshippingmonitorprimary

sp_processlogshippingmonitorsecondary

sp_processlogshippingmonitorhistory

sp_processlogshippingretentioncleanup

If the monitor server is a remote machine to the SQL box where the sqllogship.exe is running, a distributed query for these sprocs with linked server built in-house is being used to update the info on the remote monitor server.

Next, when each job is finished, the correlated job history info is recorded in the msdb tables by the SQL Agent.|||your answer is very nice, thank you very much !|||I'm glad that I can help Happy new year my friend!sql

How to run replace on all columns

Here is my replace query and I need to run this on every column in my
table. Right now I manually enter the column name (_LANGUAGES_SPOKEN)
but this is time consuming and would like to automate this process as
much as possible.

Update PROFILE
SET LANGUAGES_SPOKEN = replace(cast(_LANGUAGES_SPOKEN as
nvarchar(255)),char(13)+char(10),':')

Thanks,
JPJackpipE (pipe.jack@.gmail.com) writes:

Quote:

Originally Posted by

Here is my replace query and I need to run this on every column in my
table. Right now I manually enter the column name (_LANGUAGES_SPOKEN)
but this is time consuming and would like to automate this process as
much as possible.
>
Update PROFILE
SET LANGUAGES_SPOKEN = replace(cast(_LANGUAGES_SPOKEN as
nvarchar(255)),char(13)+char(10),':')

There is no way to loop through the columns in a table in a simple
fashion. This is because that it would rarely make any sense; columns
in a table are supposed to described distinct attribuets.

For a thing like this I would do:

SELECT 'UPDATE PROFILE SET ' + name + ' replace(substring( ' +
name + ', 1, 255), char(13) + char(10), '':'')'
FROM syscolumns
WHERE id = object_id('PROFILE')
and type_name(xtype) like '%char'

and the copy, paste and run result.

--
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|||There is no way to loop through the columns in a table in a simple

Quote:

Originally Posted by

fashion. This is because that it would rarely make any sense; columns
in a table are supposed to described distinct attribuets.
>
For a thing like this I would do:
>
SELECT 'UPDATE PROFILE SET ' + name + ' replace(substring( ' +
name + ', 1, 255), char(13) + char(10), '':'')'
FROM syscolumns
WHERE id = object_id('PROFILE')
and type_name(xtype) like '%char'
>
and the copy, paste and run result.

Well that simplify my job but still does not automate the process to a
point where one query execution will take care of entire table.

Thanks.|||I don't see why you would have to update each column in an individual
query. Why not SET all the columns in one UPDATE? The code below
would simplify that. @.tablename is used rather than a hardcoded value
to facilitate turning it into a stored procedure.

declare @.tblname nvarchar(60)
set @.tblname = 'PROFILE'

SELECT CASE WHEN C.colid = 1
THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
' SET '
ELSE ' '
END +
C.name + '= replace(cast(' + C.name +
' as nvarchar(255)),char(13)+char(10),'':'')' +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE ';'
END
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @.tblname
ORDER BY C.id, C.colid

Output from one test exeuction:

UPDATE HoldEventsTable
SET TelephoneCallID= replace(cast(TelephoneCallID as
nvarchar(255)),char(13)+char(10),':'),
Time= replace(cast(Time as
nvarchar(255)),char(13)+char(10),':'),
Event= replace(cast(Event as
nvarchar(255)),char(13)+char(10),':');

Roy Harvey
Beacon Falls, CT

On 22 Feb 2007 16:01:40 -0800, "JackpipE" <pipe.jack@.gmail.comwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>There is no way to loop through the columns in a table in a simple
>fashion. This is because that it would rarely make any sense; columns
>in a table are supposed to described distinct attribuets.
>>
>For a thing like this I would do:
>>
> SELECT 'UPDATE PROFILE SET ' + name + ' replace(substring( ' +
> name + ', 1, 255), char(13) + char(10), '':'')'
> FROM syscolumns
> WHERE id = object_id('PROFILE')
> and type_name(xtype) like '%char'
>>
>and the copy, paste and run result.

>
>
>Well that simplify my job but still does not automate the process to a
>point where one query execution will take care of entire table.
>
>Thanks.

|||On Feb 22, 8:52 pm, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

I don't see why you would have to update each column in an individual
query. Why not SET all the columns in one UPDATE? The code below
would simplify that. @.tablename is used rather than a hardcoded value
to facilitate turning it into a stored procedure.
>
declare @.tblname nvarchar(60)
set @.tblname = 'PROFILE'
>
SELECT CASE WHEN C.colid = 1
THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
' SET '
ELSE ' '
END +
C.name + '= replace(cast(' + C.name +
' as nvarchar(255)),char(13)+char(10),'':'')' +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE ';'
END
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @.tblname
ORDER BY C.id, C.colid
>
Output from one test exeuction:
>
UPDATE HoldEventsTable
SET TelephoneCallID= replace(cast(TelephoneCallID as
nvarchar(255)),char(13)+char(10),':'),
Time= replace(cast(Time as
nvarchar(255)),char(13)+char(10),':'),
Event= replace(cast(Event as
nvarchar(255)),char(13)+char(10),':');
>
Roy Harvey
Beacon Falls, CT
>

Roy,

I had different output when I ran your query:
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':');

It looked like select statement output with 17 rows (17 columns in the
table) like the above. No UPDATE or SET function.|||On 22 Feb 2007 19:33:42 -0800, "JackpipE" <pipe.jack@.gmail.comwrote:

Quote:

Originally Posted by

Roy,
>
>I had different output when I ran your query:
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':');
>
>It looked like select statement output with 17 rows (17 columns in the
>table) like the above. No UPDATE or SET function.

I assume you changed the column names to all be _NAME, rather than the
query actually returning that.

The missing UPDATE and SET is what I would expect if the query was
written to filter out the first column. The specification said every
column, so I did not write the query to allow for that. In the first
CASE the first WHEN test would have to change from the simple:

WHEN C.colid = 1

To something like:

WHEN C.colid = (SELECT MIN(x.colid) FROM syscolumns as X WHERE X.id =
O.id AND <whatever filtering was used in the outer WHERE clause>)

Likewise the subquery in the last CASE would have to add the same
tests to match the WHERE clause.

Roy Harvey
Beacon Falls, CT|||Please post responses to the newsgroup, not to email. This promotes
the basic function of newsgroups, sharing information. It also means
that more than one person is reading and thinking about your problem.

If you did not add a WHERE clause test to limit the columns then I am
quite surprised that the UPDATE line did not appear in the output.
That would seem to indicate that there is no colid = 1 for the table.
I was able to create that condition by doing an ALTER TABLE to drop
the first column, but it is a condition my original query did not
allow for.

Did you try the alternate syntax I provided? What do you get from
this query?

SELECT MIN(colid), MAX(colod), count(colid), count(distinct colid)
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @.tblname

As for executing the code from inside the stored procedure, it is
possible. The command is spread over many rows, so the first step is
to turn that query into a cursor and step through the rows
concatenating all of them into a single string. Then you would have
to use dynamic SQL to execute it. Before doing that I suggest reading
this article very carefully: http://www.sommarskog.se/dynamic_sql.html
Roy Harvey
Beacon Falls, CT

Quote:

Originally Posted by

>Roy,
>
>Thank you for your time and helping me out. In my last reply the
>output I copied was wrong. Here is the code and output I get from your
>script:
>declare @.tblname nvarchar(60)
>set @.tblname = '_PHYSICIAN_PROFILE'
>
>SELECT CASE WHEN C.colid = 1
THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
' SET '
ELSE ' '
END +
C.name + '= replace(cast(' + C.name +
' as nvarchar(255)),char(13)+char(10),'':'')' +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE ';'
END
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @.tblname
ORDER BY C.id, C.colid
>
>======= output 42 rows =========
>
_NAME= replace(cast(_NAME as
>nvarchar(255)),char(13)+char(10),':'),
_SPECIALTY= replace(cast(_SPECIALTY as
>nvarchar(255)),char(13)+char(10),':'),
_GENDER= replace(cast(_GENDER as
>nvarchar(255)),char(13)+char(10),':'),
_SPECIAL_INTERESTS= replace(cast(_SPECIAL_INTERESTS as
>nvarchar(255)),char(13)+char(10),':'),
_PRACTICE_HIGHLIGHTS= replace(cast(_PRACTICE_HIGHLIGHTS as
>nvarchar(255)),char(13)+char(10),':'),
_TRAINING_POST_GRADUATE_EDUCATION=
>replace(cast(_TRAINING_POST_GRADUATE_EDUCATION as
>nvarchar(255)),char(13)+char(10),':'),
_BOARD_CERTIFICATION= replace(cast(_BOARD_CERTIFICATION as
>nvarchar(255)),char(13)+char(10),':'),
_LANGUAGES_SPOKEN= replace(cast(_LANGUAGES_SPOKEN as
>nvarchar(255)),char(13)+char(10),':'),
_INSURANCE_ACCEPTED= replace(cast(_INSURANCE_ACCEPTED as
>nvarchar(255)),char(13)+char(10),':'),
_PERSONAL_INFORMATION= replace(cast(_PERSONAL_INFORMATION as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS1_1= replace(cast(_ADDRESS1_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS1_2= replace(cast(_ADDRESS1_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS1_3= replace(cast(_ADDRESS1_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE1= replace(cast(_PHONE1 as
>nvarchar(255)),char(13)+char(10),':'),
_FAX1= replace(cast(_FAX1 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK1= replace(cast(_IN_NETWORK1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS2_1= replace(cast(_ADDRESS2_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS2_2= replace(cast(_ADDRESS2_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS2_3= replace(cast(_ADDRESS2_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE2= replace(cast(_PHONE2 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK2= replace(cast(_IN_NETWORK2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS3_1= replace(cast(_ADDRESS3_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS3_2= replace(cast(_ADDRESS3_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS3_3= replace(cast(_ADDRESS3_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE3= replace(cast(_PHONE3 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK3= replace(cast(_IN_NETWORK3 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS4_1= replace(cast(_ADDRESS4_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS4_2= replace(cast(_ADDRESS4_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS4_3= replace(cast(_ADDRESS4_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE4= replace(cast(_PHONE4 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK4= replace(cast(_IN_NETWORK4 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS5_1= replace(cast(_ADDRESS5_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS5_2= replace(cast(_ADDRESS5_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS5_3= replace(cast(_ADDRESS5_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE5= replace(cast(_PHONE5 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK5= replace(cast(_IN_NETWORK5 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS6_1= replace(cast(_ADDRESS6_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS6_2= replace(cast(_ADDRESS6_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS6_3= replace(cast(_ADDRESS6_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE6= replace(cast(_PHONE6 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK6= replace(cast(_IN_NETWORK6 as
>nvarchar(255)),char(13)+char(10),':'),
META_SRC_URI= replace(cast(META_SRC_URI as
>nvarchar(255)),char(13)+char(10),':');
>
>I don't think I filter out anything yet I don't have UPDATE or SET
>function and the output from this query is just like a select
>statement that does not execute the replacement.
>
>Is there a way actually execute the replace from this stored procedure?

How To Restrict End User to update field in database manually....

Dear Freinds..

I want to protect a field in a table...i want to restrict users to update the value in that field...by manually logging into that database...it can be updated only through the application...if any body manually update the field value...it has to be captured in log with old value...is it possible to do this sql server...if any of u says yes 'its possible' then :beer: other wise :eek:Why do you allow users to manually log into the database in the first place? If you follow good database application design principles and limit all access to the database to stored procedures, you won't have this problem.|||triggers and history tables

http://weblogs.sqlteam.com/brettk/archive/2004/10/20/2242.aspx|||Dear Blindman,

thks for ur opinion ,i respect ur opinion.our aplication is a huge distributed application its running across 4000 location...we are having all the security design in database level..still worrying about some smart users...and our clients very concern on some values should not be tampered on database...since they had those worst experience previously...and more over 100 people are giving support for this app..who is having rights to access the database.......

Cheers
Sathesh.M|||Can you give the support users SELECT permissions, but not UPDATE permissions? That woud allow them to "see" the data, but not to change it.

-PatP|||triggers and history tables

http://weblogs.sqlteam.com/brettk/archive/2004/10/20/2242.aspx

Damn it. I have to start reading your blog. I just wrote something very similar but mine does not have any caveats and I had to deal with synchronizing some existing history tables with the live tables.|||Can you give the support users SELECT permissions, but not UPDATE permissions? That woud allow them to "see" the data, but not to change it.

-PatP
...and even then, you should not allow them to view the tables directly. You should create SQL Views for the data they are allowed to see and then grant SELECT access to those views.|||...and even then, you should not allow them to view the tables directly. You should create SQL Views for the data they are allowed to see and then grant SELECT access to those views.Yeah, but I was trying to KISS.

-PatP