how to incorporate data from more than one table(having no relation) in CR9

how to incorporate data from more than one table(having no relation) in detail section of crystal report 9...In CR, on your left you have a "Field Explorer"
In there you have "Database Fields", you right click them and chose "Database Expert".
Then you chose meke new connection and you chose witch tables to import in your CR.
If the tables are in no relationship, thet is not a problem, you just won't be able to connect them together.
Afcourse CR will inform you about that, but just click OK a that should be it.
Afcourse if you don't get data from this tables (in code) you won't be able to show data.

How to include a dataset field value in the header

Since I can not directly use a field value of a dataset in the header and
footer section of a report, I'm trying to store this field value in a
variable in the custom code.
Is there a way to do this?
Thanks...I would suggest using parameter with default value from query.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"eralper" <eralper@.discussions.microsoft.com> wrote in message
news:5F028ED8-E8BC-4795-A79E-3481CECC9EA6@.microsoft.com...
> Since I can not directly use a field value of a dataset in the header and
> footer section of a report, I'm trying to store this field value in a
> variable in the custom code.
> Is there a way to do this?
> Thanks...sql

how to implement locking

Take for example ticketmaster where they lock a certain section, row and
even seat numbers for a duration..
I am assuming all this info may be in one table . How can one ensure row
level locking without SQL Server escalating it to some high level lock.
And even if a particular row is locked, does that mean that one can update
rows that are not locked
Trying to find a solution to do locking at a row level while still leaving
other rows for DML i.e select, updates,inserts,deletes
Thanks
On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
wrote:
>Take for example ticketmaster where they lock a certain section, row and
>even seat numbers for a duration..
>I am assuming all this info may be in one table . How can one ensure row
>level locking without SQL Server escalating it to some high level lock.
>And even if a particular row is locked, does that mean that one can update
>rows that are not locked
>Trying to find a solution to do locking at a row level while still leaving
>other rows for DML i.e select, updates,inserts,deletes
You can always add an IsLocked field to the table, SQLServer will
never escalate that, and it won't block other operations, and will
even still be there if you reboot the server!
J.
|||I think we may get into design here. How will I model the data ? Take for
example Ticketmaster.
And consider high concurrency needed and not being able to block one
another.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:me34h1lcijtg0h301o85u0cj84ds2dbrfa@.4ax.com...
> On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
> wrote:
> You can always add an IsLocked field to the table, SQLServer will
> never escalate that, and it won't block other operations, and will
> even still be there if you reboot the server!
> J.
>
|||If they have the proper indexes and WHERE clauses they will have to lock
lots of rows in the single transaction before it will escalate to table.
But if you want to ensure it never escalates you can add a dummy row and
have a connection lock it all the time. As long as there is another lock in
the table at any level you can not escalate to a table lock. Of coarse that
means if you do scans you might have an issue but if you have the right
indexes and such it shouldn't be an issue.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Take for example ticketmaster where they lock a certain section, row and
> even seat numbers for a duration..
> I am assuming all this info may be in one table . How can one ensure row
> level locking without SQL Server escalating it to some high level lock.
> And even if a particular row is locked, does that mean that one can update
> rows that are not locked
> Trying to find a solution to do locking at a row level while still leaving
> other rows for DML i.e select, updates,inserts,deletes
> Thanks
>
|||Locking a dummy row is a clever way of avoiding lock escalation on a
particular table. However, please do not overuse this. Having long running
open transaction is not desirable. It should only be used as a workaround
for heavy contention issues as a result of lock escalation. Disabling lock
escalation in general may result in slower performance.
By default, SQL Server does not escalate unless more than 5000 locks are
obtained in the current statement for a particular table. If the query plan
does not contain a range scan, then most likely you will not encounter
escalation. If it does have a range scan, then you could analyze if that
range scan has a chance of encountering a lot of rows.
If you do need to implement the dummy row locking idea, be sure not to do
anything else in the transaction which holds the long term lock. So it
should be something like this:
-- if you do not want escalated X table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table where PrimyarKeyColumn = dummy_row_key_value
wait for delay ...
commit
-- if you do not want escalated S table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table with (UPDLOCK) where PrimyarKeyColumn =
dummy_row_key_value
wait for delay ...
commit
If you are planning to use SQL Server 2005, then the new
Read-Committed-Snapshot-Isolation feature guarantees that there is no S
table lock for reads.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ON%23waYCrFHA.3640@.tk2msftngp13.phx.gbl...
> If they have the proper indexes and WHERE clauses they will have to lock
> lots of rows in the single transaction before it will escalate to table.
> But if you want to ensure it never escalates you can add a dummy row and
> have a connection lock it all the time. As long as there is another lock
> in the table at any level you can not escalate to a table lock. Of coarse
> that means if you do scans you might have an issue but if you have the
> right indexes and such it shouldn't be an issue.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
>

how to implement locking

Take for example ticketmaster where they lock a certain section, row and
even seat numbers for a duration..
I am assuming all this info may be in one table . How can one ensure row
level locking without SQL Server escalating it to some high level lock.
And even if a particular row is locked, does that mean that one can update
rows that are not locked
Trying to find a solution to do locking at a row level while still leaving
other rows for DML i.e select, updates,inserts,deletes
ThanksOn Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
wrote:
>Take for example ticketmaster where they lock a certain section, row and
>even seat numbers for a duration..
>I am assuming all this info may be in one table . How can one ensure row
>level locking without SQL Server escalating it to some high level lock.
>And even if a particular row is locked, does that mean that one can update
>rows that are not locked
>Trying to find a solution to do locking at a row level while still leaving
>other rows for DML i.e select, updates,inserts,deletes
You can always add an IsLocked field to the table, SQLServer will
never escalate that, and it won't block other operations, and will
even still be there if you reboot the server!
J.|||I think we may get into design here. How will I model the data ? Take for
example Ticketmaster.
And consider high concurrency needed and not being able to block one
another.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:me34h1lcijtg0h301o85u0cj84ds2dbrfa@.
4ax.com...
> On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
> wrote:
> You can always add an IsLocked field to the table, SQLServer will
> never escalate that, and it won't block other operations, and will
> even still be there if you reboot the server!
> J.
>|||If they have the proper indexes and WHERE clauses they will have to lock
lots of rows in the single transaction before it will escalate to table.
But if you want to ensure it never escalates you can add a dummy row and
have a connection lock it all the time. As long as there is another lock in
the table at any level you can not escalate to a table lock. Of coarse that
means if you do scans you might have an issue but if you have the right
indexes and such it shouldn't be an issue.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Take for example ticketmaster where they lock a certain section, row and
> even seat numbers for a duration..
> I am assuming all this info may be in one table . How can one ensure row
> level locking without SQL Server escalating it to some high level lock.
> And even if a particular row is locked, does that mean that one can update
> rows that are not locked
> Trying to find a solution to do locking at a row level while still leaving
> other rows for DML i.e select, updates,inserts,deletes
> Thanks
>|||Locking a dummy row is a clever way of avoiding lock escalation on a
particular table. However, please do not overuse this. Having long running
open transaction is not desirable. It should only be used as a workaround
for heavy contention issues as a result of lock escalation. Disabling lock
escalation in general may result in slower performance.
By default, SQL Server does not escalate unless more than 5000 locks are
obtained in the current statement for a particular table. If the query plan
does not contain a range scan, then most likely you will not encounter
escalation. If it does have a range scan, then you could analyze if that
range scan has a chance of encountering a lot of rows.
If you do need to implement the dummy row locking idea, be sure not to do
anything else in the transaction which holds the long term lock. So it
should be something like this:
-- if you do not want escalated X table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table where PrimyarKeyColumn = dummy_row_key_value
wait for delay ...
commit
-- if you do not want escalated S table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table with (UPDLOCK) where PrimyarKeyColumn =
dummy_row_key_value
wait for delay ...
commit
If you are planning to use SQL Server 2005, then the new
Read-Committed-Snapshot-Isolation feature guarantees that there is no S
table lock for reads.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ON%23waYCrFHA.3640@.tk2msftngp13.phx.gbl...
> If they have the proper indexes and WHERE clauses they will have to lock
> lots of rows in the single transaction before it will escalate to table.
> But if you want to ensure it never escalates you can add a dummy row and
> have a connection lock it all the time. As long as there is another lock
> in the table at any level you can not escalate to a table lock. Of coarse
> that means if you do scans you might have an issue but if you have the
> right indexes and such it shouldn't be an issue.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
>

how to implement locking

Take for example ticketmaster where they lock a certain section, row and
even seat numbers for a duration..
I am assuming all this info may be in one table . How can one ensure row
level locking without SQL Server escalating it to some high level lock.
And even if a particular row is locked, does that mean that one can update
rows that are not locked
Trying to find a solution to do locking at a row level while still leaving
other rows for DML i.e select, updates,inserts,deletes
ThanksOn Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
wrote:
>Take for example ticketmaster where they lock a certain section, row and
>even seat numbers for a duration..
>I am assuming all this info may be in one table . How can one ensure row
>level locking without SQL Server escalating it to some high level lock.
>And even if a particular row is locked, does that mean that one can update
>rows that are not locked
>Trying to find a solution to do locking at a row level while still leaving
>other rows for DML i.e select, updates,inserts,deletes
You can always add an IsLocked field to the table, SQLServer will
never escalate that, and it won't block other operations, and will
even still be there if you reboot the server!
J.|||I think we may get into design here. How will I model the data ? Take for
example Ticketmaster.
And consider high concurrency needed and not being able to block one
another.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:me34h1lcijtg0h301o85u0cj84ds2dbrfa@.4ax.com...
> On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
> wrote:
>>Take for example ticketmaster where they lock a certain section, row and
>>even seat numbers for a duration..
>>I am assuming all this info may be in one table . How can one ensure row
>>level locking without SQL Server escalating it to some high level lock.
>>And even if a particular row is locked, does that mean that one can update
>>rows that are not locked
>>Trying to find a solution to do locking at a row level while still leaving
>>other rows for DML i.e select, updates,inserts,deletes
> You can always add an IsLocked field to the table, SQLServer will
> never escalate that, and it won't block other operations, and will
> even still be there if you reboot the server!
> J.
>|||If they have the proper indexes and WHERE clauses they will have to lock
lots of rows in the single transaction before it will escalate to table.
But if you want to ensure it never escalates you can add a dummy row and
have a connection lock it all the time. As long as there is another lock in
the table at any level you can not escalate to a table lock. Of coarse that
means if you do scans you might have an issue but if you have the right
indexes and such it shouldn't be an issue.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Take for example ticketmaster where they lock a certain section, row and
> even seat numbers for a duration..
> I am assuming all this info may be in one table . How can one ensure row
> level locking without SQL Server escalating it to some high level lock.
> And even if a particular row is locked, does that mean that one can update
> rows that are not locked
> Trying to find a solution to do locking at a row level while still leaving
> other rows for DML i.e select, updates,inserts,deletes
> Thanks
>|||Locking a dummy row is a clever way of avoiding lock escalation on a
particular table. However, please do not overuse this. Having long running
open transaction is not desirable. It should only be used as a workaround
for heavy contention issues as a result of lock escalation. Disabling lock
escalation in general may result in slower performance.
By default, SQL Server does not escalate unless more than 5000 locks are
obtained in the current statement for a particular table. If the query plan
does not contain a range scan, then most likely you will not encounter
escalation. If it does have a range scan, then you could analyze if that
range scan has a chance of encountering a lot of rows.
If you do need to implement the dummy row locking idea, be sure not to do
anything else in the transaction which holds the long term lock. So it
should be something like this:
-- if you do not want escalated X table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table where PrimyarKeyColumn = dummy_row_key_value
wait for delay ...
commit
-- if you do not want escalated S table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table with (UPDLOCK) where PrimyarKeyColumn =dummy_row_key_value
wait for delay ...
commit
If you are planning to use SQL Server 2005, then the new
Read-Committed-Snapshot-Isolation feature guarantees that there is no S
table lock for reads.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ON%23waYCrFHA.3640@.tk2msftngp13.phx.gbl...
> If they have the proper indexes and WHERE clauses they will have to lock
> lots of rows in the single transaction before it will escalate to table.
> But if you want to ensure it never escalates you can add a dummy row and
> have a connection lock it all the time. As long as there is another lock
> in the table at any level you can not escalate to a table lock. Of coarse
> that means if you do scans you might have an issue but if you have the
> right indexes and such it shouldn't be an issue.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Take for example ticketmaster where they lock a certain section, row and
>> even seat numbers for a duration..
>> I am assuming all this info may be in one table . How can one ensure row
>> level locking without SQL Server escalating it to some high level lock.
>> And even if a particular row is locked, does that mean that one can
>> update rows that are not locked
>> Trying to find a solution to do locking at a row level while still
>> leaving other rows for DML i.e select, updates,inserts,deletes
>> Thanks
>

How to identify which all SPs are accessing a given table ?

I don't know if I am in the right section of the forum. Please help me with this :

Is there any system stored procedure or any other method to identify the list of all stored procedures that are using a particular table in my database.

Thanks

Prasad P

Hi,

the only reliable method to get the information is to search the Information Schemas for the information, there is an article about sp_depends which sounds like you would get reliable information from it, but you don′t. I didn′t check the behaviour in SQL2k5, but check this article to get deeper information:

http://b.wunder.home.comcast.net/16509.htm

HTH, Jens Suessmeyer.

|||Check out my blog site:

http://blogs.claritycon.com/blogs/the_englishman/archive/2006/02/09/197.aspx

After having the same problem, I wrote a blog containing a stored procedure which searches stored procedures for a text string using the syscomments system table (which stores the stored proc definitions). If you pass the table name into this procedure, it should get you in the correct direction.

Let me know if that solves your problem, or whether you need more assistance.

HTH|||? No, there's nothing built in. A quick and dirty way of figuring it out is: SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TEXT LIKE '%YourTableName%' This does have some issues due to especially large routines and routines that make use of dynamic SQL and concatenate names, but it may give you some indication. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Prasad Peesapati@.discussions..microsoft.com> wrote in message news:bdc2238e-c590-47ff-927a-5660b2f552db@.discussions.microsoft.com... I don't know if I am in the right section of the forum. Please help me with this : Is there any system stored procedure or any other method to identify the list of all stored procedures that are using a particular table in my database. Thanks Prasad P