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
>
>

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...
>

Auto-increment my primary key: why 2 instead of 1?

Hi all,

I have a table where I have my ProdPK set up as Primary key, turned on "Is Identity" and set the Identity increment to 1. But each time I add a new item, the number incremented by 2... I have couple of other tables and they are all fine, just this particular table increased twice as it should. I check the setting against other tables and everything seems to be the same.

By the way, this is adding the data to the table inside MS SQL Server Management Studio manually. I haven't done anything in the ASP.NET page yet.

Thank you very much,

Kenny.

That's weird. I would double check the identity column settings, and maybe check for triggers on the table?

|||

Thanks. I did checked the table's setting and the auto-increment was set to "1". Other settings are identical to my other tables. Any suggestions?

Thanks again,

Kenny.

|||

Can you post the table script and the results you observe by executing "dbcc checkident ( YourTableName )" ? Have you made sure that there are no triggers defined on the table ?

|||

Here is the table's script:

1USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]2GO3/****** Object: Table [dbo].[T_PRODUCTS] Script Date: 12/22/2007 11:58:52 ******/4SET ANSI_NULLS ON5GO6SET QUOTED_IDENTIFIER ON7GO8SET ANSI_PADDING ON9GO10CREATE TABLE [dbo].[T_PRODUCTS](11[PROD_ID] [int] IDENTITY(1,1) NOT NULL,12[ACC_TYPE_ID_FK] [int] NOT NULL,13[PROD_NAME] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,14[PROD_DESCR] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,15[PROD_MODEL] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,16[PROD_STATUS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,17[PROD_PRICE] [smallmoney] NULL,18[PROD_DATE_ADDED] [datetime] NOT NULL CONSTRAINT [DF_T_PRODUCTS_PROD_DATE_ADDED] DEFAULT (getdate()),19[PROD_ADDED_BY] [uniqueidentifier] NULL,20[PROD_NO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,21[PROD_NOTES] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,22[PROD_PIC_S] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,23[PROD_PIC_L] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,24[PROD_NEW] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,25 CONSTRAINT [PK_T_PRODUCTS] PRIMARY KEY CLUSTERED26(27[PROD_ID] ASC28)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]29) ON [PRIMARY]3031GO32SET ANSI_PADDING OFF33GO34USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]35GO36ALTER TABLE [dbo].[T_PRODUCTS] WITH NOCHECK ADD CONSTRAINT [ACT_TYPE_ID_FK] FOREIGN KEY([ACC_TYPE_ID_FK])37REFERENCES [dbo].[T_ACC_TYPES] ([ACC_TYPE_ID])38NOT FOR REPLICATION

This is what it return when I run the dbcc:

Checking identity information: current identity value '4', current column value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Currently there are only two rows in my database, with the primary key values 2 and 4. It skipped 1 and 3.

Thank you,

Kenny.

|||

I don't know what exactly is the problem, but my database primary key started at 2, then 4, then 8... After 8, everything then become normal, meaning they increased correctly by 1!!!

I still don't know why, but it solved the problem itself I guess!

Thanks all,

Kenny.

Autogenerate Primary Key

Hi all
How do you autogenerate your own primary key in SQL.
Instead of SQL generating an IDENTIY number which would be 1, 2 ,3..etc
I was wanting to give it my own sequence of numbers, how exactly do I do that can anyone help??why? what do these sequence of numbers look like? do they have special meanings?|||Humour me for a second - why?|||why? what do these sequence of numbers look like? do they have special meanings?Humour him too ^^^^^^^ :)|||The number would look like this 07-0000

The first two digits are the year, the others are in numerical secquence.. like 07-0001, 07-0002, 07-0003 (JP IR#)and so on, they normally log this in a journal but now they want a database to log this in where the database would generate these numbers and they have to do is put in the type, the amount and whether its a hold or not. its for our surveillance department and they have IR numbers that help in their reports|||smart numbers are stupid for many many reasons. I would just store those 2 parts in seperate fields and bring them together in the UI so they can see the number they want.|||Sorry I didnt realize they would be stupid, I just thought I would ask. Doesnt hurt to ask. I wasnt sure how to create something like|||Heh. The problem with semi intelligent (and also dumb) bespoke keys is when it comes to serialisation. This is not much of an issue if you add one row at a tme and do not have high concurrency but if either of these are not true then performance really suffers. Do you need to reset the count each year? SQL Server does not support this internally. I think other RDBMSs (like MySQL) do but that is not much help :)

auto update/create statistics

For an OLTP application with 100+ users, is it better to disable the
following two options:
Auto create statistics
Auto update statistics
...and instead run
exec sp_updatestats daily/weekly?
TIA
Depends on your setup. If you have a reasonably sized maintenance window, I
would go for a daily sp_updatestats.
The problem with Auto update statistics IMO is that it is most likely to
kick in when your system is at its busiest.
Jacco Schalkwijk
SQL Server MVP
"Nimi" <Nimi@.discussions.microsoft.com> wrote in message
news:F6727E2C-8EFA-4291-B00A-E84E4143E57C@.microsoft.com...
> For an OLTP application with 100+ users, is it better to disable the
> following two options:
> Auto create statistics
> Auto update statistics
> ...and instead run
> exec sp_updatestats daily/weekly?
> TIA
|||you might want to disable AutoUpdate statistics IF you are seeing that it is
causing problems.
You likely do not want to disable AutoCreate Statistics.
Greg Jackson
PDX, Oregon

auto update/create statistics

For an OLTP application with 100+ users, is it better to disable the
following two options:
Auto create statistics
Auto update statistics
...and instead run
exec sp_updatestats daily/weekly?
TIADepends on your setup. If you have a reasonably sized maintenance window, I
would go for a daily sp_updatestats.
The problem with Auto update statistics IMO is that it is most likely to
kick in when your system is at its busiest.
Jacco Schalkwijk
SQL Server MVP
"Nimi" <Nimi@.discussions.microsoft.com> wrote in message
news:F6727E2C-8EFA-4291-B00A-E84E4143E57C@.microsoft.com...
> For an OLTP application with 100+ users, is it better to disable the
> following two options:
> Auto create statistics
> Auto update statistics
> ...and instead run
> exec sp_updatestats daily/weekly?
> TIA|||you might want to disable AutoUpdate statistics IF you are seeing that it is
causing problems.
You likely do not want to disable AutoCreate Statistics.
Greg Jackson
PDX, Oregon