automating profiler

I have created a stored proc that will automate the capture of traces. The trace is captured to a file instead of a table because tracing to a table on the same server has caused performance issues.
The problem is that I really want the trace in a table so that we can produce reports with Reporting Services. I know that I can manually save the trace to a trace table. But is there a way to automate this process? I can't find any command line parameter
s for Profiler.
Sure, take a look at fn_trace_gettable in BOL. By the way you almost never
want to trace directly to a table if you care about performance.
Andrew J. Kelly SQL MVP
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:75FFDF10-B0AA-4AD4-8573-0A241F630717@.microsoft.com...
> I have created a stored proc that will automate the capture of traces. The
trace is captured to a file instead of a table because tracing to a table on
the same server has caused performance issues.
> The problem is that I really want the trace in a table so that we can
produce reports with Reporting Services. I know that I can manually save the
trace to a trace table. But is there a way to automate this process? I can't
find any command line parameters for Profiler.
>
>
|||Is there a SQL 7.0 soluthion?
"Andrew J. Kelly" wrote:

> Sure, take a look at fn_trace_gettable in BOL. By the way you almost never
> want to trace directly to a table if you care about performance.
> --
> Andrew J. Kelly SQL MVP
>
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:75FFDF10-B0AA-4AD4-8573-0A241F630717@.microsoft.com...
> trace is captured to a file instead of a table because tracing to a table on
> the same server has caused performance issues.
> produce reports with Reporting Services. I know that I can manually save the
> trace to a trace table. But is there a way to automate this process? I can't
> find any command line parameters for Profiler.
>
>
|||Not that I am aware of.
Andrew J. Kelly SQL MVP
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:DC0B3779-1875-40D4-848B-31473782A038@.microsoft.com...[vbcol=seagreen]
> Is there a SQL 7.0 soluthion?
> "Andrew J. Kelly" wrote:
never[vbcol=seagreen]
The[vbcol=seagreen]
table on[vbcol=seagreen]
the[vbcol=seagreen]
can't[vbcol=seagreen]
|||Do you know anything about the xp_trace_opentracefile stored proc in 7.0? Is it possible that this will do the same thing as fn_trace_gettable in 2000?
"Andrew J. Kelly" wrote:

> Not that I am aware of.
> --
> Andrew J. Kelly SQL MVP
>
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:DC0B3779-1875-40D4-848B-31473782A038@.microsoft.com...
> never
> The
> table on
> the
> can't
>
>
|||Do you know anything about the extended stored proc called xp_trace_opentracefile?
Will this do the same thing as fn_trace_gettable?
"Andrew J. Kelly" wrote:

> Not that I am aware of.
> --
> Andrew J. Kelly SQL MVP
>
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:DC0B3779-1875-40D4-848B-31473782A038@.microsoft.com...
> never
> The
> table on
> the
> can't
>
>
|||I am not familiar with the xp and don't have 7.0 anymore.
Andrew J. Kelly SQL MVP
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:5302ACCD-993C-4CD6-914A-0B2FEC8B8203@.microsoft.com...
> Do you know anything about the extended stored proc called
xp_trace_opentracefile?[vbcol=seagreen]
> Will this do the same thing as fn_trace_gettable?
> "Andrew J. Kelly" wrote:
almost[vbcol=seagreen]
traces.[vbcol=seagreen]
can[vbcol=seagreen]
save[vbcol=seagreen]
I[vbcol=seagreen]
|||The SQL Server 7.0 resource kit has a COM+ object that will allow you to
read a SQL Server 7.0 formatted trace. I don't have sample code handy but it
wasn't hard to figure out how to import the trace using some simple VB
code...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:DC0B3779-1875-40D4-848B-31473782A038@.microsoft.com...[vbcol=seagreen]
> Is there a SQL 7.0 soluthion?
> "Andrew J. Kelly" wrote:
never[vbcol=seagreen]
The[vbcol=seagreen]
table on[vbcol=seagreen]
the[vbcol=seagreen]
can't[vbcol=seagreen]
|||Thank you, I'll give it a try.
"Brian Moran" wrote:

> The SQL Server 7.0 resource kit has a COM+ object that will allow you to
> read a SQL Server 7.0 formatted trace. I don't have sample code handy but it
> wasn't hard to figure out how to import the trace using some simple VB
> code...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:DC0B3779-1875-40D4-848B-31473782A038@.microsoft.com...
> never
> The
> table on
> the
> can't
>
>
|||I can't find the resource kit on the original 7.0 disks. The only sql server resource kit that I find under the MSDN subscriber downloads is for sql server 2000. Is there another place that I need to look?
"Brian Moran" wrote:

> The SQL Server 7.0 resource kit has a COM+ object that will allow you to
> read a SQL Server 7.0 formatted trace. I don't have sample code handy but it
> wasn't hard to figure out how to import the trace using some simple VB
> code...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:DC0B3779-1875-40D4-848B-31473782A038@.microsoft.com...
> never
> The
> table on
> the
> can't
>
>

Automating deployment of maintenance plans

Hello,

I have created a Maintenance Plan on our development SQL Server 2005 Standard using the designer in SQL Server Management Studio. The plan backs up databases and transaction logs to a hard disk and does some cleanup too. It is scheduled to run nightly. This plan needs to be deployed to 13 production sites by someone else not familiar with SQL Server.

Can I use some combination of a SQL script, an export of the maintenance plan, and/or a batch file to automate the deployment of this plan and it's schedule to servers at several different sites? The deployment team will have admin remote desktop access to the production SQL Servers, which also have SQL Management Studio installed but we cannot expect the team to recreate the plan manually on each site.

I haven't been able to find much documentation on doing this automatically. Any help will be appreciated.

Thank you,

- Jason

Create a SSIS package to perform this maintenance plan task and use DTUTIL to deploy on multiple servers.

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE fyi.

sql

automatically set database to simple recovery upon creation

Is there a way to set the recovery model of any new databases created on SQL
Server 2005 to Simple instead of Full? I would like the recovery model to
default to Simple for any new databases created on the server.
Thanks for your help!
Recovery model is inherited from the model database. So you can set model to simple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> Is there a way to set the recovery model of any new databases created on SQL
> Server 2005 to Simple instead of Full? I would like the recovery model to
> default to Simple for any new databases created on the server.
> Thanks for your help!
|||awesome! Thanks!
"Tibor Karaszi" wrote:

> Recovery model is inherited from the model database. So you can set model to simple.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
>

automatically set database to simple recovery upon creation

Is there a way to set the recovery model of any new databases created on SQL
Server 2005 to Simple instead of Full? I would like the recovery model to
default to Simple for any new databases created on the server.
Thanks for your help!Recovery model is inherited from the model database. So you can set model to simple.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> Is there a way to set the recovery model of any new databases created on SQL
> Server 2005 to Simple instead of Full? I would like the recovery model to
> default to Simple for any new databases created on the server.
> Thanks for your help!|||awesome! Thanks!
"Tibor Karaszi" wrote:
> Recovery model is inherited from the model database. So you can set model to simple.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> > Is there a way to set the recovery model of any new databases created on SQL
> > Server 2005 to Simple instead of Full? I would like the recovery model to
> > default to Simple for any new databases created on the server.
> >
> > Thanks for your help!
>

automatically set database to simple recovery upon creation

Is there a way to set the recovery model of any new databases created on SQL
Server 2005 to Simple instead of Full? I would like the recovery model to
default to Simple for any new databases created on the server.
Thanks for your help!Recovery model is inherited from the model database. So you can set model to
simple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> Is there a way to set the recovery model of any new databases created on S
QL
> Server 2005 to Simple instead of Full? I would like the recovery model to
> default to Simple for any new databases created on the server.
> Thanks for your help!|||awesome! Thanks!
"Tibor Karaszi" wrote:

> Recovery model is inherited from the model database. So you can set model
to simple.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
>

Automatic index (Statistics) vs Manually created indexes

Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
Erik
Hi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:

> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created indexes
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexes
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>

Automatic index (Statistics) vs Manually created indexes

Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
ErikHi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:
> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created indexes
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexes
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>

Automatic index (Statistics) vs Manually created indexes

Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
ErikHi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:

> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created index
es
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexe
s
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>

Automatic Email subject

Good Morning,

I have created a varible in a report which displays as a text box i.e contains data like "LWD Apps 450" where the value will change each day. The report is emiled to a list of users, is there a way to automatically enter this value so that it is in the subject bar when the email is automatically sent?

Thanks in advance.

Steve

Hi Steve,

The entry form for subscriptions only allows for two variables (@.ReportName and @.ExecutionTime).

The only way I can think to modify the subscription on the fly would be to each day programmatically create a new subscription with a subject. Here's an msdn article with sample code:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

In the code, you would perform whatever operation you needed to get the same information that the RDL is getting. Then instead of this line:
extensionParams(4).Value = "@.ReportName was executed at @.ExecutionTime"
you would use this line:
extensionParams(4).Value = "LWD Apps " & variableThatIJustRetrieved

You would set the report to run once and the time to run right then (or a minute in the future). You would also want to delete the subscription after it has run, so that you would have a clean slate for tomorrow's run.

It's definitely kludgy though. Does anyone else have any thoughts?
-Jessica

Automatic Email subject

Good Morning,

I have created a varible in a report which displays as a text box i.e contains data like "LWD Apps 450" where the value will change each day. The report is emiled to a list of users, is there a way to automatically enter this value so that it is in the subject bar when the email is automatically sent?

Thanks in advance.

Steve

Hi Steve,

The entry form for subscriptions only allows for two variables (@.ReportName and @.ExecutionTime).

The only way I can think to modify the subscription on the fly would be to each day programmatically create a new subscription with a subject. Here's an msdn article with sample code:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

In the code, you would perform whatever operation you needed to get the same information that the RDL is getting. Then instead of this line:
extensionParams(4).Value = "@.ReportName was executed at @.ExecutionTime"
you would use this line:
extensionParams(4).Value = "LWD Apps " & variableThatIJustRetrieved

You would set the report to run once and the time to run right then (or a minute in the future). You would also want to delete the subscription after it has run, so that you would have a clean slate for tomorrow's run.

It's definitely kludgy though. Does anyone else have any thoughts?
-Jessica

Automatic Email

Dear Freind,
Its all boutt Dts.I have already created a DTSpackage and Activex script in VBscript to retrieve some particular names ...and i want to send those names through E mail.But the email should be automatic(using sql Sheduler ).Real Problem is...How can i write the code to access the DTS object from ASP.Net with VB? and How can shedule...please help me...i am hopefully waiting..........thanks in advanceIf you just want to send the mails queried from a table you can use the regular mail sending procedures from SQL Server like xp_sendmail (depends on your SQL Server version which to use)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Moving to the "SQL Server Integration Services" forum.

Automatic Database Backup

Hi all,

I have written an application in C# .NET which will run on another PC. The database required for the application will be created during the installation. How can I provide that the application backups the database periodically? Can I generate an SQL script which will run at installation and then backup the database automatically?

I really need advices.
Thank you...
BurcuIn Enterprise Manager you can set up automated maintenance procedures for backing up, re indexing, and repairing databases.|||

Quote:

Originally Posted by Motoma

In Enterprise Manager you can set up automated maintenance procedures for backing up, re indexing, and repairing databases.

Thank you. But, what I want to do is to realize it programmatically. I don't want that the user has to set up anything in Enterprise Manager. All the back up procedure must be organized during the installation of my application program.

An idea is to define a scheduled job on server agent by using SQL-DMO library. So, the job will back up the database periodically. But I am not sure if it is the best choice.
Any other ideas?|||

Quote:

Originally Posted by eflatunn

Thank you. But, what I want to do is to realize it programmatically. I don't want that the user has to set up anything in Enterprise Manager. All the back up procedure must be organized during the installation of my application program.

An idea is to define a scheduled job on server agent by using SQL-DMO library. So, the job will back up the database periodically. But I am not sure if it is the best choice.
Any other ideas?

I am sure there is a way to set up SQL Server maintenance plans without the Enterprise Manager IDE. I am sorry, but I do not know how exactly to do this. Your best bet, if you wanted to pursue this, would be to check out the MSDN and look through all of the system stored procedures. After that, you could try the setting up a Schedule with the SQL Profiler running to see if you could find out what the IDE is calling on the server.|||I just found something while digging through the help files. There are four stored procedures that you may be able to use: sp_add_jobschedule sp_delete_jobshedule sp_help_jobschedule and sp_update_jobschedule.|||hai
I also want to know how can i get the backup of a database programmatically
If any one know this please help me|||I included SQL-DMO library in my application. Using SQL-DMO objects, I create a job in SQL Server Agent and assign a schedule so that back up can be done periodically.|||Hope the following helps

backupDir = Directory.GetCurrentDirectory() + "\\DBBackup";
if (!Directory.Exists(backupDir))
{
Directory.CreateDirectory(backupDir);
}
datePart = DateTime.Now.ToString(dateFormat);

backupFileName = backupDir + "\\DBName_" + datePart + "_" + "backup.log";

backupQuery = "use master; if exists ( select 1 from sysdevices where name = 'DBName') exec sp_dropdevice 'DBName'; " +
"exec sp_addumpdevice 'disk', 'DBName', '" + backupFileName + "' ; backup database DBName to DBName";

try
{
//gets osql and runs osql tool to execute the DB scripts
ProcessStartInfo procInfo = new ProcessStartInfo("osql.exe");
// specifies the window style
procInfo.WindowStyle = ProcessWindowStyle.Hidden;
//specifies the arguments for the process
procInfo.Arguments = Common.GetCommonProcessArguments(backupQuery);
//starts the process
Process osql = Process.Start(procInfo);

//waits for all the dbscripts to run.
osql.WaitForExit();
osql.Dispose();
}

private static string GetCommonProcessArguments(string fileName)
{
// string to be passed to osql tool
string result = " -S " + Environment.MachineName + @." -E "+ "-n" + " -Q " + Char.ToString('"') +
fileName + Char.ToString('"') + " -o " + Char.ToString('"') + CurrentPath + "\\DBScriptsLog.txt";

return result;

}

automatic change of permission on database after install web site

I have Web Site with ASPNETDB database and own database. I was precompiled this site and I created aplication with Web Setup Project. This setup aplication includes SQL 2005 EXPRESS. When I installed and ran an my web site in Internet explorer, that displayed error "Failed to update database ... because the database is read-only". I know, that is a bug with permisson and the fix a bug is solution for this problem but i have installation package and i need automatic fix in setup application. Thanx for comments.

Vitty,

The main focus of this forum is on connectivty, client odbc/oledb drivers and etc. The expert on your issue might not visit this forum often. So, I suggest you to visite sql setup forum instead.

Automated SQL Server Startup

Is there any way start a SQL Server database through a script and still
be able to administrate the server through EM? I have created a scripts
for shutting it down and e-mailing me, but I would like to automate
startup through a scheduled task as well.
Thanks...
NathanFigured it out net start...
doh.
nathan
none@.nnn.com wrote:

> Is there any way start a SQL Server database through a script and still
> be able to administrate the server through EM? I have created a scripts
> for shutting it down and e-mailing me, but I would like to automate
> startup through a scheduled task as well.
> Thanks...
> Nathan
>|||Hi,
I've created 2 batch file, startsql.bat and stopsql.bat. Each have 'net str
at mssqlserver' and net stop mssqlserver' for starting and stoppping the ser
ver. I schedule it in the Schedule (under control panel) by selecting to ru
n the program as startsql.b
at or stopsql.bat and the user id is my window administartor login id and p
assword. I waited and watched and nothing happen. What am I not doing corr
ectly? Is it the batch file or is it the schedule not done correctly? I am
running Window 2000 and SQ
L 2000
Thanks,
Alpha

Automated restore of a user database

I am trying to restore a user database in a scheduled Job I created that executes the following command "restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101 "unable to get exclusive use".
Absolutely no one is on this server when I do this
From which database are you executing the restore command? Make sure it is the master database. you can run an
sp_who just before and get the output from the jobstep to a file and examine that file to see who is using the
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that executes the following command
"restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an
error 3101 "unable to get exclusive use". Absolutely no one is on this server when I do this
|||Hi,
Turn the database to single user before restore and make it multiuser after
restore:-
Change the script to do below :-
Alter database <dbname> set single_user with rollback immediate
go
restore database dbname from disk=...........
go
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this
|||ALTER DATABASE sqleligibilitydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE sqleligibilitydatabase SET ONLINE
RESTORE DATABASE......
That will clear all users out of the database. Also, make sure your job
step does not execute in the context of the target database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this

Automated restore of a user database

I am trying to restore a user database in a scheduled Job I created that exe
cutes the following command "restore database sqleligibilitydatabase from di
sk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101 "un
able to get exclusive use".
Absolutely no one is on this server when I do thisFrom which database are you executing the restore command? Make sure it is t
he master database. you can run an
sp_who just before and get the output from the jobstep to a file and examine
that file to see who is using the
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that executes
the following command
"restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibil
itydatabase_db_%.bak'". I get an
error 3101 "unable to get exclusive use". Absolutely no one is on this serv
er when I do this|||Hi,
Turn the database to single user before restore and make it multiuser after
restore:-
Change the script to do below :-
Alter database <dbname> set single_user with rollback immediate
go
restore database dbname from disk=...........
go
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this|||ALTER DATABASE sqleligibilitydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE sqleligibilitydatabase SET ONLINE
RESTORE DATABASE......
That will clear all users out of the database. Also, make sure your job
step does not execute in the context of the target database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this

Autoformat a table?

Hi. I created an SSRS report awhile back and it had a very nice design to it. I vaguely remember choosing an autoformat style for it, so that it would have blue headers and grid lines, etc. For the life of me, I can't find that feature. Where do you do you select one of the predesigned styles to apply to a report? Thanks.Those templates are part of the Report Wizard. You can launch the wizard by selecting to create a new report.|||Thanks for the response. Can you change a template after a report has been created; in other words, can the template be changed after running the wizard? Our users might want to go for a different look after they see a demo. Do we have to run the wizard again to use a different template? I've looked everywhere in SSRS but can't see any place where the template can be changed -- other than to change the XML code line by line. I wish it were as simple as saying reportTemplate = "something".

Auto time stamp in Sql. Express

Is there a property setting in SqlEX. That automatically inserts the date and time in to a field (timestamp) in the dB, when a record is created. If so can someone please show me how this is done.

Thanks in advance

You can set up the default value for the column as getdate() in the design view of the table.

Auto Sizing the Page Header

Is there a way to auto-size the height of the page header depending on it's
contents?
For example, I have created a page header with a logo that appears on the
first page (1 inch x 1 1/2 inches) as well as my report title. But on
consecutive pages only the title shows and the image gets surpressed. I
would like to see the header contract in size on consecutive pages so that
only the report title fits at the top in an effort to conserve page real
estate.
Is this possible?
Thanks,
LisaNo. The page header height is set at design time and cannot be modified at
runtime.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:%23jr%23nYuWEHA.3476@.tk2msftngp13.phx.gbl...
> Is there a way to auto-size the height of the page header depending on
it's
> contents?
> For example, I have created a page header with a logo that appears on the
> first page (1 inch x 1 1/2 inches) as well as my report title. But on
> consecutive pages only the title shows and the image gets surpressed. I
> would like to see the header contract in size on consecutive pages so that
> only the report title fits at the top in an effort to conserve page real
> estate.
> Is this possible?
> Thanks,
> Lisa
>

auto indexes

Using sp_helpindexes on a table, I found some indexes that is auto created
by SQLServer. The indexes' name all started with hind_. Does anyone know
what database options cause the server auto creating these indexes?
Thanks,
LijunThey are not indexes, they are statistics and are normal. They get created
automatically by the engine if you have Auto Create Stats turn on (which is
the default) and are nothing to worry about. They are actually a good
thing.
--
Andrew J. Kelly
SQL Server MVP
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:%23XnkwvOYDHA.736@.TK2MSFTNGP09.phx.gbl...
> Using sp_helpindexes on a table, I found some indexes that is auto created
> by SQLServer. The indexes' name all started with hind_. Does anyone know
> what database options cause the server auto creating these indexes?
> Thanks,
> Lijun
>|||These are created by the Index Tuning Wizard
FIX: Index Tuning Wizard Fails to Remove Hypothetical Clustered Indexes
http://support.microsoft.com/default.aspx?scid=kb;en-us;290414
BUG: Hypothetical Clustered Index From Index Tuning Wizard May Cause
Recompile Loop
http://support.microsoft.com/default.aspx?scid=kb;EN-US;293177
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:%23XnkwvOYDHA.736@.TK2MSFTNGP09.phx.gbl...
Using sp_helpindexes on a table, I found some indexes that is auto created
by SQLServer. The indexes' name all started with hind_. Does anyone know
what database options cause the server auto creating these indexes?
Thanks,
Lijun|||That's twice so far today I spaced out when reading a post. Jasper and
Wayne are or coarse correct. Statistics show up as _WA_...
--
Andrew J. Kelly
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ewXNZKPYDHA.2384@.tk2msftngp13.phx.gbl...
> They are not indexes, they are statistics and are normal. They get
created
> automatically by the engine if you have Auto Create Stats turn on (which
is
> the default) and are nothing to worry about. They are actually a good
> thing.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Lijun Zhang" <nospam@.nospam.nospam> wrote in message
> news:%23XnkwvOYDHA.736@.TK2MSFTNGP09.phx.gbl...
> > Using sp_helpindexes on a table, I found some indexes that is auto
created
> > by SQLServer. The indexes' name all started with hind_. Does anyone know
> > what database options cause the server auto creating these indexes?
> >
> > Thanks,
> > Lijun
> >
> >
>