'read' audit

Is there any way to identify which records in a SQL database were 'read'
and by whom? Thanks.
Message posted via http://www.droptable.comBy "read", do you mean returned by a query or involved in a query
computation? Even in a modestly large system, that could be tens of millions
of rows per day. That said, there is perhaps a method to extract this
information from a SQL Profiler event.
"Carolyn via droptable.com" <forum@.droptable.com> wrote in message
news:c8ba5f4b6213454d88acc5502db49860@.SQ
droptable.com...
> Is there any way to identify which records in a SQL database were 'read'
> and by whom? Thanks.
> --
> Message posted via http://www.droptable.com

'read' audit

Is there any way to identify which records in a SQL database were 'read'
and by whom? Thanks.
Message posted via http://www.sqlmonster.com
By "read", do you mean returned by a query or involved in a query
computation? Even in a modestly large system, that could be tens of millions
of rows per day. That said, there is perhaps a method to extract this
information from a SQL Profiler event.
"Carolyn via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c8ba5f4b6213454d88acc5502db49860@.SQLMonster.c om...
> Is there any way to identify which records in a SQL database were 'read'
> and by whom? Thanks.
> --
> Message posted via http://www.sqlmonster.com

RE: Script task and OLEDB destination Performance

Hi fellows,

Sorry to disturb but just a question. I have a package which extracts all the records from table A and update to table B. These records may range from 100,000 to 500,000 records.

So my question is that whether is it more feasibile/efficient to use script task to pump all the rows into table B from table A or use OLEDB destionation using sql command. Which is more efficient and help me increase my package performance? Thanks again.

Regards,

Ken

If they are inserts, I'd use the OLEDB Destination to insert directly into the target table. If they are updates, use an OLEDB Destination to write the data to a temp table, then use a Execute SQL task after the data flow to issue a batch update.

RE: Please help me!!! SQL Newbie here - need help with inserting of records!!!!!

Hi,

I am a SQL and databases newbie and this might seem like a really simple question but I just can't seem to get my head around the concept. I'm building a C# music database app. that manages music files but I am having serious trouble wrapping my head around the initial inserts of all the music MP3s on a hard drive - how do I do the inserts?

I am using the following tables (I have simplified them):

ARTIST table

ARTISTID (PK)

ARTISTNAME

ALBUM table

ALBUMID (PK)

ARTISTID (FK)

ALBUMTITLE

TRACKALBUM

TRACKID(PK)

ALBUMID(FK)

TRACKNUMBER

TRACK

TRACKID

TRACKTITLE

So whats the best way to go about inserting new tracks into this database? I realise this is probably a very simple thing but any help would be VERY MUCH APPRECIATED as this is one of the core functions of my app.

Hi,

The concept of having Foreign key relationships is that you need to have the entry in the parent table to put a referecing key entry in the child table. I would suggest building the tables top-down

1. First populate the Artist table

2. Next populated the Album table and so on

With the referential integrity in place, this would ensure that you do not insert anything in the child table that is not there in the parent table. I am assuming that these tables have one-to-many FK relationships.

The following link could be helpful: http://msdn2.microsoft.com/en-us/library/bb332381.aspx

HTH

Re: Get not null or empty string is not work

Hi all,

Could anyone see where the error is in this IF statement?
I have three records in Shipment_history table with that work_ord_num and w/i those three records one record has a value in cust_part_num field.
I want the query return the cust_part_num value but it returns the record w/o cust_part_num.
Thanks much!

spSomething
:
Declare popu_tbl Cursor

For
SELECT TOP 100 PERCENT dbo.tblShipping_sched.work_ord_num, ...
FROM tbl
WHERE..

Open popu_tbl
Fetch Next From
While ...
Begin

--***
IF @.cust_part_num is null
SELECT @.cust_part_num = cust_part_num FROM dbo.Shipment_history
WHERE work_ord_num like (left(@.work_ord_num, 6) + '%') AND work_ord_line_num = @.work_ord_line_num
AND cust_part_num <> "";
--** the line above doesn't workUse:
IF @.cust_part_num is NOT null

...but why are you doing this in a cursor?

I'd almost guarantee that there are other issues with the code you have ommitted.

Re: code to remove data w/o deleting tbl

Hi,
WHat is the code to remove all records in SQL Server table w/o deleting structure of the table ?
I tried DoCmd.DeleteObject acTable, "tblSamples_completed" it deletes the table object.
Is Drop TABLE the right syntax in VBA?
ThanksThe safe answer would be:DELETE FROM tblSamples_completedThe quick answer would be:TRUNCATE TABLE tblSamples_completed-PatP

Re : Restore table from Transaction Log

Hi,
After I copied some views from other database, I lost all records in five
tables. Unfortunely, I didn't have the backup file.
I tried to use Lumigent to restore from the Transaction log, and three of
them have been recovered successfully. But still have two tables cannot
restore.
Anyone can share some ideas how to restore the rest two.
Many many many thanks!Hi
Copying views will not delete data or drop tables, I can only assume that
you have scripted the view and it's dependent objects from Enterprise
Manager. In which case I think you should be able to restore all the tables
using the Lumigent product.
Failing that, as you don't have a backup, then the tables must be on the
system you scripted the view from.
John
"Bevis" wrote:

> Hi,
> After I copied some views from other database, I lost all records in five
> tables. Unfortunely, I didn't have the backup file.
> I tried to use Lumigent to restore from the Transaction log, and three of
> them have been recovered successfully. But still have two tables cannot
> restore.
> Anyone can share some ideas how to restore the rest two.
> Many many many thanks!
>
>

rdlc show querystring parameter in page header

I pass in 3 querystring parameters to my web form. The Object Data Sources pick up these parameters

and select the appropriate records.

I want to display one of the querystring parameters in my Page Header, specifically the one for Fiscal Year.

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned...Therefore, I must get the querystring parameter that was originally passed in...

How do I populate the report control textbox with the value of querystring parameter?

Thanks!

Jim

JIM_LANGDON wrote:

I pass in 3 querystring parameters to my web form. The Object Data Sources pick up these parameters

and select the appropriate records.

I want to display one of the querystring parameters in my Page Header, specifically the one for Fiscal Year.

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned...Therefore, I must get the querystring parameter that was originally passed in...

How do I populate the report control textbox with the value of querystring parameter?

Thanks!

Jim

Are you using a report generator?

If not, then isn't this is as simple as setting the expression of the textbox to:

=Fields!FiscalYear.Value

|||

I don't know what you mean by "Report Generator"..isn't it SSRS?

I pass in the parameter via the call to the webform that contains the

reportviewer control -- webform.aspx?fy=2007.

The reportviewer control that is bound to the report.rdlc.

The RDLC has several datasources which show up as objectdatasource controls

on the web form. The objectdatasource controls are configured to use the appropriate

datasource method and the parameters are defined to use the querystring and "fy" as

the parameter name.

Doesn't the Fields! collection just have the datasource columns? I tried what you suggested

Fields!fy.Value, since the name in querystring is "fy"...no success.

|||

JIM_LANGDON wrote:

I tried what you suggested

Fields!fy.Value, since the name in querystring is "fy"...no success.

This is probably because you need to specify the dataset on the textbox. Right click the textbox and select properties. In the "containing group or dataset" textbox, you need to either select or enter the dataset that contains 'fy'.

Basically, you can treat a textbox similarly to a table/matrix. It can have a dataset associated with it and you can populate the textbox with any value from the parameters or fields list.

|||

But the querystring does not belong to a "dataset"....so I can't reference it with Fields!fieldname. Hmmm.

|||

Isn't fiscal year one of your fields in a dataset? Aren't you trying to get fiscal year in a textbox?

Why do you want to touch a querystring?

|||

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned...Therefore, I must get the querystring parameter that was originally passed in...

The goal is to display a specific querystring parameter in the page header of the report.

|||

JIM_LANGDON wrote:

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned

Yes, and you could conditionally specify what the Fiscal Year should default to if no records are returned.

If you'd like to pursue the querystring path to resolve this, feel free. I'm just somewhat confused with your reasoning.

|||

The Fiscal Year that is passed to the web form is 2006. If no Fiscal Year is passed, it will default to 2007. The records selected are based on the year that is passed in.

If I pass in the year 2006, I want the page header to display "Fiscal Year: 2006" and the body to display "No records found."

It seems clear to me that the querystring is the only place to logically capture the Fiscal Year. Is there another method you had in mind to meet the goal or another way to do things to accomplish the same thing?

|||

JIM_LANGDON wrote:

It seems clear to me that the querystring is the only place to logically capture the Fiscal Year. Is there another method you had in mind to meet the goal or another way to do things to accomplish the same thing?

Maybe I just don't know what you mean by "querystring".

I think everything that you have mentioned so far can be accomplished by using a dataset (as either text or stored procedure).

|||

Sounds like what you have to do is one of the following:

add a new parameter to the report (with no valid values) and set the value of this parameter from the querystring. In your report textbox use Parameters!<your param name>.value to display the value

OR

create a new dataset in your report and load the querystring parameters into it in code, pass this additional dataset to the report

|||

The report page is initiated by a link <a>, html anchor tag.

<a href="report_web_form.aspx?fy=2006">Run This Report</a>

The href property assignment is what I mean by "querystring".

The dataset that is generated uses the 2006 value to determine what

records to select. If no records are returned, you cannot use the dataset to

get the value 2006. I don't see how this can be accomplished using a dataset.

|||

Thanks Adam..

From your first solution how do I "set the value of this parameter from the querystring". Where is that done. Are we talking about an assignment in the page load event of the report web form?

From your second solution how do I "load the querystring parameters into it in code"?

Sorry, I just need a bit more explanation.

|||

OK, here we go. The solution. Thanks to the exhausting yet always productive discussion today.

1. Define a report parameter in the report.rdlc:

Name: FISCAL_YEAR

Data Type: String

"Hidden" checkbox checked.

Default Values: "null" checkbox checked.

2. Place a text box in the page header of the report.rdlc with the value "=parameters!FISCAL_YEAR.Value".

3. Place the following code in the report web form:

Imports Microsoft.Reporting.WebForms

Partical Class reportWebForm

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim _fiscalYearParameter As New ReportParameter("FISCAL_YEAR", "2007")

_fiscalYearParameter.Values(0) = Request.QueryString("fy")

ReportViewer.LocalReport.SetParameters(New ReportParameter() {_fiscalYearParameter})

End Sub

Raw File Source issue

I have a single file that contains records destined for multiple tables. The "first" record is considered primary and the other records are considered "secondary" (meaning that they have foreign keys to the primary table).

In order to properly insert this I needed to use two data flows. The first data flow directed the primary rows to the primary table and the secondary rows get directed to a raw file destination. The second data flow read in from the raw file and wrote out the rows to the appropriate tables.

But here is my problem.

This darn validation! While I think validation is a great idea, the extensive use of it in what seems like EVERY aspect of SSIS seems to cause more headaches than not...

When I deploy my package and try to run it I get an error because the raw file source DOES NOT EXIST. Of course it does not exist, it gets created when the package runs... I cannot deploy something that does not exist yet.

I even have a problem while I am trying to work with the package in VS. The only way to get the package to run is to disable the second data flow so it does not try to validate it. Run the package so the raw file is created. And then re-enable the second data flow again. (Which then I guess I could take the raw file and deploy it with my package but that just seems silly.... deploying temporary files... that would be like deploying Internet Explorer with the Temporary Internet Files folders....)

And of course with that type of solution my package could never "clean up" after itself...

Try setting DelayValidation to TRUE for all source / destination components

Thanks,
Sankaranarayanan MG

|||I have used DelayValidation on other objects but I do not see any property of that sort when looking at the Raw File Source. The only thing I see with the word valid is ValidateExternalMetadata. Should I be looking elsewhere?|||

Yes, DelayValidation is a task property not a component property so you would need to set it on the DataFlow task that contains the component you need to have validation delayed on. Note that this delays the validation for all the components in the task not just the one component you need it for.

HTH,

Matt

Rating System / AVG (Easy Question)

I am building a rating system, and there will be lots of records that will
simply be 1,2,3,4 or 5. tinyInt obviously works fine for this, BUT:
I want to do something like 'SELECT AVG(ratings) as avgRating' And this
always returns an tinyint datatype since the ratings are tinyints. I want
slightly more precision than a tinyint for the average, what I want is
something in the format of '#.##' (i.e. 3.45). What datatypes do I need to
store the ratings in and what command can I use to get JUST a #.##?
I've changed the datatypes to money and decimal and used the ROUND function,
but it still returns something like '3.4500'.
Any help is appreciated, thanks in advance
CraigThis is a multi-part message in MIME format.
--=_NextPart_000_002A_01C36729.1E7833E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select avg (cast (rating as numeric (3, 2)))
from MyTable
--
Tom
"Craig S" <craig@.removethis_birch.net> wrote in message =news:eVy6Pe0ZDHA.3768@.tk2msftngp13.phx.gbl...
> I am building a rating system, and there will be lots of records that =will
> simply be 1,2,3,4 or 5. tinyInt obviously works fine for this, BUT:
> > I want to do something like 'SELECT AVG(ratings) as avgRating' And =this
> always returns an tinyint datatype since the ratings are tinyints. I =want
> slightly more precision than a tinyint for the average, what I want is
> something in the format of '#.##' (i.e. 3.45). What datatypes do I =need to
> store the ratings in and what command can I use to get JUST a #.##?
> > I've changed the datatypes to money and decimal and used the ROUND =function,
> but it still returns something like '3.4500'.
> > Any help is appreciated, thanks in advance
> Craig
> > --=_NextPart_000_002A_01C36729.1E7833E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
select avg (cast (rating as =numeric (3, 2)))
from MyTable
Tom
"Craig S" wrote in message news:eVy6Pe0ZDHA.3768@.tk2msftngp13.phx.gbl...> I =am building a rating system, and there will be lots of records that will> =simply be 1,2,3,4 or 5. tinyInt obviously works fine for this, BUT:> => I want to do something like 'SELECT AVG(ratings) as avgRating' And =this> always returns an tinyint datatype since the ratings are tinyints. I want> slightly more precision than a tinyint =for the average, what I want is> something in the format of '#.##' (i.e. 3.45). What datatypes do I need to> store the ratings in =and what command can I use to get JUST a #.##?> > I've changed the datatypes to money and decimal and used the ROUND function,> but =it still returns something like '3.4500'.> > Any help is =appreciated, thanks in advance> Craig> >

---

--=_NextPart_000_002A_01C36729.1E7833E0--

Rather empty gatherer logs

I noticed that we were getting no results in our search this morning.
The records were in the DB, search terms matched when using "LIKE" but
not CONTAINS. Once I did a repopulation (incremental) all worked fine.
What I don't understand is that the repopulation is actually scheduled
to run each day at 00:00 and according to the gatherer logs, it did
run.
15/02/2006 00:00:06 Add Started Incremental crawl
15/02/2006 00:03:16 Add Completed Incremental crawl
15/02/2006 16:17:02 Add Started Full crawl
15/02/2006 16:23:30 Add Completed Full crawl
It is also puzzling that the manual repopulation (I started) says
"Started Full".
We had no errors or warnings in the application log.
Why are the gatherer logs so small/empty - its that because there were
no errors? If so, why did we get no results - I know the records were
there at 00:00.
Perhaps you made a schema change at 4:17 and this triggered a full
population. I can find no record of incremental populations being kicked off
at midnight either.
Are you certain there is no incremental population scheduled at midnight? I
take it this is SQL 2000.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jack" <cawoodm@.gmail.com> wrote in message
news:1140019316.201183.231620@.g43g2000cwa.googlegr oups.com...
>I noticed that we were getting no results in our search this morning.
> The records were in the DB, search terms matched when using "LIKE" but
> not CONTAINS. Once I did a repopulation (incremental) all worked fine.
> What I don't understand is that the repopulation is actually scheduled
> to run each day at 00:00 and according to the gatherer logs, it did
> run.
> 15/02/2006 00:00:06 Add Started Incremental crawl
> 15/02/2006 00:03:16 Add Completed Incremental crawl
> 15/02/2006 16:17:02 Add Started Full crawl
> 15/02/2006 16:23:30 Add Completed Full crawl
> It is also puzzling that the manual repopulation (I started) says
> "Started Full".
> We had no errors or warnings in the application log.
> Why are the gatherer logs so small/empty - its that because there were
> no errors? If so, why did we get no results - I know the records were
> there at 00:00.
>
|||> I can find no record of incremental populations being kicked off at midnight either.
What's this then?
15/02/2006 00:00:06 Add Started Incremental crawl
15/02/2006 00:03:16 Add Completed Incremental crawl
I assumed this was the scheduled midnight incremental?
You may be right about the schema change but then I am still missing my
manual incremental in the log files around this time (16:20)
|||I meant to say, I can find no record of incremental crawls being kicked off
by default on any of my machines Sorry for the confusion.
It certainly looks like you somehow have an incremental crawl schededuled
for midnight. I notice that if I schedule an incremental population, by
default it is scheduled for 1 minute before midnight. Check in the
management folder to see if you have it scheduled there. It will be
categorized as Full-Text.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jack" <cawoodm@.gmail.com> wrote in message
news:1140034764.911389.60000@.g44g2000cwa.googlegro ups.com...
> What's this then?
> 15/02/2006 00:00:06 Add Started Incremental crawl
> 15/02/2006 00:03:16 Add Completed Incremental crawl
> I assumed this was the scheduled midnight incremental?
> You may be right about the schema change but then I am still missing my
> manual incremental in the log files around this time (16:20)
>
|||I think the orginal problem (no results) must be due to a schema
change. Apparently the FT Catalog gets cleared when this happens. I
still think the "Started Full crawl" is a mystery but sometimes this
happens.
Thanks
Jack