A way to add .Net classes in a report?

I know that subject line might be a little confusing - -
I have a project with a .vb class, that, when accessing a web page, based on
the user's login, I grab their Employee ID. With that, I can conceivably
create a method to get a list of employees who report to that person
Is there a way to include a .vb class in a report project, and then, access
a particular method (in this case, getting the list of direct reports), in
the document map?Elmo,
I have done something similar. You should be able to create a reportviewer
in an aspx page and access your class in the aspx code...pass the subsequent
values to the report.
billN
--
Message posted via http://www.sqlmonster.com|||Will I be able to use the same report file (.rdl) that I designed in a
Report Server Project in BI?
I tried adding a .rdl file to a ReportViewer control one time, and it wasn't
recognized.
"wnichols via SQLMonster.com" <u3357@.uwe> wrote in message
news:7e587ee1771a4@.uwe...
> Elmo,
> I have done something similar. You should be able to create a
> reportviewer
> in an aspx page and access your class in the aspx code...pass the
> subsequent
> values to the report.
> billN
> --
> Message posted via http://www.sqlmonster.com
>

A view based on a parameter

Hello.

After upsizing an access mdb backend to SQL Server 2005, some of the access frontend queries need rewriting.

One of my Access queries contains several joined tables (tables that used to live on users local c drive) and also references the value of a form control as its where criteria. When this is run against linked sql tables it is very slow.

So I thought I would use a view to represent the joined tables and link to that in Access, but views don't allow you to pass parameters. How do I create a view that I can link to in my access front end whose contents will vary based on a passed parameter from Access? I am OK at vba coding so I can call to this object in code if necessary. The recordsource needs to be updateable.

Any help would be appreciated!

Shirley

It sounds like you might want a stored procedure or user-defined function. Both of those allow you to pass parameters, both in and out. I found a really good reference to an Access-to-SQL Server information site here:

http://www.informit.com/discussion/index.asp?postid=6a35b938-8029-40a8-9511-95bd1b7f1255&rl=1

Buck Woody

A view based on a parameter

Hello.

After upsizing an access mdb backend to SQL Server 2005, some of the access frontend queries need rewriting.

One of my Access queries contains several joined tables (tables that used to live on users local c drive) and also references the value of a form control as its where criteria. When this is run against linked sql tables it is very slow.

So I thought I would use a view to represent the joined tables and link to that in Access, but views don't allow you to pass parameters. How do I create a view that I can link to in my access front end whose contents will vary based on a passed parameter from Access? I am OK at vba coding so I can call to this object in code if necessary. The recordsource needs to be updateable.

Any help would be appreciated!

Shirley

It sounds like you might want a stored procedure or user-defined function. Both of those allow you to pass parameters, both in and out. I found a really good reference to an Access-to-SQL Server information site here:

http://www.informit.com/discussion/index.asp?postid=6a35b938-8029-40a8-9511-95bd1b7f1255&rl=1

Buck Woody

a trigger code

I have table T1 and I am trying to extract some date from T1 table based on inserts, updates and deletes. The destination table T2 has three fields F1, F2, F3, so I need something like

Insert into T2 (F1, F2, F3)

Select (F1, F2,Type)

From T1

Type should be defined based on Insert, Update, or Delete. This will be my first trigger, Can anyone write this trigger for me?

If you want to determine type of trigger action from single trigger then see code below:

set nocount on
go
if object_id('tr_tbl') is not null
drop table tr_tbl
go
create table tr_tbl(i int)
go
create trigger tr_test on tr_tbl for insert, update, delete as
if exists(select * from inserted) and exists(select * from deleted)
print 'Update...' -- set @.type = 'U'
else
if exists(select * from inserted)
print 'Insert...' -- set @.type = 'I'
else
print 'Delete...' -- set @.type = 'D'

... your code
go

insert tr_tbl values(1)
update tr_tbl set i = i + 1
delete tr_tbl

go

drop table tr_tbl

go

But it might be easier writing separate triggers. And you shouldn't query the base table T1 directly since you will get all rows in the table and not just the rows affected by the DML. You need to use the inserted/deleted tables based on the trigger action.

|||

This helps a lot, thanks,

One question: since there are more than one user on the system will there be a case that insert virtual table might have more than one row?

|||Yes, you should always write your trigger code such that it handles multiple rows. This will also force you to use simple set of DMLs from trigger for the logic which is more efficient than a procedural approach. If you want to enforce only singleton operations via DML then you can do so within trigger by checking for @.@.ROWCOUNT and rolling back the transaction/ DML operation.sql

a trigger code

I have table T1 and I am trying to extract some date from T1 table based on inserts, updates and deletes. The destination table T2 has three fields F1, F2, F3, so I need something like

Insert into T2 (F1, F2, F3)

Select (F1, F2,Type)

From T1

Type should be defined based on Insert, Update, or Delete. This will be my first trigger, Can anyone write this trigger for me?

you already have the code you need. you just need to put it in a trigger for which the syntax can be found in BOL.

a question or two re fulltext queries

hi,
i'm using SQL Server 2000 (SP3, I think).
i am looking at SQL for finding documents (table DOC) based on a couple of
criteria, one of which involves a fulltext index on a related table (TXT).
i discovered today that a less selective CONTAINS() clause:
CONTAINS (txt_stripped, 'daimler')
results in disastrous performance when I ask for the "top 10" documents
sorted by published date (a field of DOC) descending.
if I
1) include a more selective contains clause:
CONTAINS (txt_stripped, 'daimler AND mercedes')
2) omit the "top 10" (there are only125 documents total.) or
3) omit the "ORDER BY doc_pubfrom DESC" clause
the query performs fine.
i examined the query plans and discovered that in the bad case the first
thing done is to walk the doc_pubfrom index (figures, I guess: top 10 order
by...). in the other cases the first thing done is the "remote scan" of the
fulltext index, which is what I want done first.
i already know how I'll solve this problem: i don't need the "top 10" at
this point -- it was just a whim while prototyping.
my questions, though for future reference:
1) is there syntax to influence SQL's query planning specifically to prefer
the full text index ?
2) is there a way to get the query plan textually? (the graphic view is
great but I wondered what I would do if I wanted to mail it or post it)
cheers,
Tim Hanson
1) Could you post the entire schema with indexes?
2) use set showplan_text on
Hilary Cotter
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
"tbh" <femdev@.newsgroups.nospam> wrote in message
news:uTlE0P$KGHA.1180@.TK2MSFTNGP09.phx.gbl...
> hi,
> i'm using SQL Server 2000 (SP3, I think).
> i am looking at SQL for finding documents (table DOC) based on a couple of
> criteria, one of which involves a fulltext index on a related table (TXT).
> i discovered today that a less selective CONTAINS() clause:
> CONTAINS (txt_stripped, 'daimler')
> results in disastrous performance when I ask for the "top 10" documents
> sorted by published date (a field of DOC) descending.
> if I
> 1) include a more selective contains clause:
> CONTAINS (txt_stripped, 'daimler AND mercedes')
> 2) omit the "top 10" (there are only125 documents total.) or
> 3) omit the "ORDER BY doc_pubfrom DESC" clause
> the query performs fine.
> i examined the query plans and discovered that in the bad case the first
> thing done is to walk the doc_pubfrom index (figures, I guess: top 10
> order by...). in the other cases the first thing done is the "remote scan"
> of the fulltext index, which is what I want done first.
> i already know how I'll solve this problem: i don't need the "top 10" at
> this point -- it was just a whim while prototyping.
> my questions, though for future reference:
> 1) is there syntax to influence SQL's query planning specifically to
> prefer the full text index ?
> 2) is there a way to get the query plan textually? (the graphic view is
> great but I wondered what I would do if I wanted to mail it or post it)
> cheers,
> Tim Hanson
>
|||thanks, Hillary, that was quick!
re 1) by "schema" you mean all affected tables? that would be a serious
handful. i'd hate to trouble you and post so much stuff. do you have any
general hints on keywords for nudging the query plan in favor of fulltext
index first?
re 2) thanks!!
cheers,
Tim
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OPg4yc$KGHA.668@.TK2MSFTNGP11.phx.gbl...
> 1) Could you post the entire schema with indexes?
> 2) use set showplan_text on
> --
> Hilary Cotter
|||Just the problem tables. I don't have any hints off the top of my head. You
might be able to do a force order query hint which might help.
Hilary Cotter
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
"tbh" <femdev@.newsgroups.nospam> wrote in message
news:uyE2Ow$KGHA.4052@.TK2MSFTNGP15.phx.gbl...
> thanks, Hillary, that was quick!
> re 1) by "schema" you mean all affected tables? that would be a serious
> handful. i'd hate to trouble you and post so much stuff. do you have any
> general hints on keywords for nudging the query plan in favor of fulltext
> index first?
> re 2) thanks!!
> cheers,
> Tim
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OPg4yc$KGHA.668@.TK2MSFTNGP11.phx.gbl...
>
>
|||I have the same problem, when I search for a (litle) word that apears
in many records, the query that uses full-text index takes to long to
execute.
I use Top 1000, Contains() and Order By.
How can I optimize this without changing the TOP and Order by?
Thaks
|||Can you use a containstable and the top_n_by_rank clause? IE
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
, 10
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
Hilary Cotter
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
<paulo.gonc@.gmail.com> wrote in message
news:1139421012.509817.170610@.g43g2000cwa.googlegr oups.com...
>I have the same problem, when I search for a (litle) word that apears
> in many records, the query that uses full-text index takes to long to
> execute.
> I use Top 1000, Contains() and Order By.
> How can I optimize this without changing the TOP and Order by?
> Thaks
>

A question on Conversation timer persistence

I'd like to add code to a trigger to calculate the time to fire a message into a queue based on a field changing, and conversation timers seem like the way to go. My first question refers to this line from the BOL:

"Calling BEGIN CONVERSATION TIMER on a conversation before the timer has expired sets the timeout to the new value."

I think that in this trigger, I can simply begin a new conversation if the given field has changed to reset the timer. But intuition tells me that in order to change the timer to a new value, I need to retrieve the existing conversation, correct?

Also, I've read that conversation timers are persistent in that they survive database restarts and shutdowns. But I'm not sure to what extent. After a database restart/shutdown, does the conversation timer "reset" itself to the time interval specified when the conversation was begun or is it able to account for the time the database was down/offline?

Thanks,

Chris

I'm not sure I understand the requirements. Why is that you need to fire a timer as a result of a field change? The usual requirement is to fire a message so that some asynch processing happens later, but not based on a timer. Can you give some more details?

You can have only one timer per conversation. That what the BOL line refers to. You cannot have multiple timers, setting a new timer will erase the old one.

The timers are set as absolute time, not interval. After a database/server restart, if the time of the timer is in the past, then the timer will be fired.

HTH,
~ Remus

|||

Remus,

Thanks for the quick response.

Here's the workflow of the process: A user creates a work order for which they can assign a follow-up time. When this follow-up time arrives, I want to send a message to a Service Broker queue that I have already set up to process the message. If the follow-up time changes, the timer is adjusted to account for the change in time.

The only difference between what I need to do now and what I've already done is sending the message to the queue at a specific time. I imagined a trigger that would fire every time the follow-up time changed so that I could alter the conversation timer. This trigger would begin a new conversation, set a timer, and an activated stored procedure would look for the message type http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer and send a message to my original queue where it will be processed. I see 2 problems with my logic: I am looking for a DialogTimer message type, so the activated stored procedure only knows it is time to do something, but it has no message body that I can use to forward onto the final Service Broker queue. Also, I have no way of finding the conversation I started the last time the trigger fired.

I'm wondering if using a conversation timer is the wrong approach, and if so, what is?

Thanks,

Chris

|||

What you need is a table to associate the conversation which fired the timer with the original work order. When the work order is created, the trigger begin a dialog, sets the timer and then inserts into this table the newly created conversation handle and the work order id.

When the timer fires, the activated procedure receives the message, looks up the work order id in this table (based on the conversation handle the message was RECEIVE on) and does whatever work is required at that moment.

The same table can be also used when updates occur on the follow_up field. Instead of beginning a conversation, the trigger will look up this association table and find the existing conversation.

One thing to note is that timer messages are unlike any message in the sense that they are sent by one conversation endpoint to itself. So the conversation handle on which the timer was set is the same one as on which is going to be received.

I do believe that conversation timers are the right approach. No other approach I can think of is better. Conversation timers are very cheap from a resource point of view, completely contained within the database (this gives lots of advantages related to backup/restore, failover and availability), and offer the possibility to actually luch a procedure.

HTH,
~ Remus

|||

Thanks a lot Remus. A state table was what I came up with as well. I really appreciate being able to come here for valuable, practical advice on how to approach Service Broker issues. Thanks again,

Chris

A problem on Group Sorting

is it true that the group sorting expert can only sort the groups according based on a summary?

I have inserted a formula field on the group header, and i want to sort the groups based on the value of this formula field.. Is it possible to do so?
I am using crystal report 9...

thanksIn the menu, goto report->Recordsort Export; Select the formula and add it to sortable list