I have started to work on a report with table data region, and there
was need to sort the records withing a group, so I created a new
group, assigned the appropriate sort order, and then cleared "Include
group header" and "Include group footer" checkboxes. Because of this
the group did not show up in the layout window. But now I want to make
some changes to the sort, and I could not find a way to get to the
group definition again. Please help. Thanks.Found the solution. From Table Properties, go to Groups tab, and then
you can manage the groups there.
Showing posts with label created. Show all posts
Showing posts with label created. Show all posts
Wednesday, March 28, 2012
How to show duplicate rows in Report Builder ?
Hi!
I've created a report in Report Builder, based on a query. When I run the report in Report Builder, it doesn't show the duplicates rows, even if it works fine in Data source view.
I've created a report in Report Builder, based on a query. When I run the report in Report Builder, it doesn't show the duplicates rows, even if it works fine in Data source view.
How can I see al rows in Report Builder(even if they're duplicates) ?
Thank you in advance.
Best regards,
VV
Report Builder shows groups of data, if the data you gropued does not contain a unique column, the data will be grouped, if you want to display all rows, you will need to display column which is different in the mutli-instanced rows.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
Monday, March 26, 2012
How to show 2 reports in 1 viewer right after each other
I am using vb.net 2005 and have created 2 reports with Crystal. I have the reports showing fine by themselves, but what I want to happen is as follows:
User generates a detail report > user views/prints report > user closes report > summary report opens > user views/prints summary.
I want both reports to use the same CrystalReportViewer/Form, so once the first report is closed, I want the 2nd one to open. Is this possible?
I am not sure how to accomplish this. Any help?Are you pulling directly from a database or are you populating the data programatically?
This is how we do something similar in vb6 so if you use that you can put one in the close event of the form you are loading... As long as you don't allow them to close throug the view and you close on a button on the screen.
Hope this helps..
Rico
'Defines the CRxdrt object as the application
Set mobjApplication = CreateObject("CrystalRuntime.Application.11")
'Opens the defined report
Set mobjReport = mobjApplication.OpenReport(mcReportName, iOPEN_MULTIUSE)
'Sets the datasource of the report
mobjReport.Database.Tables(1).SetDataSource mrsReportData, 3
'Sets the crview object to the report we just opened
crptViewer.ReportSource = mobjReport
'Makes it viewable on the screen
crptViewer.ViewReport|||Is this the right close event?
Private Sub frmDetailSumm_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
'my code to load 2nd report
End Sub
User generates a detail report > user views/prints report > user closes report > summary report opens > user views/prints summary.
I want both reports to use the same CrystalReportViewer/Form, so once the first report is closed, I want the 2nd one to open. Is this possible?
I am not sure how to accomplish this. Any help?Are you pulling directly from a database or are you populating the data programatically?
This is how we do something similar in vb6 so if you use that you can put one in the close event of the form you are loading... As long as you don't allow them to close throug the view and you close on a button on the screen.
Hope this helps..
Rico
'Defines the CRxdrt object as the application
Set mobjApplication = CreateObject("CrystalRuntime.Application.11")
'Opens the defined report
Set mobjReport = mobjApplication.OpenReport(mcReportName, iOPEN_MULTIUSE)
'Sets the datasource of the report
mobjReport.Database.Tables(1).SetDataSource mrsReportData, 3
'Sets the crview object to the report we just opened
crptViewer.ReportSource = mobjReport
'Makes it viewable on the screen
crptViewer.ViewReport|||Is this the right close event?
Private Sub frmDetailSumm_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
'my code to load 2nd report
End Sub
How to share read only data
Hi,
I'm new to replication. Can anyone help with the following?
I want to publish the specific rows of data that are created on the publisher without accepting changes to those rows from subscribers, but do accept new rows (and changes to those rows) from subscribers.
EG. publisher creates rows 1 and 2 which are published to the subscriber
subscriber creates rows 3 and 4 which are merged back to the publisher
subscriber's updates (or delets) to rows 1 and 2 are not merged back to the publisher
So basically I want the publisher created rows to be published but remain as is whilst allowing subscriber created rows to be merged back to the publisher.
Hope is makes sense.
Thanks in advance,
Seedsy
Look at the properties of the articles (tables). There is an option called Synchronization Direction. That property, along with others, are what you are looking for.
By default, the Synchronization Direction property is set to Bidirectional, meaning, data will be synchronized both directions. I am pretty sure you can set this property to only allow synchronization one direction.
Hope this helps...
|||I couldn't find any properties within the Publication's Articles' "Table Article Properties" but I found the SQLMergeObject's ExchangeType Property which allows upload / download / bidirectional. Shame its not a GUI option within the REPL tools.
Thanks I should be able to work it out from here.
|||Basically you want to publish ins/upd/del changes from the publisher to the subscriber, and only inserts at the subscriber to the publisher, correct? Seems like you want to implement bi-directional transactional replication (or possibly peer to peer replication), you can decide not to replicate upd and del commands at one of the nodes in the article properties.|||Thanks Greg.
I can see that Table Article properties allows checking of the user permissions when merging subscriber changes for Insert, Update and Delete.
So is the approach that ... one should be using the restrictions of user permissions to disallow update and delete by the subscriber?
Seedsy
PS I should mention I'm using SQL2000 (V8.00.2039) SP4 so maybe I don't have all the bells and whistles.
|||i'm not sure it will work for merge replication, but I'm sure bi-directional transactional replication is what you need if you're on SQL 2000.How to share dimensions for multiple cubes in AS 2005?
Hello,
I have a couple of cubes that I created using a wizard in AS 2005. Every time I create a new cube, a AS wizard adds dimensions with 1,2 etc at the end of their names. Is it possible to use/share existing dimensions which are used by other cubes instead of having bunch of dimensions which are the same and the only difference is their name?
Thanks!
When you are going through cube building wizard, once you get to the Review Shared Dimensions page, select all the dimensions that you want to reuse. The wizard will reuse these dimensions and it won't create the dimensions with the 1,2 etc.
Hope this helps.
Van Dieu
|||Altering what you have already generated may be complicated. I don't use the wizard to generate cubes, so I'm not sure how much work is involved to change it. It might be as simple as opening the cube editor, deleting the dimensions that are duplicates, and adding the ones that you want to use, but I doubt it. You'll probably have to alter the data source view and the dimension usage in the cube editor as well.|||Actually, it is that easy. You can just open up the cube editor and delete all the duplicated dimensions. Then on the dimension usage tab add them back in (the dimensions that you want to share).
Van Dieu
|||Good to know
How to share dataset after it was created within the report
Is there a way to share the dataset with the other reports once it was
created in once of the reports in the same project? Right now, we are
copying the same dataset from one to the others...
BrianI'm not sure if you want to pull out data from a created report, then
use that in the others.
Or, if you already have that data, and want to feed it into 5 reports?
I'm new to RS, and the only way I have used it sofar is setting up the
connections, parameters, then querying it through IIS. I myself am
wondering if I can take a Dataset (received through MSMQ), then tell a
Report 'use this data', then send it off to a printer (pdf etc).|||It looks like the solution to my problem will be to use the RS viewer
controls in VS.net 2005. Basically if it works the way I believe I can
get a dataset, then apply that to a number of reports, save them off to
PDF, then I am done.
created in once of the reports in the same project? Right now, we are
copying the same dataset from one to the others...
BrianI'm not sure if you want to pull out data from a created report, then
use that in the others.
Or, if you already have that data, and want to feed it into 5 reports?
I'm new to RS, and the only way I have used it sofar is setting up the
connections, parameters, then querying it through IIS. I myself am
wondering if I can take a Dataset (received through MSMQ), then tell a
Report 'use this data', then send it off to a printer (pdf etc).|||It looks like the solution to my problem will be to use the RS viewer
controls in VS.net 2005. Basically if it works the way I believe I can
get a dataset, then apply that to a number of reports, save them off to
PDF, then I am done.
Friday, March 23, 2012
How to setup Parent>>Child>>Child relationship...?
Hello,
SQL newby looking for some advice. I have created the three tables below. XXParent is the master table, XXParentChild is the child table to XXParent and it should have a one-to-many relation to its parent. XXParentChildChild is the child table to XXParentChild, and it will likewise have a one to many relation to XXParentChild. In effect one XXParent row can have many XXParentChild rows assigned to it and one XXParentChild row can have many XXParentChildChild rows assigned to it.
What I'm missing is how to create the table so that once I've entered a row in XXParent, I can insert multiple rows in XXParentChild and subsequently insert multiple rows in XXParentChildChild for each of its parent rows, while maintaining referential integrity.
First, not sure what record id style to use, whether IDENTITY, or UNIQUEID, etc..
Second, not sure how to set up the FK's and Relationships between the tables.
Any advice appreciated greatly!!
Thanks in advance!
CREATE TABLE [XXParent] (
[XXSuiteID] [int] IDENTITY (1, 1) NOT NULL ,
[XXDateRun] [datetime] NULL ,
[XXStartTime] [datetime] NULL ,
[XXEndTime] [datetime] NULL ,
[XXsSucceeded] [int] NULL ,
[XXsWarned] [int] NULL ,
[XXsFailed] [int] NULL ,
[XXMachine] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXClientMachine] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXLogin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXLabel] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_XXSuite] PRIMARY KEY CLUSTERED
(
[XXSuiteID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [XXParentChild] (
[XXSuiteID] [int] NOT NULL ,
[XXID] [int] IDENTITY (1, 1) NOT NULL ,
[XXIDInternal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXTier] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXStart] [datetime] NULL ,
[XXEnd] [datetime] NULL ,
[XXWFBTime] [datetime] NULL ,
[XXWFBCalled] [int] NULL ,
[XXSearches] [int] NULL ,
[XXSearchesTime] [datetime] NULL ,
[XXResult] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [XXParentChildChild] (
[XXID] [int] NOT NULL ,
[XXMssgType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXMessage] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GOAnswered my own question:
CREATE TABLE XXParent (
XXSuiteID int IDENTITY (1, 1) NOT NULL,
XXDateRun datetime NULL ,
XXStartTime datetime NULL ,
XXEndTime datetime NULL ,
XXsSucceeded int NULL ,
XXsWarned int NULL ,
XXsFailed int NULL ,
XXMachine varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXClientMachine varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXLogin varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXLabel varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT PK_XXSuite PRIMARY KEY CLUSTERED
(
XXSuiteID
) ON PRIMARY
) ON PRIMARY
GO
CREATE TABLE XXParentChild (
XXID int IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
XXSuiteID int NOT NULL ,
XXIDInternal varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXName varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXDescription varchar (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXTier text COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXNo varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXStart datetime NULL ,
XXEnd datetime NULL ,
XXWFBTime datetime NULL ,
XXWFBCalled int NULL ,
XXSearches int NULL ,
XXSearchesTime datetime NULL ,
XXResult varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
FOREIGN KEY (XXSuiteID) REFERENCES XXParent(XXSuiteID)
) ON PRIMARY TEXTIMAGE_ON PRIMARY
GO
CREATE TABLE XXParentChildChild (
XXCHILDID int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
XXID int NOT NULL ,
XXMssgType varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXMessage varchar (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
FOREIGN KEY (XXID) REFERENCES XXParentChild(XXID)
) ON PRIMARY
GOsql
Wednesday, March 21, 2012
How to set up row/column labels for matrix report?
It seems the row/column lables are not automatically created in the matrix
report as in table report.
Anyone know how to do this?
Thanks,The matrix automatically creates the labels based on the x-axis (columns)
fields that you supply it.
-Tim
"yongli" <yongli@.discussions.microsoft.com> wrote in message
news:3BF751AA-4752-41B0-B1EE-5A6247B9C6B4@.microsoft.com...
> It seems the row/column lables are not automatically created in the matrix
> report as in table report.
> Anyone know how to do this?
> Thanks,|||Thanks for the quick response. Probably I didn't provide the full details.
When I used the report model base on my OLAP cube, the labels were created
automatically. I was talking about creating a free-form report using matrix,
when I draged the dataset (based on MDX) field over to the column/row in the
matrix, it didn't create lable for me, so I wonder if I have to manually add
it in the case.
Thanks,
"Tim Dot NoSpam" wrote:
> The matrix automatically creates the labels based on the x-axis (columns)
> fields that you supply it.
> -Tim
> "yongli" <yongli@.discussions.microsoft.com> wrote in message
> news:3BF751AA-4752-41B0-B1EE-5A6247B9C6B4@.microsoft.com...
> > It seems the row/column lables are not automatically created in the matrix
> > report as in table report.
> >
> > Anyone know how to do this?
> >
> > Thanks,
>
>|||Gotcha. If you use the report wizard first to see how the wizard sets up
the matrix, this might help. I usually use the matrix wizard to create the
report, mainly because I'm lazy...
T-im
"yongli" <yongli@.discussions.microsoft.com> wrote in message
news:19CACC04-476B-47A1-9962-3A90A724B7A4@.microsoft.com...
> Thanks for the quick response. Probably I didn't provide the full
> details.
> When I used the report model base on my OLAP cube, the labels were created
> automatically. I was talking about creating a free-form report using
> matrix,
> when I draged the dataset (based on MDX) field over to the column/row in
> the
> matrix, it didn't create lable for me, so I wonder if I have to manually
> add
> it in the case.
> Thanks,
> "Tim Dot NoSpam" wrote:
>> The matrix automatically creates the labels based on the x-axis (columns)
>> fields that you supply it.
>> -Tim
>> "yongli" <yongli@.discussions.microsoft.com> wrote in message
>> news:3BF751AA-4752-41B0-B1EE-5A6247B9C6B4@.microsoft.com...
>> > It seems the row/column lables are not automatically created in the
>> > matrix
>> > report as in table report.
>> >
>> > Anyone know how to do this?
>> >
>> > Thanks,
>>
report as in table report.
Anyone know how to do this?
Thanks,The matrix automatically creates the labels based on the x-axis (columns)
fields that you supply it.
-Tim
"yongli" <yongli@.discussions.microsoft.com> wrote in message
news:3BF751AA-4752-41B0-B1EE-5A6247B9C6B4@.microsoft.com...
> It seems the row/column lables are not automatically created in the matrix
> report as in table report.
> Anyone know how to do this?
> Thanks,|||Thanks for the quick response. Probably I didn't provide the full details.
When I used the report model base on my OLAP cube, the labels were created
automatically. I was talking about creating a free-form report using matrix,
when I draged the dataset (based on MDX) field over to the column/row in the
matrix, it didn't create lable for me, so I wonder if I have to manually add
it in the case.
Thanks,
"Tim Dot NoSpam" wrote:
> The matrix automatically creates the labels based on the x-axis (columns)
> fields that you supply it.
> -Tim
> "yongli" <yongli@.discussions.microsoft.com> wrote in message
> news:3BF751AA-4752-41B0-B1EE-5A6247B9C6B4@.microsoft.com...
> > It seems the row/column lables are not automatically created in the matrix
> > report as in table report.
> >
> > Anyone know how to do this?
> >
> > Thanks,
>
>|||Gotcha. If you use the report wizard first to see how the wizard sets up
the matrix, this might help. I usually use the matrix wizard to create the
report, mainly because I'm lazy...
T-im
"yongli" <yongli@.discussions.microsoft.com> wrote in message
news:19CACC04-476B-47A1-9962-3A90A724B7A4@.microsoft.com...
> Thanks for the quick response. Probably I didn't provide the full
> details.
> When I used the report model base on my OLAP cube, the labels were created
> automatically. I was talking about creating a free-form report using
> matrix,
> when I draged the dataset (based on MDX) field over to the column/row in
> the
> matrix, it didn't create lable for me, so I wonder if I have to manually
> add
> it in the case.
> Thanks,
> "Tim Dot NoSpam" wrote:
>> The matrix automatically creates the labels based on the x-axis (columns)
>> fields that you supply it.
>> -Tim
>> "yongli" <yongli@.discussions.microsoft.com> wrote in message
>> news:3BF751AA-4752-41B0-B1EE-5A6247B9C6B4@.microsoft.com...
>> > It seems the row/column lables are not automatically created in the
>> > matrix
>> > report as in table report.
>> >
>> > Anyone know how to do this?
>> >
>> > Thanks,
>>
How to set up reporting services to have up-to-date cube data whenever report opened
Hi, at work I have set up reporting services with analysis services
providing cube data. I have created a cube and deployed it to reporting
services. What I want to know is how do i guarantee that when an executive
opens up the report, they will get the latest data from the SQL database? I
have installed/created everything with defaults, including the reports and
their deployment via visual studio to reporting services.
Here I am guessing that when a report is opened that reporting services
connects to analysis services (which interacts with sql server whatever way)
to get the latest update of data within the report? I am just a little
concerned because I had a previous report set up and no matter how i tried
to get it to update data, it just couldnt. I had to redeploy the solution
from VS to reporting services.
Any help most appreciated!
cheers
murrayThe default deployment will always get you the latest data... However if you
make a change to the report onthe reporting server, that change will NOT be
overridden by redeploying...
Perhaps you set the cache time on the report in Report Manager... That means
that data will be re-used without re-querying for 60 minutes... Redeploying
the report will NOT change this and many other settings... You would have to
either delete and redeploy or change the setting in Report Manager..
However, for the standard default deploy of a new report, no caching is used..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"j.murray" wrote:
> Hi, at work I have set up reporting services with analysis services
> providing cube data. I have created a cube and deployed it to reporting
> services. What I want to know is how do i guarantee that when an executive
> opens up the report, they will get the latest data from the SQL database? I
> have installed/created everything with defaults, including the reports and
> their deployment via visual studio to reporting services.
> Here I am guessing that when a report is opened that reporting services
> connects to analysis services (which interacts with sql server whatever way)
> to get the latest update of data within the report? I am just a little
> concerned because I had a previous report set up and no matter how i tried
> to get it to update data, it just couldnt. I had to redeploy the solution
> from VS to reporting services.
> Any help most appreciated!
> cheers
> murray
>
>sql
providing cube data. I have created a cube and deployed it to reporting
services. What I want to know is how do i guarantee that when an executive
opens up the report, they will get the latest data from the SQL database? I
have installed/created everything with defaults, including the reports and
their deployment via visual studio to reporting services.
Here I am guessing that when a report is opened that reporting services
connects to analysis services (which interacts with sql server whatever way)
to get the latest update of data within the report? I am just a little
concerned because I had a previous report set up and no matter how i tried
to get it to update data, it just couldnt. I had to redeploy the solution
from VS to reporting services.
Any help most appreciated!
cheers
murrayThe default deployment will always get you the latest data... However if you
make a change to the report onthe reporting server, that change will NOT be
overridden by redeploying...
Perhaps you set the cache time on the report in Report Manager... That means
that data will be re-used without re-querying for 60 minutes... Redeploying
the report will NOT change this and many other settings... You would have to
either delete and redeploy or change the setting in Report Manager..
However, for the standard default deploy of a new report, no caching is used..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"j.murray" wrote:
> Hi, at work I have set up reporting services with analysis services
> providing cube data. I have created a cube and deployed it to reporting
> services. What I want to know is how do i guarantee that when an executive
> opens up the report, they will get the latest data from the SQL database? I
> have installed/created everything with defaults, including the reports and
> their deployment via visual studio to reporting services.
> Here I am guessing that when a report is opened that reporting services
> connects to analysis services (which interacts with sql server whatever way)
> to get the latest update of data within the report? I am just a little
> concerned because I had a previous report set up and no matter how i tried
> to get it to update data, it just couldnt. I had to redeploy the solution
> from VS to reporting services.
> Any help most appreciated!
> cheers
> murray
>
>sql
Monday, March 19, 2012
How to set up a report when a specific user makes changes to a database
Hi,
We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows
2003 R2 SP2 server.
I successfully created a mailbox for SQL to use and associated the SQL Agent
to use this e-mail when sending out alerts.
I am however not able to find a way for an e-mail to be sent if a specific
user makes a change to a database.
Can somebody please point me in the right direction.
Thank you,
WarrenCan you explain exactly what you mean by "makes a change to the database"? A
LTER TABLE? UPDATE?
Something else? Triggers and possibly event notifications seems to be what y
ou want to look into.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2
003 R2 SP2 server.
> I successfully created a mailbox for SQL to use and associated the SQL Age
nt to use this e-mail
> when sending out alerts.
> I am however not able to find a way for an e-mail to be sent if a specific
user makes a change to
> a database.
> Can somebody please point me in the right direction.
> Thank you,
> Warren
>|||Basically anything that would be considered a change to the database for
auditing purposes.
Can you lead me in the right direction on setting up triggers or event
notifications? I am new to SQL 2005.
Thank you,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Can you explain exactly what you mean by "makes a change to the database"?
> ALTER TABLE? UPDATE? Something else? Triggers and possibly event
> notifications seems to be what you want to look into.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>|||> Basically anything that would be considered a change to the database for a
uditing purposes.
Wow. Are you willing to pay the performance penalty for this?
Your best bet is probably to use some of the products out there. They use a
combination of
server-side Profiler traces along with reading the transaction log (somethin
g which we cannot do
ourselves) so performance impact would be less than if doing it ourselves. N
ot to mention the dev
time of doing it yourself.
Here's one: http://www.lumigent.com/products/auditdb.html
Some of the other log reader tools vendors might also have such products, I'
ve listed the ones I
know of on my links page: http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Basically anything that would be considered a change to the database for a
uditing purposes.
> Can you lead me in the right direction on setting up triggers or event not
ifications? I am new to
> SQL 2005.
> Thank you,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>|||Maybe I should refrase that a little bit.
Basically I need to have some type of checks and balances setup where an
alert is sent when the dba does something to a database or makes changes.
In the configuration we are using the dba may connect to the SQL server once
a quarter if that.
Warren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
> Wow. Are you willing to pay the performance penalty for this?
> Your best bet is probably to use some of the products out there. They use
> a combination of server-side Profiler traces along with reading the
> transaction log (something which we cannot do ourselves) so performance
> impact would be less than if doing it ourselves. Not to mention the dev
> time of doing it yourself.
> Here's one: http://www.lumigent.com/products/auditdb.html
> Some of the other log reader tools vendors might also have such products,
> I've listed the ones I know of on my links page:
> http://www.karaszi.com/SQLServer/links.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>|||Did you look at the product?
There's no pre-packaged solution for this. You need to determine exactly wha
t type of changes you
want to be alerted for. Based on that decide whether you find a product that
suit your need or if
you want to do it yourself. Depending on what type of changes you want to be
alerted for, event
notification can be an option. See Books Online, CREATE EVENT NOTIFICATION.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
> Maybe I should refrase that a little bit.
> Basically I need to have some type of checks and balances setup where an a
lert is sent when the
> dba does something to a database or makes changes.
> In the configuration we are using the dba may connect to the SQL server on
ce a quarter if that.
> Warren
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>|||Okay,
Maybe I really just need the basic and I can't figure out how to make it
work. Books on line wasn't very helpful.
Is there a white paper on setting up user auditing for just logging on?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> Did you look at the product?
> There's no pre-packaged solution for this. You need to determine exactly
> what type of changes you want to be alerted for. Based on that decide
> whether you find a product that suit your need or if you want to do it
> yourself. Depending on what type of changes you want to be alerted for,
> event notification can be an option. See Books Online, CREATE EVENT
> NOTIFICATION.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
>|||> Is there a white paper on setting up user auditing for just logging on?
Only login events. That makes it a very special case, and you can do that w
ith a few clicks. SSMS,
Right-click the server in Object Explorer, Properties, the security window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:%23OOnP485GHA.512@.TK2MSFTNGP06.phx.gbl...
> Okay,
> Maybe I really just need the basic and I can't figure out how to make it w
ork. Books on line
> wasn't very helpful.
> Is there a white paper on setting up user auditing for just logging on?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
>
We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows
2003 R2 SP2 server.
I successfully created a mailbox for SQL to use and associated the SQL Agent
to use this e-mail when sending out alerts.
I am however not able to find a way for an e-mail to be sent if a specific
user makes a change to a database.
Can somebody please point me in the right direction.
Thank you,
WarrenCan you explain exactly what you mean by "makes a change to the database"? A
LTER TABLE? UPDATE?
Something else? Triggers and possibly event notifications seems to be what y
ou want to look into.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2
003 R2 SP2 server.
> I successfully created a mailbox for SQL to use and associated the SQL Age
nt to use this e-mail
> when sending out alerts.
> I am however not able to find a way for an e-mail to be sent if a specific
user makes a change to
> a database.
> Can somebody please point me in the right direction.
> Thank you,
> Warren
>|||Basically anything that would be considered a change to the database for
auditing purposes.
Can you lead me in the right direction on setting up triggers or event
notifications? I am new to SQL 2005.
Thank you,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Can you explain exactly what you mean by "makes a change to the database"?
> ALTER TABLE? UPDATE? Something else? Triggers and possibly event
> notifications seems to be what you want to look into.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>|||> Basically anything that would be considered a change to the database for a
uditing purposes.
Wow. Are you willing to pay the performance penalty for this?
Your best bet is probably to use some of the products out there. They use a
combination of
server-side Profiler traces along with reading the transaction log (somethin
g which we cannot do
ourselves) so performance impact would be less than if doing it ourselves. N
ot to mention the dev
time of doing it yourself.
Here's one: http://www.lumigent.com/products/auditdb.html
Some of the other log reader tools vendors might also have such products, I'
ve listed the ones I
know of on my links page: http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Basically anything that would be considered a change to the database for a
uditing purposes.
> Can you lead me in the right direction on setting up triggers or event not
ifications? I am new to
> SQL 2005.
> Thank you,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>|||Maybe I should refrase that a little bit.
Basically I need to have some type of checks and balances setup where an
alert is sent when the dba does something to a database or makes changes.
In the configuration we are using the dba may connect to the SQL server once
a quarter if that.
Warren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
> Wow. Are you willing to pay the performance penalty for this?
> Your best bet is probably to use some of the products out there. They use
> a combination of server-side Profiler traces along with reading the
> transaction log (something which we cannot do ourselves) so performance
> impact would be less than if doing it ourselves. Not to mention the dev
> time of doing it yourself.
> Here's one: http://www.lumigent.com/products/auditdb.html
> Some of the other log reader tools vendors might also have such products,
> I've listed the ones I know of on my links page:
> http://www.karaszi.com/SQLServer/links.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>|||Did you look at the product?
There's no pre-packaged solution for this. You need to determine exactly wha
t type of changes you
want to be alerted for. Based on that decide whether you find a product that
suit your need or if
you want to do it yourself. Depending on what type of changes you want to be
alerted for, event
notification can be an option. See Books Online, CREATE EVENT NOTIFICATION.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
> Maybe I should refrase that a little bit.
> Basically I need to have some type of checks and balances setup where an a
lert is sent when the
> dba does something to a database or makes changes.
> In the configuration we are using the dba may connect to the SQL server on
ce a quarter if that.
> Warren
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>|||Okay,
Maybe I really just need the basic and I can't figure out how to make it
work. Books on line wasn't very helpful.
Is there a white paper on setting up user auditing for just logging on?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> Did you look at the product?
> There's no pre-packaged solution for this. You need to determine exactly
> what type of changes you want to be alerted for. Based on that decide
> whether you find a product that suit your need or if you want to do it
> yourself. Depending on what type of changes you want to be alerted for,
> event notification can be an option. See Books Online, CREATE EVENT
> NOTIFICATION.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
>|||> Is there a white paper on setting up user auditing for just logging on?
Only login events. That makes it a very special case, and you can do that w
ith a few clicks. SSMS,
Right-click the server in Object Explorer, Properties, the security window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:%23OOnP485GHA.512@.TK2MSFTNGP06.phx.gbl...
> Okay,
> Maybe I really just need the basic and I can't figure out how to make it w
ork. Books on line
> wasn't very helpful.
> Is there a white paper on setting up user auditing for just logging on?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
>
How to set up a report when a specific user makes changes to a database
Hi,
We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows
2003 R2 SP2 server.
I successfully created a mailbox for SQL to use and associated the SQL Agent
to use this e-mail when sending out alerts.
I am however not able to find a way for an e-mail to be sent if a specific
user makes a change to a database.
Can somebody please point me in the right direction.
Thank you,
WarrenCan you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
Something else? Triggers and possibly event notifications seems to be what you want to look into.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this e-mail
> when sending out alerts.
> I am however not able to find a way for an e-mail to be sent if a specific user makes a change to
> a database.
> Can somebody please point me in the right direction.
> Thank you,
> Warren
>|||Basically anything that would be considered a change to the database for
auditing purposes.
Can you lead me in the right direction on setting up triggers or event
notifications? I am new to SQL 2005.
Thank you,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Can you explain exactly what you mean by "makes a change to the database"?
> ALTER TABLE? UPDATE? Something else? Triggers and possibly event
> notifications seems to be what you want to look into.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows
>> 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL
>> Agent to use this e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a
>> specific user makes a change to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>|||> Basically anything that would be considered a change to the database for auditing purposes.
Wow. Are you willing to pay the performance penalty for this?
Your best bet is probably to use some of the products out there. They use a combination of
server-side Profiler traces along with reading the transaction log (something which we cannot do
ourselves) so performance impact would be less than if doing it ourselves. Not to mention the dev
time of doing it yourself.
Here's one: http://www.lumigent.com/products/auditdb.html
Some of the other log reader tools vendors might also have such products, I've listed the ones I
know of on my links page: http://www.karaszi.com/SQLServer/links.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Basically anything that would be considered a change to the database for auditing purposes.
> Can you lead me in the right direction on setting up triggers or event notifications? I am new to
> SQL 2005.
> Thank you,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
>> Something else? Triggers and possibly event notifications seems to be what you want to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this e-mail
>> when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a specific user makes a change
>> to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>|||Maybe I should refrase that a little bit.
Basically I need to have some type of checks and balances setup where an
alert is sent when the dba does something to a database or makes changes.
In the configuration we are using the dba may connect to the SQL server once
a quarter if that.
Warren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database for
>> auditing purposes.
> Wow. Are you willing to pay the performance penalty for this?
> Your best bet is probably to use some of the products out there. They use
> a combination of server-side Profiler traces along with reading the
> transaction log (something which we cannot do ourselves) so performance
> impact would be less than if doing it ourselves. Not to mention the dev
> time of doing it yourself.
> Here's one: http://www.lumigent.com/products/auditdb.html
> Some of the other log reader tools vendors might also have such products,
> I've listed the ones I know of on my links page:
> http://www.karaszi.com/SQLServer/links.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database for
>> auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event
>> notifications? I am new to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the
>> database"? ALTER TABLE? UPDATE? Something else? Triggers and possibly
>> event notifications seems to be what you want to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a
>> Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL
>> Agent to use this e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a
>> specific user makes a change to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>>
>|||Did you look at the product?
There's no pre-packaged solution for this. You need to determine exactly what type of changes you
want to be alerted for. Based on that decide whether you find a product that suit your need or if
you want to do it yourself. Depending on what type of changes you want to be alerted for, event
notification can be an option. See Books Online, CREATE EVENT NOTIFICATION.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
> Maybe I should refrase that a little bit.
> Basically I need to have some type of checks and balances setup where an alert is sent when the
> dba does something to a database or makes changes.
> In the configuration we are using the dba may connect to the SQL server once a quarter if that.
> Warren
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Wow. Are you willing to pay the performance penalty for this?
>> Your best bet is probably to use some of the products out there. They use a combination of
>> server-side Profiler traces along with reading the transaction log (something which we cannot do
>> ourselves) so performance impact would be less than if doing it ourselves. Not to mention the dev
>> time of doing it yourself.
>> Here's one: http://www.lumigent.com/products/auditdb.html
>> Some of the other log reader tools vendors might also have such products, I've listed the ones I
>> know of on my links page: http://www.karaszi.com/SQLServer/links.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event notifications? I am new
>> to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
>> Something else? Triggers and possibly event notifications seems to be what you want to look
>> into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this
>> e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a specific user makes a change
>> to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>>
>>
>|||Okay,
Maybe I really just need the basic and I can't figure out how to make it
work. Books on line wasn't very helpful.
Is there a white paper on setting up user auditing for just logging on?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> Did you look at the product?
> There's no pre-packaged solution for this. You need to determine exactly
> what type of changes you want to be alerted for. Based on that decide
> whether you find a product that suit your need or if you want to do it
> yourself. Depending on what type of changes you want to be alerted for,
> event notification can be an option. See Books Online, CREATE EVENT
> NOTIFICATION.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
>> Maybe I should refrase that a little bit.
>> Basically I need to have some type of checks and balances setup where an
>> alert is sent when the dba does something to a database or makes changes.
>> In the configuration we are using the dba may connect to the SQL server
>> once a quarter if that.
>> Warren
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database
>> for auditing purposes.
>> Wow. Are you willing to pay the performance penalty for this?
>> Your best bet is probably to use some of the products out there. They
>> use a combination of server-side Profiler traces along with reading the
>> transaction log (something which we cannot do ourselves) so performance
>> impact would be less than if doing it ourselves. Not to mention the dev
>> time of doing it yourself.
>> Here's one: http://www.lumigent.com/products/auditdb.html
>> Some of the other log reader tools vendors might also have such
>> products, I've listed the ones I know of on my links page:
>> http://www.karaszi.com/SQLServer/links.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database
>> for auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event
>> notifications? I am new to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the
>> database"? ALTER TABLE? UPDATE? Something else? Triggers and possibly
>> event notifications seems to be what you want to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a
>> Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the
>> SQL Agent to use this e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a
>> specific user makes a change to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>>
>>
>>
>|||> Is there a white paper on setting up user auditing for just logging on?
Only login events. That makes it a very special case, and you can do that with a few clicks. SSMS,
Right-click the server in Object Explorer, Properties, the security window.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:%23OOnP485GHA.512@.TK2MSFTNGP06.phx.gbl...
> Okay,
> Maybe I really just need the basic and I can't figure out how to make it work. Books on line
> wasn't very helpful.
> Is there a white paper on setting up user auditing for just logging on?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
>> Did you look at the product?
>> There's no pre-packaged solution for this. You need to determine exactly what type of changes you
>> want to be alerted for. Based on that decide whether you find a product that suit your need or if
>> you want to do it yourself. Depending on what type of changes you want to be alerted for, event
>> notification can be an option. See Books Online, CREATE EVENT NOTIFICATION.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
>> Maybe I should refrase that a little bit.
>> Basically I need to have some type of checks and balances setup where an alert is sent when the
>> dba does something to a database or makes changes.
>> In the configuration we are using the dba may connect to the SQL server once a quarter if that.
>> Warren
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Wow. Are you willing to pay the performance penalty for this?
>> Your best bet is probably to use some of the products out there. They use a combination of
>> server-side Profiler traces along with reading the transaction log (something which we cannot
>> do ourselves) so performance impact would be less than if doing it ourselves. Not to mention
>> the dev time of doing it yourself.
>> Here's one: http://www.lumigent.com/products/auditdb.html
>> Some of the other log reader tools vendors might also have such products, I've listed the ones
>> I know of on my links page: http://www.karaszi.com/SQLServer/links.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event notifications? I am
>> new to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE?
>> UPDATE? Something else? Triggers and possibly event notifications seems to be what you want
>> to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
>>> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this
>>> e-mail when sending out alerts.
>>> I am however not able to find a way for an e-mail to be sent if a specific user makes a
>>> change to a database.
>>> Can somebody please point me in the right direction.
>>>
>>> Thank you,
>>> Warren
>>>
>>>
>>
>>
>>
>
We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows
2003 R2 SP2 server.
I successfully created a mailbox for SQL to use and associated the SQL Agent
to use this e-mail when sending out alerts.
I am however not able to find a way for an e-mail to be sent if a specific
user makes a change to a database.
Can somebody please point me in the right direction.
Thank you,
WarrenCan you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
Something else? Triggers and possibly event notifications seems to be what you want to look into.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this e-mail
> when sending out alerts.
> I am however not able to find a way for an e-mail to be sent if a specific user makes a change to
> a database.
> Can somebody please point me in the right direction.
> Thank you,
> Warren
>|||Basically anything that would be considered a change to the database for
auditing purposes.
Can you lead me in the right direction on setting up triggers or event
notifications? I am new to SQL 2005.
Thank you,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Can you explain exactly what you mean by "makes a change to the database"?
> ALTER TABLE? UPDATE? Something else? Triggers and possibly event
> notifications seems to be what you want to look into.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows
>> 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL
>> Agent to use this e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a
>> specific user makes a change to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>|||> Basically anything that would be considered a change to the database for auditing purposes.
Wow. Are you willing to pay the performance penalty for this?
Your best bet is probably to use some of the products out there. They use a combination of
server-side Profiler traces along with reading the transaction log (something which we cannot do
ourselves) so performance impact would be less than if doing it ourselves. Not to mention the dev
time of doing it yourself.
Here's one: http://www.lumigent.com/products/auditdb.html
Some of the other log reader tools vendors might also have such products, I've listed the ones I
know of on my links page: http://www.karaszi.com/SQLServer/links.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Basically anything that would be considered a change to the database for auditing purposes.
> Can you lead me in the right direction on setting up triggers or event notifications? I am new to
> SQL 2005.
> Thank you,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
>> Something else? Triggers and possibly event notifications seems to be what you want to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this e-mail
>> when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a specific user makes a change
>> to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>|||Maybe I should refrase that a little bit.
Basically I need to have some type of checks and balances setup where an
alert is sent when the dba does something to a database or makes changes.
In the configuration we are using the dba may connect to the SQL server once
a quarter if that.
Warren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database for
>> auditing purposes.
> Wow. Are you willing to pay the performance penalty for this?
> Your best bet is probably to use some of the products out there. They use
> a combination of server-side Profiler traces along with reading the
> transaction log (something which we cannot do ourselves) so performance
> impact would be less than if doing it ourselves. Not to mention the dev
> time of doing it yourself.
> Here's one: http://www.lumigent.com/products/auditdb.html
> Some of the other log reader tools vendors might also have such products,
> I've listed the ones I know of on my links page:
> http://www.karaszi.com/SQLServer/links.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database for
>> auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event
>> notifications? I am new to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the
>> database"? ALTER TABLE? UPDATE? Something else? Triggers and possibly
>> event notifications seems to be what you want to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a
>> Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL
>> Agent to use this e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a
>> specific user makes a change to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>>
>|||Did you look at the product?
There's no pre-packaged solution for this. You need to determine exactly what type of changes you
want to be alerted for. Based on that decide whether you find a product that suit your need or if
you want to do it yourself. Depending on what type of changes you want to be alerted for, event
notification can be an option. See Books Online, CREATE EVENT NOTIFICATION.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
> Maybe I should refrase that a little bit.
> Basically I need to have some type of checks and balances setup where an alert is sent when the
> dba does something to a database or makes changes.
> In the configuration we are using the dba may connect to the SQL server once a quarter if that.
> Warren
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Wow. Are you willing to pay the performance penalty for this?
>> Your best bet is probably to use some of the products out there. They use a combination of
>> server-side Profiler traces along with reading the transaction log (something which we cannot do
>> ourselves) so performance impact would be less than if doing it ourselves. Not to mention the dev
>> time of doing it yourself.
>> Here's one: http://www.lumigent.com/products/auditdb.html
>> Some of the other log reader tools vendors might also have such products, I've listed the ones I
>> know of on my links page: http://www.karaszi.com/SQLServer/links.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event notifications? I am new
>> to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
>> Something else? Triggers and possibly event notifications seems to be what you want to look
>> into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this
>> e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a specific user makes a change
>> to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>>
>>
>|||Okay,
Maybe I really just need the basic and I can't figure out how to make it
work. Books on line wasn't very helpful.
Is there a white paper on setting up user auditing for just logging on?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> Did you look at the product?
> There's no pre-packaged solution for this. You need to determine exactly
> what type of changes you want to be alerted for. Based on that decide
> whether you find a product that suit your need or if you want to do it
> yourself. Depending on what type of changes you want to be alerted for,
> event notification can be an option. See Books Online, CREATE EVENT
> NOTIFICATION.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
>> Maybe I should refrase that a little bit.
>> Basically I need to have some type of checks and balances setup where an
>> alert is sent when the dba does something to a database or makes changes.
>> In the configuration we are using the dba may connect to the SQL server
>> once a quarter if that.
>> Warren
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database
>> for auditing purposes.
>> Wow. Are you willing to pay the performance penalty for this?
>> Your best bet is probably to use some of the products out there. They
>> use a combination of server-side Profiler traces along with reading the
>> transaction log (something which we cannot do ourselves) so performance
>> impact would be less than if doing it ourselves. Not to mention the dev
>> time of doing it yourself.
>> Here's one: http://www.lumigent.com/products/auditdb.html
>> Some of the other log reader tools vendors might also have such
>> products, I've listed the ones I know of on my links page:
>> http://www.karaszi.com/SQLServer/links.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database
>> for auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event
>> notifications? I am new to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the
>> database"? ALTER TABLE? UPDATE? Something else? Triggers and possibly
>> event notifications seems to be what you want to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a
>> Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the
>> SQL Agent to use this e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a
>> specific user makes a change to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>>
>>
>>
>|||> Is there a white paper on setting up user auditing for just logging on?
Only login events. That makes it a very special case, and you can do that with a few clicks. SSMS,
Right-click the server in Object Explorer, Properties, the security window.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:%23OOnP485GHA.512@.TK2MSFTNGP06.phx.gbl...
> Okay,
> Maybe I really just need the basic and I can't figure out how to make it work. Books on line
> wasn't very helpful.
> Is there a white paper on setting up user auditing for just logging on?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
>> Did you look at the product?
>> There's no pre-packaged solution for this. You need to determine exactly what type of changes you
>> want to be alerted for. Based on that decide whether you find a product that suit your need or if
>> you want to do it yourself. Depending on what type of changes you want to be alerted for, event
>> notification can be an option. See Books Online, CREATE EVENT NOTIFICATION.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
>> Maybe I should refrase that a little bit.
>> Basically I need to have some type of checks and balances setup where an alert is sent when the
>> dba does something to a database or makes changes.
>> In the configuration we are using the dba may connect to the SQL server once a quarter if that.
>> Warren
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Wow. Are you willing to pay the performance penalty for this?
>> Your best bet is probably to use some of the products out there. They use a combination of
>> server-side Profiler traces along with reading the transaction log (something which we cannot
>> do ourselves) so performance impact would be less than if doing it ourselves. Not to mention
>> the dev time of doing it yourself.
>> Here's one: http://www.lumigent.com/products/auditdb.html
>> Some of the other log reader tools vendors might also have such products, I've listed the ones
>> I know of on my links page: http://www.karaszi.com/SQLServer/links.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event notifications? I am
>> new to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE?
>> UPDATE? Something else? Triggers and possibly event notifications seems to be what you want
>> to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
>>> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this
>>> e-mail when sending out alerts.
>>> I am however not able to find a way for an e-mail to be sent if a specific user makes a
>>> change to a database.
>>> Can somebody please point me in the right direction.
>>>
>>> Thank you,
>>> Warren
>>>
>>>
>>
>>
>>
>
How to set up a report when a specific user makes changes to a database
Hi,
We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows
2003 R2 SP2 server.
I successfully created a mailbox for SQL to use and associated the SQL Agent
to use this e-mail when sending out alerts.
I am however not able to find a way for an e-mail to be sent if a specific
user makes a change to a database.
Can somebody please point me in the right direction.
Thank you,
Warren
Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
Something else? Triggers and possibly event notifications seems to be what you want to look into.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this e-mail
> when sending out alerts.
> I am however not able to find a way for an e-mail to be sent if a specific user makes a change to
> a database.
> Can somebody please point me in the right direction.
> Thank you,
> Warren
>
|||Basically anything that would be considered a change to the database for
auditing purposes.
Can you lead me in the right direction on setting up triggers or event
notifications? I am new to SQL 2005.
Thank you,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Can you explain exactly what you mean by "makes a change to the database"?
> ALTER TABLE? UPDATE? Something else? Triggers and possibly event
> notifications seems to be what you want to look into.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>
|||> Basically anything that would be considered a change to the database for auditing purposes.
Wow. Are you willing to pay the performance penalty for this?
Your best bet is probably to use some of the products out there. They use a combination of
server-side Profiler traces along with reading the transaction log (something which we cannot do
ourselves) so performance impact would be less than if doing it ourselves. Not to mention the dev
time of doing it yourself.
Here's one: http://www.lumigent.com/products/auditdb.html
Some of the other log reader tools vendors might also have such products, I've listed the ones I
know of on my links page: http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Basically anything that would be considered a change to the database for auditing purposes.
> Can you lead me in the right direction on setting up triggers or event notifications? I am new to
> SQL 2005.
> Thank you,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>
|||Maybe I should refrase that a little bit.
Basically I need to have some type of checks and balances setup where an
alert is sent when the dba does something to a database or makes changes.
In the configuration we are using the dba may connect to the SQL server once
a quarter if that.
Warren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
> Wow. Are you willing to pay the performance penalty for this?
> Your best bet is probably to use some of the products out there. They use
> a combination of server-side Profiler traces along with reading the
> transaction log (something which we cannot do ourselves) so performance
> impact would be less than if doing it ourselves. Not to mention the dev
> time of doing it yourself.
> Here's one: http://www.lumigent.com/products/auditdb.html
> Some of the other log reader tools vendors might also have such products,
> I've listed the ones I know of on my links page:
> http://www.karaszi.com/SQLServer/links.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>
|||Did you look at the product?
There's no pre-packaged solution for this. You need to determine exactly what type of changes you
want to be alerted for. Based on that decide whether you find a product that suit your need or if
you want to do it yourself. Depending on what type of changes you want to be alerted for, event
notification can be an option. See Books Online, CREATE EVENT NOTIFICATION.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
> Maybe I should refrase that a little bit.
> Basically I need to have some type of checks and balances setup where an alert is sent when the
> dba does something to a database or makes changes.
> In the configuration we are using the dba may connect to the SQL server once a quarter if that.
> Warren
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>
|||Okay,
Maybe I really just need the basic and I can't figure out how to make it
work. Books on line wasn't very helpful.
Is there a white paper on setting up user auditing for just logging on?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> Did you look at the product?
> There's no pre-packaged solution for this. You need to determine exactly
> what type of changes you want to be alerted for. Based on that decide
> whether you find a product that suit your need or if you want to do it
> yourself. Depending on what type of changes you want to be alerted for,
> event notification can be an option. See Books Online, CREATE EVENT
> NOTIFICATION.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
>
|||> Is there a white paper on setting up user auditing for just logging on?
Only login events. That makes it a very special case, and you can do that with a few clicks. SSMS,
Right-click the server in Object Explorer, Properties, the security window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:%23OOnP485GHA.512@.TK2MSFTNGP06.phx.gbl...
> Okay,
> Maybe I really just need the basic and I can't figure out how to make it work. Books on line
> wasn't very helpful.
> Is there a white paper on setting up user auditing for just logging on?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
>
We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows
2003 R2 SP2 server.
I successfully created a mailbox for SQL to use and associated the SQL Agent
to use this e-mail when sending out alerts.
I am however not able to find a way for an e-mail to be sent if a specific
user makes a change to a database.
Can somebody please point me in the right direction.
Thank you,
Warren
Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
Something else? Triggers and possibly event notifications seems to be what you want to look into.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this e-mail
> when sending out alerts.
> I am however not able to find a way for an e-mail to be sent if a specific user makes a change to
> a database.
> Can somebody please point me in the right direction.
> Thank you,
> Warren
>
|||Basically anything that would be considered a change to the database for
auditing purposes.
Can you lead me in the right direction on setting up triggers or event
notifications? I am new to SQL 2005.
Thank you,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Can you explain exactly what you mean by "makes a change to the database"?
> ALTER TABLE? UPDATE? Something else? Triggers and possibly event
> notifications seems to be what you want to look into.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>
|||> Basically anything that would be considered a change to the database for auditing purposes.
Wow. Are you willing to pay the performance penalty for this?
Your best bet is probably to use some of the products out there. They use a combination of
server-side Profiler traces along with reading the transaction log (something which we cannot do
ourselves) so performance impact would be less than if doing it ourselves. Not to mention the dev
time of doing it yourself.
Here's one: http://www.lumigent.com/products/auditdb.html
Some of the other log reader tools vendors might also have such products, I've listed the ones I
know of on my links page: http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Basically anything that would be considered a change to the database for auditing purposes.
> Can you lead me in the right direction on setting up triggers or event notifications? I am new to
> SQL 2005.
> Thank you,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>
|||Maybe I should refrase that a little bit.
Basically I need to have some type of checks and balances setup where an
alert is sent when the dba does something to a database or makes changes.
In the configuration we are using the dba may connect to the SQL server once
a quarter if that.
Warren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
> Wow. Are you willing to pay the performance penalty for this?
> Your best bet is probably to use some of the products out there. They use
> a combination of server-side Profiler traces along with reading the
> transaction log (something which we cannot do ourselves) so performance
> impact would be less than if doing it ourselves. Not to mention the dev
> time of doing it yourself.
> Here's one: http://www.lumigent.com/products/auditdb.html
> Some of the other log reader tools vendors might also have such products,
> I've listed the ones I know of on my links page:
> http://www.karaszi.com/SQLServer/links.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>
|||Did you look at the product?
There's no pre-packaged solution for this. You need to determine exactly what type of changes you
want to be alerted for. Based on that decide whether you find a product that suit your need or if
you want to do it yourself. Depending on what type of changes you want to be alerted for, event
notification can be an option. See Books Online, CREATE EVENT NOTIFICATION.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
> Maybe I should refrase that a little bit.
> Basically I need to have some type of checks and balances setup where an alert is sent when the
> dba does something to a database or makes changes.
> In the configuration we are using the dba may connect to the SQL server once a quarter if that.
> Warren
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>
|||Okay,
Maybe I really just need the basic and I can't figure out how to make it
work. Books on line wasn't very helpful.
Is there a white paper on setting up user auditing for just logging on?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> Did you look at the product?
> There's no pre-packaged solution for this. You need to determine exactly
> what type of changes you want to be alerted for. Based on that decide
> whether you find a product that suit your need or if you want to do it
> yourself. Depending on what type of changes you want to be alerted for,
> event notification can be an option. See Books Online, CREATE EVENT
> NOTIFICATION.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
>
|||> Is there a white paper on setting up user auditing for just logging on?
Only login events. That makes it a very special case, and you can do that with a few clicks. SSMS,
Right-click the server in Object Explorer, Properties, the security window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:%23OOnP485GHA.512@.TK2MSFTNGP06.phx.gbl...
> Okay,
> Maybe I really just need the basic and I can't figure out how to make it work. Books on line
> wasn't very helpful.
> Is there a white paper on setting up user auditing for just logging on?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
>
How to set up a regular job which will put the quesry result into file and email
Dear forum people,
I created a query which suppose to run every 2 weeks. I know how to schedule a job, my question is how to get the query result into a text or excel file.
If I need to use DTS package, please give me a step by step explanation how to move the result into text file and how to email this file.
I am appreciate every answer.
Thank you.
Annyou can do this whole thing with SQLMail.|||Thrasymachus,
please explain me how can I use SQLMail. Please do not get angry with me as I am not sql person, I just create some queries and know how to use some other things from SQL Enterprise Management.
Thank you.|||I do not get angry but I do not repeat readily available information. So, if you go to Start-->Programs-->Microsoft SQL Server-->SQL Server books Online-->Contents-->Admnistering SQL Server-->Managing Servers-->SQL Mail you can read all about this.
If you want to see examples type xp_sendmail in http://www.google.com/codesearch/advanced_code_search.
I created a query which suppose to run every 2 weeks. I know how to schedule a job, my question is how to get the query result into a text or excel file.
If I need to use DTS package, please give me a step by step explanation how to move the result into text file and how to email this file.
I am appreciate every answer.
Thank you.
Annyou can do this whole thing with SQLMail.|||Thrasymachus,
please explain me how can I use SQLMail. Please do not get angry with me as I am not sql person, I just create some queries and know how to use some other things from SQL Enterprise Management.
Thank you.|||I do not get angry but I do not repeat readily available information. So, if you go to Start-->Programs-->Microsoft SQL Server-->SQL Server books Online-->Contents-->Admnistering SQL Server-->Managing Servers-->SQL Mail you can read all about this.
If you want to see examples type xp_sendmail in http://www.google.com/codesearch/advanced_code_search.
Monday, March 12, 2012
how to set SQL Server OLE DB provider index option , It gives error ?
All ,
I have created link server and i want to set all appropriate Setting for the Provider option
Provider used : - Microsoft OLE DB provider for SQL server
After setting “Index as Access Path” check box to true I have encountered
Following error
Server: Msg 7319, Level 16, State 1, Procedure Jobs, Line 2OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_T_Jobs' with incorrect bookmark ordinal 0.OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].
Note :- Remote Query Icon Show 98%
Please let me know how to fix the problem and how to make sure distributed query Uses the proper index on remote Link server
Regards,
RahulB
microsoft ole db provider for sql server is not an index provider. it won't be able use index as access path from the remote sql server.
|||I am facing the same issue. However, i don't much care which index on the oracle box a query uses...i just want the data. How do i avoid this error?
To test things, i took the table on oracle that was generating this error and duplicated it (with data) but without any indexes, and the data returns successfully.
Obviously the idea of removing indexes from oracle tables so the SQL Server can link-server connect to them is not reasonable. My method of connection is the 4-part method. Open query works even on the table...but i don't want to be forced to use that technique.
Here are the two queries...(Query 1 fails, Query 2 works):
1. SELECT * FROM ORCLPAO..SERVICE.BI_EVENT
2. SELECT TOP 10 * FROM OPENQUERY (orclpao, 'select * from service.bi_event')
Query 1 generates this error:
Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'EVENT_APTNUM_XS' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].
how to set SQL Server OLE DB provider index option , It gives error ?
All ,
I have created link server and i want to set all appropriate Setting for the Provider option
Provider used : - Microsoft OLE DB provider for SQL server
After setting “Index as Access Path” check box to true I have encountered
Following error
Server: Msg 7319, Level 16, State 1, Procedure Jobs, Line 2OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_T_Jobs' with incorrect bookmark ordinal 0.OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].
Note :- Remote Query Icon Show 98%
Please let me know how to fix the problem and how to make sure distributed query Uses the proper index on remote Link server
Regards,
RahulB
microsoft ole db provider for sql server is not an index provider. it won't be able use index as access path from the remote sql server.
|||I am facing the same issue. However, i don't much care which index on the oracle box a query uses...i just want the data. How do i avoid this error?
To test things, i took the table on oracle that was generating this error and duplicated it (with data) but without any indexes, and the data returns successfully.
Obviously the idea of removing indexes from oracle tables so the SQL Server can link-server connect to them is not reasonable. My method of connection is the 4-part method. Open query works even on the table...but i don't want to be forced to use that technique.
Here are the two queries...(Query 1 fails, Query 2 works):
1. SELECT * FROM ORCLPAO..SERVICE.BI_EVENT
2. SELECT TOP 10 * FROM OPENQUERY (orclpao, 'select * from service.bi_event')
Query 1 generates this error:
Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'EVENT_APTNUM_XS' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].
Friday, February 24, 2012
How to set date as default
Hi, I probably do something stupid, but I cannot figure out how to set the date as deault when
inserting a record in a table. I have created a Default in the database, giving it a name and the Value = GetDate() and bind it to the desired column.
When I insert a record without filling in a value in this datefield, the table shows me "01/01/1900" in stead of Today.
What am I doing wrong ?
Help is appreciated, Ger.
How are you doing the insert?|||
Here is part of the code :
Dim ConnStr As String = "workstation id=ONTWIKKEL;packet size=4096;integrated security=SSPI;data source='ONTWIKKEL\WEBAPPS';persist security info=False;initial catalog=UPOdyssee"
Dim Conn As SqlClient.SqlConnection = New SqlClient.SqlConnection(ConnStr)
Dim mSQL As String
Dim a(99)
a(1) = Request.Form("HtxtVestiging")
a(2) = Request.Form("HtxtInschrijfdatum")
a(3) = Request.Form("HtxtNaamVoorletters")
a(4) = Request.Form("HtxtRoepnaam")
a(5) = Request.Form("HtxtLoginnaam")
a(6) = Request.Form("HtxtPaswoord")
a(7) = Request.Form("HtxtAdres")
a(8) = Request.Form("HtxtPostcode")
a(9) = Request.Form("HtxtWoonplaats")
a(10) = Request.Form("HtxtTelnr")
a(11) = Request.Form("HtxtEmail")
a(12) = Request.Form("HtxtMobiel")
a(13) = Request.Form("HtxtBereikbaarVia")
a(14) = Request.Form("HtxtGeboortedatum")
a(15) = DDL_Geslacht.SelectedValue
a(16) = Request.Form("HtaWatVoorWerk")
a(17) = DDL_VastTijdelijk.SelectedValue
a(18) = Request.Form("HtxtVanafWelkePeriode")
a(19) = Request.Form("HtxtTotWelkePeriode")
a(20) = DDL_Beschikbaarheid.SelectedValue
a(21) = Request.Form("HtxtVanUren")
a(22) = Request.Form("HtxtTotUren")
a(23) = DDL_Vervoer.SelectedValue
a(24) = DDL_Reisbereidheid.SelectedValue
a(25) = Request.Form("HtxtMaxReistijd")
a(26) = Request.Form("HtxtMaxKM")
a(27) = Request.Form("HtxtInkomenBruto")
a(28) = Request.Form("HtxtInkomenNetto")
a(29) = DDL_Uitkering.SelectedValue
a(30) = Request.Form("HtxtUitkering")
a(31) = Request.Form("HtxtFiscaalnr")
a(32) = DDL_Identificatiebewijs.SelectedValue
a(33) = Request.Form("HtxtSoortLegitimatiebewijs")
a(34) = Request.Form("HtxtLegitimatiebewijsnr")
a(35) = Request.Form("HtxtGeldigTot")
a(36) = DDL_Rijbewijs.SelectedValue
a(37) = Request.Form("HtxtSoortRijbewijs")
a(38) = Request.Form("HtxtRijbewijsnr")
a(39) = DDL_MilitaireDienst.SelectedValue
a(40) = Request.Form("HtxtFunctieMD")
a(41) = Request.Form("HtxtJaarMD")
a(42) = Request.Form("HtxtPaspoortnr")
a(43) = Request.Form("HtxtEinddatumPaspoort")
a(44) = Request.Form("HtxtVergunningnr")
a(45) = Request.Form("HtxtEinddatumVergunning")
a(46) = Request.Form("HtxtBankGiroNr")
a(47) = DDL_Ziektekosten.SelectedValue
a(48) = Request.Form("HtxtPolisnr")
a(49) = DDL_HoeAanOnsGekomen.SelectedValue
a(50) = Request.Form("HtxtOpl1")
a(51) = Request.Form("HtxtOpl2")
a(52) = Request.Form("HtxtOpl3")
a(53) = Request.Form("HtxtOpl4")
a(54) = Request.Form("HtxtOpl5")
a(55) = Request.Form("HtxtOpl6")
a(56) = Request.Form("HtxtOpl7")
a(57) = Request.Form("HtxtRichting1")
a(58) = Request.Form("HtxtRichting2")
a(59) = Request.Form("HtxtRichting3")
a(60) = Request.Form("HtxtRichting4")
a(61) = Request.Form("HtxtRichting5")
a(62) = Request.Form("HtxtRichting6")
a(63) = Request.Form("HtxtRichting7")
a(64) = Request.Form("HtxtDipl1")
a(65) = Request.Form("HtxtDipl2")
a(66) = Request.Form("HtxtDipl3")
a(67) = Request.Form("HtxtDipl4")
a(68) = Request.Form("HtxtDipl5")
a(69) = Request.Form("HtxtDipl6")
a(70) = Request.Form("HtxtDipl7")
a(71) = Request.Form("HtxtJaar1")
a(72) = Request.Form("HtxtJaar2")
a(73) = Request.Form("HtxtJaar3")
a(74) = Request.Form("HtxtJaar4")
a(75) = Request.Form("HtxtJaar5")
a(76) = Request.Form("HtxtJaar6")
a(77) = Request.Form("HtxtJaar7")
a(78) = DDL_NogStudie.SelectedValue
a(79) = Request.Form("HtxtWelkeStudie")
a(80) = Request.Form("HtxtWGVanTot1")
a(81) = Request.Form("HtxtWGVanTot2")
a(82) = Request.Form("HtxtWGVanTot3")
a(83) = Request.Form("HtxtWGVanTot4")
a(84) = Request.Form("HtxtWGVanTot5")
a(85) = Request.Form("HtxtWGVanTot6")
a(86) = Request.Form("HtxtFunctie1")
a(87) = Request.Form("HtxtFunctie2")
a(88) = Request.Form("HtxtFunctie3")
a(89) = Request.Form("HtxtFunctie4")
a(90) = Request.Form("HtxtFunctie5")
a(91) = Request.Form("HtxtFunctie6")
a(92) = Request.Form("HtxtVertrek1")
a(93) = Request.Form("HtxtVertrek2")
a(94) = Request.Form("HtxtVertrek3")
a(95) = Request.Form("HtxtVertrek4")
a(96) = Request.Form("HtxtVertrek5")
a(97) = Request.Form("HtxtVertrek6")
a(98) = Request.Form("HtaBijzOpm")
a(99) = DDL_Functie.SelectedIndex + 1 ' i.v.m. de zerobased van de ddl en de 1-based van de tabel
mSQL = "INSERT into Medewerkers (Vestiging,Inschrijfdatum, Naam, Roepnaam, Loginnaam, Paswoord, "
mSQL = mSQL & "Adres, Postcode, Woonplaats, Telefoonnummer, Email, Mobiel, BereikTel, Geboortedatum, Geslacht, WatVoorWerk, VastTijdelijk, VanafPeriode, TotPeriode, Beschikbaarheid, "
mSQL = mSQL & "VanUren, TotUren, Vervoer, Reisbereidheid, MaxReistijd, MaxKM, InkomenBruto, InkomenNetto, Uitkering, SoortUitkering, Fiscaalnr, "
mSQL = mSQL & "Identificatiebewijs, SoortLegibewijs, Legitimatiebewijs, GeldigTot, Rijbewijs, "
mSQL = mSQL & "Soortrijbewijs, Rijbewijsnr, MilitaireDienst, FunctieMD, JaarMD, Paspoortnr, EinddatumPaspoort, Vergunningnr, EinddatumVergunning, BankGironr, "
mSQL = mSQL & "Ziektekosten, Polisnr, HoeAanOnsGekomen, Opl1, Opl2, Opl3, Opl4, Opl5, Opl6, "
mSQL = mSQL & "Opl7, Richting1, Richting2, Richting3, Richting4, Richting5, Richting6, Richting7, Dipl1, Dipl2, Dipl3, Dipl4, Dipl5, Dipl6, Dipl7, "
mSQL = mSQL & "Jaar1, Jaar2, Jaar3, Jaar4, Jaar5, Jaar6, Jaar7, NogStudie, WelkeStudie, WGVanTot1, WGVanTot2, WGVanTot3, WGVanTot4, WGVanTot5, "
mSQL = mSQL & "WGVanTot6, Functie1, Functie2, Functie3, Functie4, Functie5, Functie6, Vertrek1, Vertrek2, Vertrek3, Vertrek4, "
mSQL = mSQL & "Vertrek5, Vertrek6, BijzOpm, FunctieID) "
mSQL = mSQL & "VALUES ('" & a(1) & "','" &a(2) & "','" & a(3) & "','" & a(4) & "','" & a(5) & "','" & a(6) & "',"
mSQL = mSQL & "'" & a(7) & "','" & a(8) & "','" & a(9) & "','" & a(10) & "','" & a(11) & "','" & a(12) & "',"
mSQL = mSQL & "'" & a(13) & "','" & a(14) & "','" & a(15) & "','" & a(16) & "','" & a(17) & "','" & a(18) & "',"
mSQL = mSQL & "'" & a(19) & "','" & a(20) & "','" & a(21) & "','" & a(22) & "','" & a(23) & "','" & a(24) & "',"
mSQL = mSQL & "'" & a(25) & "','" & a(26) & "','" & a(27) & "','" & a(28) & "','" & a(29) & "','" & a(30) & "',"
mSQL = mSQL & "'" & a(31) & "','" & a(32) & "','" & a(33) & "','" & a(34) & "','" & a(35) & "','" & a(36) & "',"
mSQL = mSQL & "'" & a(37) & "','" & a(38) & "','" & a(39) & "','" & a(40) & "','" & a(41) & "','" & a(42) & "',"
mSQL = mSQL & "'" & a(43) & "','" & a(44) & "','" & a(45) & "','" & a(46) & "','" & a(47) & "','" & a(48) & "',"
mSQL = mSQL & "'" & a(49) & "','" & a(50) & "','" & a(51) & "','" & a(52) & "','" & a(53) & "','" & a(54) & "',"
mSQL = mSQL & "'" & a(55) & "','" & a(56) & "','" & a(57) & "','" & a(58) & "','" & a(59) & "','" & a(60) & "',"
mSQL = mSQL & "'" & a(61) & "','" & a(62) & "','" & a(63) & "','" & a(64) & "','" & a(65) & "','" & a(66) & "',"
mSQL = mSQL & "'" & a(67) & "','" & a(68) & "','" & a(69) & "','" & a(70) & "','" & a(71) & "','" & a(72) & "',"
mSQL = mSQL & "'" & a(73) & "','" & a(74) & "','" & a(75) & "','" & a(76) & "','" & a(77) & "','" & a(78) & "',"
mSQL = mSQL & "'" & a(79) & "','" & a(80) & "','" & a(81) & "','" & a(82) & "','" & a(83) & "','" & a(84) & "',"
mSQL = mSQL & "'" & a(85) & "','" & a(86) & "','" & a(87) & "','" & a(88) & "','" & a(89) & "','" & a(90) & "',"
mSQL = mSQL & "'" & a(91) & "','" & a(92) & "','" & a(93) & "','" & a(94) & "','" & a(95) & "','" & a(96) & "',"
mSQL = mSQL & "'" & a(97) & "','" & a(98) & "','" & a(99) & "')"
Dim Command As SqlClient.SqlCommand = New SqlClient.SqlCommand(mSQL)
Dim x As Integer
Conn.Open()
Command.Connection = Conn
x = Command.ExecuteNonQuery()
Conn.Close()
Command = Nothing
Inschrijdatum is empty, so I expect that the field in the table will be TODAY and not 1/1/1900.
Ger.
|||Two problems:
First, Inschrijdatum isn't empty, it's a zero length string.
Secondly, the only way you are going to get SQL Server to insert the default is one of three ways.
a) Don't mention the column in the insert at all.
b) Tell it to use the DEFAULT like INSERT ... VALUES (...,DEFAULT,...), note there is no quotes around DEFAULT, it is not a string.
c) Explicitly tell it the default value like INSERT ... VALUES (...,GetDate(),...)
That said, replace:
"','" &a(2) & "','"
with:
"'," & IIF(a(2)<>"","'" & a(2) & "'","DEFAULT") & ",'"
|||Oh, I guess I should mention this code is susceptible to SQL injection attacks.
For example, type this in your field labeled "HtaBijzOpm": "','') TRUNCATE Medewerkers --" and submit your form, and you've just deleted all the records from your Medewerkers table.
|||
Motley wrote:
Oh, I guess I should mention this code is susceptible to SQL injection attacks.
For example, type this in your field labeled "HtaBijzOpm": "','') TRUNCATE Medewerkers --" and submit your form, and you've just deleted all the records from your Medewerkers table.
Thanks Motley for replying I will reconstruct the inserts according your advice.
How can I avoid this SQL injection attack ?
Ger.
how to set collation to SQL_Latin1_General_CP1_CI_AS at install ?
help
All my USER DB have been created using the following collationL
SQL_Latin1_General_CP1_CI_AS
My "SQL instance 1" uses :
Latin1_General_CI_AS
Im in the process of setting up "SQL instance 2" and cannot find an option
to set the collation to SQL_Latin1_General_CP1_CI_AS
At install the "COLLATION DESIGNATOR and sort order" field shows
LATIN1_GENERAL.
The "SQL collations...." option below shows various options.
I cannot see an options to setup as "SQL_Latin1_General_CP1_CI_AS" or
"Latin1_General_CI_AS".
How can i use the setup wizard to select "SQL_Latin1_General_CP1_CI_AS"
Thanks for any help
Scott
(SQL 2005 standard - english)CP1 specifies code page 1252, for all other code pages the complete code
page number is specified.
CI specifies case-insensitive
AS specifies accent-sensitive.
Sort order ID SQL collation name
52 SQL_Latin1_General_Cp1_CI_AS
For more information about this topic, you can refer to the following link:
http://msdn2.microsoft.com/en-us/library/ms180175.aspx
Ekrem Önsoy
"Scott" <s@.yahoo.co.uk> wrote in message
news:O2pRP4%23CIHA.1212@.TK2MSFTNGP05.phx.gbl...
> help
> All my USER DB have been created using the following collationL
> SQL_Latin1_General_CP1_CI_AS
> My "SQL instance 1" uses :
> Latin1_General_CI_AS
> Im in the process of setting up "SQL instance 2" and cannot find an option
> to set the collation to SQL_Latin1_General_CP1_CI_AS
> At install the "COLLATION DESIGNATOR and sort order" field shows
> LATIN1_GENERAL.
> The "SQL collations...." option below shows various options.
> I cannot see an options to setup as "SQL_Latin1_General_CP1_CI_AS" or
> "Latin1_General_CI_AS".
> How can i use the setup wizard to select "SQL_Latin1_General_CP1_CI_AS"
> Thanks for any help
> Scott
> (SQL 2005 standard - english)
>
>|||thats helpful, many thanks
scott
All my USER DB have been created using the following collationL
SQL_Latin1_General_CP1_CI_AS
My "SQL instance 1" uses :
Latin1_General_CI_AS
Im in the process of setting up "SQL instance 2" and cannot find an option
to set the collation to SQL_Latin1_General_CP1_CI_AS
At install the "COLLATION DESIGNATOR and sort order" field shows
LATIN1_GENERAL.
The "SQL collations...." option below shows various options.
I cannot see an options to setup as "SQL_Latin1_General_CP1_CI_AS" or
"Latin1_General_CI_AS".
How can i use the setup wizard to select "SQL_Latin1_General_CP1_CI_AS"
Thanks for any help
Scott
(SQL 2005 standard - english)CP1 specifies code page 1252, for all other code pages the complete code
page number is specified.
CI specifies case-insensitive
AS specifies accent-sensitive.
Sort order ID SQL collation name
52 SQL_Latin1_General_Cp1_CI_AS
For more information about this topic, you can refer to the following link:
http://msdn2.microsoft.com/en-us/library/ms180175.aspx
Ekrem Önsoy
"Scott" <s@.yahoo.co.uk> wrote in message
news:O2pRP4%23CIHA.1212@.TK2MSFTNGP05.phx.gbl...
> help
> All my USER DB have been created using the following collationL
> SQL_Latin1_General_CP1_CI_AS
> My "SQL instance 1" uses :
> Latin1_General_CI_AS
> Im in the process of setting up "SQL instance 2" and cannot find an option
> to set the collation to SQL_Latin1_General_CP1_CI_AS
> At install the "COLLATION DESIGNATOR and sort order" field shows
> LATIN1_GENERAL.
> The "SQL collations...." option below shows various options.
> I cannot see an options to setup as "SQL_Latin1_General_CP1_CI_AS" or
> "Latin1_General_CI_AS".
> How can i use the setup wizard to select "SQL_Latin1_General_CP1_CI_AS"
> Thanks for any help
> Scott
> (SQL 2005 standard - english)
>
>|||thats helpful, many thanks
scott
How to set ANSI_NULLS on on a existing table?
Hi,
I was trying to create an index on the indexed view, when I got this
error
Msg 1935 : Cannot create index.Object 'tablename' was created with the
following SET option off: ANSI_NULLS.
Is there anyway inside the database to set ANSI_NULLS on for the
existing table?
Thanks a lot for your help.
AJ> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
You'll need to recreate the table with SET ANSI_NULL ON.
Hope this helps.
Dan Guzman
SQL Server MVP
<aj70000@.hotmail.com> wrote in message
news:1144964972.095350.55480@.t31g2000cwb.googlegroups.com...
> Hi,
> I was trying to create an index on the indexed view, when I got this
> error
> Msg 1935 : Cannot create index.Object 'tablename' was created with the
> following SET option off: ANSI_NULLS.
> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
> Thanks a lot for your help.
> AJ
>|||Hi Dan,
Thanks for the reply. that is a bummer since I have atleast 12 tables
that i need to use for indexed view. and these tables contain financial
information.
AJ|||aj70...@.hotmail.com wrote:
> Hi Dan,
> Thanks for the reply. that is a bummer since I have atleast 12 tables
> that i need to use for indexed view. and these tables contain financial
> information.
> AJ
Were your tables created with ANSI_NULLS OFF by design or due to
ignorance? Unfortunately, ignorance is the usual reason. Take care
always to have ANSI_NULLS set to ON when creating tables or other
objects.
Note that Enterprise Manager defaults to ANSI_NULLS OFF when it's
installed. Those people who create tables with Enterprise Manager often
forget to change that setting first. Your procs and other objects may
suffer the same problem if you create them in EM. The smartest option
is to avoid EM altogether. Don't use EM to create objects.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
I was trying to create an index on the indexed view, when I got this
error
Msg 1935 : Cannot create index.Object 'tablename' was created with the
following SET option off: ANSI_NULLS.
Is there anyway inside the database to set ANSI_NULLS on for the
existing table?
Thanks a lot for your help.
AJ> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
You'll need to recreate the table with SET ANSI_NULL ON.
Hope this helps.
Dan Guzman
SQL Server MVP
<aj70000@.hotmail.com> wrote in message
news:1144964972.095350.55480@.t31g2000cwb.googlegroups.com...
> Hi,
> I was trying to create an index on the indexed view, when I got this
> error
> Msg 1935 : Cannot create index.Object 'tablename' was created with the
> following SET option off: ANSI_NULLS.
> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
> Thanks a lot for your help.
> AJ
>|||Hi Dan,
Thanks for the reply. that is a bummer since I have atleast 12 tables
that i need to use for indexed view. and these tables contain financial
information.
AJ|||aj70...@.hotmail.com wrote:
> Hi Dan,
> Thanks for the reply. that is a bummer since I have atleast 12 tables
> that i need to use for indexed view. and these tables contain financial
> information.
> AJ
Were your tables created with ANSI_NULLS OFF by design or due to
ignorance? Unfortunately, ignorance is the usual reason. Take care
always to have ANSI_NULLS set to ON when creating tables or other
objects.
Note that Enterprise Manager defaults to ANSI_NULLS OFF when it's
installed. Those people who create tables with Enterprise Manager often
forget to change that setting first. Your procs and other objects may
suffer the same problem if you create them in EM. The smartest option
is to avoid EM altogether. Don't use EM to create objects.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
How to set a primary key constraint in a View table using SQL Server 2005
Hi All,
I have created a table using VIEWS in SQL server 2005, now i want to be ablle to edit it in a datagrid but i cannot do so as i there is no primary key!
now does anybody know how to set a primary key constraint so i can set one of the fields as a primary key to identify the row?
many thanks
You can't apply PRIMARY KEY constriant on views.|||Hi,
Is there any way in which i can state one of the columns to be a unique identifier? as i want to be able to edit my datagrid which is populating a VIEW table from SQL '05 but i cannot do so as i do not have a unique number to identify the row, even though one of the columns in the table is a PK in its original table.
any ideas?
|||
If you just want a unique column to identify a row,newid() function is good for you
, which can be used in your CREATE VIEW statements:
use northwind
go
create view v_test as
select o.OrderID, C.ContactName,newid() as ColID
from Orders o join Customers c
on o.CustomerID=C.CustomerID
Sunday, February 19, 2012
How to set a key for a field in a data table
In my SQL Server 2005 database I have created a table with several data fields. One of the fields is designated as a primary key. And another one is just a key.
I can easily set the first field as the primary key, but am not able to set the second field as being just a key field.
I perform these steps to set the primary key.
(1) Right click on the table name and then select 'Open Table Definition'
(2) I right click on the field in my data table and select 'Select Primary Key'
As I mentioned previously, to set another field up as just a key field, I am not able to do this. My choices are the following when I right click on the data field to be designated as the key field:
Set Primary Key, Insert Column, Delete Column,Relationships,Indexes/Keys,Full Text Index,XML Indexes, Check constraints,Properties.
None of these choices will allow me to set the field up as a key field.
Someone please help me out with this problem.
I'm not sure what you're asking for but I can make some guesses. A Primary Key uniquely identifies each record. Therefore there can only be a single primary key for a table. However, a PK can be composed of more than 1 field.
If, however, you have one field which is the key and a second field which contains unique values, then you will want the first field as the key and add an index or constraint for the second field.
HTH.
Subscribe to:
Posts (Atom)