hide system tables: February 2012

Friday, February 24, 2012

How to implement Application Roles for an application?

I want to know how to implement Application Roles for an application which
uses mutliple databases. Based on what I have read, Application Roles are
tied to one database only and accessing other databases requires adding gues
t
user account to other databases. For example, if an application is using on
e
database to contain access rights data to its functions and another database
for actual transaction data, how can I implement Application Role?
Thanks.Your understanding is correct; once an application role is activated, other
databases can be accessed only via the guest user security context.
If you don't want to grant permissions to guest or public in the other
databases, consider creating referencing views or procs in your application
role database and enabling cross-database chaining in the databases
involved. As long as the objects involved have the same owner, permissions
are not needed on indirectly referenced objects. Note that the databases
also need to be owned by the same login in order to maintain an unbroken
ownership chain for dbo-owned objects.
You should enable cross-database chaining only if you fully understand the
security implications. See the Books Online
<instsql.chm::/in_runsetup_1cj5.htm> for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:36AC8304-5158-4335-B1C4-B503F51E3F43@.microsoft.com...
>I want to know how to implement Application Roles for an application which
> uses mutliple databases. Based on what I have read, Application Roles are
> tied to one database only and accessing other databases requires adding
> guest
> user account to other databases. For example, if an application is using
> one
> database to contain access rights data to its functions and another
> database
> for actual transaction data, how can I implement Application Role?
>
> Thanks.|||Hi Dan,
Can you elaborate little bit more about referencing views and procedure?
My understanding of Application role:
1. It is connection-based (session-based).
2. It needs to be activated by using sp_setapprole.
3. By default, application role has no permission on the databases. So,
permissions need to be assigned to the role.
I'm trying to develop an application which can connect to SQL Server using
either SQL Authentication or Windows Authentication through ODBC. The
administrator(s) of the application can configure whether the databases
created by the application can be accessed by other applications (this is wh
y
I'm looking at application role). The application is using multiple
databases per connection/session.
Thanks,
Peter
"Dan Guzman" wrote:

> Your understanding is correct; once an application role is activated, othe
r
> databases can be accessed only via the guest user security context.
> If you don't want to grant permissions to guest or public in the other
> databases, consider creating referencing views or procs in your applicatio
n
> role database and enabling cross-database chaining in the databases
> involved. As long as the objects involved have the same owner, permission
s
> are not needed on indirectly referenced objects. Note that the databases
> also need to be owned by the same login in order to maintain an unbroken
> ownership chain for dbo-owned objects.
> You should enable cross-database chaining only if you fully understand the
> security implications. See the Books Online
> <instsql.chm::/in_runsetup_1cj5.htm> for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:36AC8304-5158-4335-B1C4-B503F51E3F43@.microsoft.com...
>
>|||> Can you elaborate little bit more about referencing views and procedure?
You can create views and/or procs in your application role database that
reference those objects in other databases needed by your application. For
example:
USE MyAppRoleDatabase
GO
CREATE VIEW dbo.MyView
AS
SELECT SomeData
FROM MyOtherDatabase.dbo.MyTable
GO
GRANT ALL ON dbo.MyView TO MyAppRole
GO
As long as MyAppRoleDatabase and MyOtherDatabase are owned by the same
login, the ownership chain for dbo-owned objects is unbroken. This allows
the application role to access MyTable data via MyView even without
permissions the underlying table. As mentioned earlier in this thread, the
guest user needs to be enabled in MyOtherDatabase in order to establish a
security context for the application role.

> 3. By default, application role has no permission on the databases. So,
> permissions need to be assigned to the role.
This is true but note that permissions are needed only on the objects
*directly* referenced by the role. Permissions are not checked on
indirectly referenced objects as long as the ownership chain is unbroken.

> I'm trying to develop an application which can connect to SQL Server using
> either SQL Authentication or Windows Authentication through ODBC.
Application roles can be used with either authentication method.

> The administrator(s) of the application can configure whether the
> databases
> created by the application can be accessed by other applications (this is
> why
> I'm looking at application role).
Are you saying that you have multiple applications and application roles?
In this case, each application role database would need referencing objects
as described above.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:6362673A-F318-429B-946A-FC66BFA2D490@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> Can you elaborate little bit more about referencing views and procedure?
> My understanding of Application role:
> 1. It is connection-based (session-based).
> 2. It needs to be activated by using sp_setapprole.
> 3. By default, application role has no permission on the databases. So,
> permissions need to be assigned to the role.
> I'm trying to develop an application which can connect to SQL Server using
> either SQL Authentication or Windows Authentication through ODBC. The
> administrator(s) of the application can configure whether the databases
> created by the application can be accessed by other applications (this is
> why
> I'm looking at application role). The application is using multiple
> databases per connection/session.
>
> Thanks,
> Peter
>
> "Dan Guzman" wrote:
>

how to implement a recursive algorithm as iterative?

Hi,
I have to go through some tables, like a network. I have a recursive
algorithm, but I need to do it iteratively.
Basically, there are 2 tables through which I have to go: Items table and
Relationship table.
The tables I have defined are like this:
- Item table, 2 fields: ItemID, ItemWeight
- Relationships table, 3 fields: Item1, Item2, LinkWeight.
Given Item x, I need to get all of the items related to it. It has to go
down through that item's related items if relationship weight is greater tha
n
item weight.
It stops
- when relationship weight is lower than item weight,
- it has already propagated all of the items
I have this algorithm:
I defined 2 global variables:
- PropagatedItems - contains all of the items that have been "visited" and
their related items
- FoundItems: contains the items that were visited but not its related items
Sub GetRelatedItems(ByVal idItem As Integer)
Dim idit As Integer
If ItemIsIncluded(idItem) Then
Set rlinks = CurrentDb().OpenRecordset("Select iditem1, iditem2,
linkweight from linkweight where item1 = " & idItem & " or item2 = " & idIte
m)
While Not rlinks.EOF
If rlinks.iditem1 = idItem Then
idit = rlinks.iditem2
Else
idit = rlinks.iditem1
End If
If rlinks.linkweight < GetItemWeight(idit) Then
IncludeItem (idit)
Else
GetRelatedItems(idit)
End If
rlinks.MoveNext
Wend
End If
End Sub
Sub Main()
GetRelatedItems(ItemID)
End sub
The problem I have is volume: this database could have a million or more
items.
That's why I can't go through this tables with a recursive algorithm.
Can you help me? How could this be done in a better way?See responses inline:
amota wrote:
> Hi,
> I have to go through some tables, like a network. I have a recursive
> algorithm, but I need to do it iteratively.
> Basically, there are 2 tables through which I have to go: Items table
> and Relationship table.
> The tables I have defined are like this:
> - Item table, 2 fields: ItemID, ItemWeight
> - Relationships table, 3 fields: Item1, Item2, LinkWeight.
> Given Item x, I need to get all of the items related to it. It has to
> go
> down through that item's related items if relationship weight is
> greater than item weight.
> It stops
> - when relationship weight is lower than item weight,
> - it has already propagated all of the items
> I have this algorithm:
> I defined 2 global variables:
> - PropagatedItems - contains all of the items that have been
> "visited" and their related items
> - FoundItems: contains the items that were visited but not its
> related items
> Sub GetRelatedItems(ByVal idItem As Integer)
> Dim idit As Integer
> If ItemIsIncluded(idItem) Then
> Set rlinks = CurrentDb().OpenRecordset("Select iditem1, iditem2,
> linkweight from linkweight where item1 = " & idItem & " or item2 = "
> & idItem)
I'm not clear why you are asking an Access question (this is DAO code) in a
SQL Server newsgroup. You run the risk of getting responses that will work
in SQL Server but not in Access. Your better plan would have been to ask in
an Access newsgroup such as microsoft.public.access.queries.

> While Not rlinks.EOF
> If rlinks.iditem1 = idItem Then
> idit = rlinks.iditem2
> Else
> idit = rlinks.iditem1
> End If
> If rlinks.linkweight < GetItemWeight(idit) Then
> IncludeItem (idit)
> Else
> GetRelatedItems(idit)
> End If
> rlinks.MoveNext
> Wend
> End If
> End Sub
> Sub Main()
> GetRelatedItems(ItemID)
> End sub
> The problem I have is volume: this database could have a million or
> more items.
> That's why I can't go through this tables with a recursive algorithm.
> Can you help me? How could this be done in a better way?
How about this:
Select i.ItemID,r1.Item2, r2.Item1
from (Items i inner join linkweight r1 on i.ItemID = r1.Item1)
inner join linkweight r2 on i.ItemId = r2.Item2
Where r1.linkweight > ItemWeight and
r2.linkweight > ItemWeight
order by ItemID
If that does not give you what you want, perhaps you need a union query:
Select i.ItemID,r1.Item2 RelatedItem
from Items i inner join linkweight r1 on i.ItemID = r1.Item1
Where r1.linkweight > ItemWeight
Union
Select i.ItemID,r1.Item1
from Items i inner join linkweight r2 on i.ItemID = r2.Item1
Where r2.linkweight > ItemWeight
order by ItemID
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||"amota" <amota@.discussions.microsoft.com> wrote in message
news:25EB9702-0A72-4961-B115-856788E503D1@.microsoft.com...
> Hi,
> I have to go through some tables, like a network. I have a recursive
> algorithm, but I need to do it iteratively.
>
<snip>
amota,
Please repost your message to the following newsgroup:
microsoft.pulic.access.modulesdaovba
Sincerely,
Chris O.|||I'm just using access as a demo, but I plan to do it with SQL Server.
That's why I posted here in this group.
--
amota
"Chris2" escribió:

> "amota" <amota@.discussions.microsoft.com> wrote in message
> news:25EB9702-0A72-4961-B115-856788E503D1@.microsoft.com...
> <snip>
> amota,
> Please repost your message to the following newsgroup:
> microsoft.pulic.access.modulesdaovba
>
> Sincerely,
> Chris O.
>
>|||So did you try my suggestions ... ?
amota wrote:
> I'm just using access as a demo, but I plan to do it with SQL Server.
> That's why I posted here in this group.
>
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||hmm, no, didnt work.
First one, gives me an empty record set
Second one, doesn't give a complete set.
Also, I asked here because I think it's better to do it as a stored procedur
e.
amota
"Bob Barrows [MVP]" escribió:

> So did you try my suggestions ... ?
> amota wrote:
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||example:
ITEMS TABLE:
ItemID ItemWeight
10 -1
11 0.3
12 1
13 -0.2
Relationships TABLE:
Item1 Item2 linkweight
11 10 0.1
12 10 0.42
12 11 0.5
13 10 0.33
13 12 -0.33
For Item 13:
Correct set: 13, 10, 11, 12
Notice that direct related are 13 and 10
Items related to 10 are 11 and 12
Your first suggestion, gives empty,
the second one, gives 2 records: 13,10 and 13,13
--
amota
"Bob Barrows [MVP]" escribió:

> So did you try my suggestions ... ?
> amota wrote:
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||Look at this example (in T-SQL):
http://milambda.blogspot.com/2005/0...or-monkeys.html
A good hyerarchy begins with a model that prevents circular referencing.
ML|||Again, the syntactical differences between JetSQL and Transact-SQL (SQL
Server's variant of SQL) may prevent suggestions made here from being usable
in your Access prototype. You should consider prototyping with MSDE.
Whether or not a stored procedure is used is somewhat irrelevant to the
question about what is the best sql to use for your task.
Your followup message provides some sample data so i will reply to that one.
amota wrote:
> hmm, no, didnt work.
> First one, gives me an empty record set
> Second one, doesn't give a complete set.
> Also, I asked here because I think it's better to do it as a stored
> procedure.
> --
> amota
>
> "Bob Barrows [MVP]" escribi:
>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||amota wrote:
> example:
> ITEMS TABLE:
> ItemID ItemWeight
> 10 -1
> 11 0.3
> 12 1
> 13 -0.2
> Relationships TABLE:
> Item1 Item2 linkweight
> 11 10 0.1
> 12 10 0.42
> 12 11 0.5
> 13 10 0.33
> 13 12 -0.33
> For Item 13:
> Correct set: 13, 10, 11, 12
> Notice that direct related are 13 and 10
> Items related to 10 are 11 and 12
> Your first suggestion, gives empty,
> the second one, gives 2 records: 13,10 and 13,13
Ah! You have a hierarchy. ML gave one solution. For another solution, read
these articles about using nested sets:
http://www.mvps.org/access/queries/qry0023.htm
http://www.dbmsmag.com/9603d06.html
http://www.intelligententerprise.com/001020/celko.shtml
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

How to implement a foreign key

Hello,

I am facing a problem, I have a table and the primary key of this table is a foreign key of another table. I want to insert data into the mother table, and at the same time i want to insert data into the child table depending on the last inserted row of the mother table. So I need 2 insert commands to run. But I think data inconsistency may happened. When i insert into the second table, then the mother table may be updated. Primary key of the mother table is declared as identity column. Can anyone suggest me how i can insert into those two tables.I think you should use stored procedure which do some operations like this:

1) insert to mother table
2) use @.@.IDENTITY to get identity value last inserted row
3) insert to the second table

How to implement a DropDownList w/o a Table

I am using Visual Studio 2005 & SQL Server. How do i implement a DDL for users to select which value to input. like i can with Access. i do not need a table i think. if not the table would have only ID & Value.?

If you want a static set of items in the dropdown list you can define them in the property panel under the property Items you will find a collection, click the "..." and add items with value and text to appear in the dropdown list.

Best regards,
Per Salmi

|||

If you're trying to get the values from the database, you can do something like this:

Sub Page_Load(ByVal senderAs Object,ByVal eAs EventArgs)If Not Page.IsPostBackThen GetData()End If
 End Sub
 Sub GetData()
Dim connStringAs String
 Dim conAs SqlConnection
Try connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString
 con =New SqlConnection(connString)
Dim cmdAs SqlCommand =New SqlCommand()
Dim readerAs SqlDataReader
 cmd.Connection = con
 cmd.CommandText ="EXECUTE dbo.GetDepartmentCode" reader = cmd.ExecuteReaderIf reader.HasRowsThen
 While (reader.Read)
Dim newItemAs New ListItem
 newItem.Text = (reader.Item("DeptCode")).ToString
 newItem.Value = (reader.Item("DeptCode")).ToString
 deptCodeDDL.Items.Add(newItem)
End While
 End If reader.Close()Catch exAs Exception
 Response.Write(ex)
Finally con.Close() con.Dispose()End Try
 Catch exAs ApplicationException
 Response.Write("Could not load the database")
End Try
 End Sub

The ASP.Net side would like this:

<asp:DropDownList ID="deptCodeDDL" runat="server"></asp:DropDownList>

A few things to note though.

In the code I've posted, I use a stored procedure (sproc) to retrieve the data from the database. If you don't what sprocs are, you can read about themhere. Using sprocs is generally considered a better way of accessing the db.

Also, you will of course, have to replace DeptCode with the column name from where you will be getting the values.

Finally, I use a ConnectionString, which is being retrieved from the Web.config file. You should have that in you application folder.

If you don't, given below is a sample:

<!--
 Note: As an alternative to hand editing this file you can use the
 web admin tool to configure settings for your application. Use
 the Website->Asp.Net Configuration option in Visual Studio.
 A full list of settings and comments can be found in
 machine.config.comments usually located in
 \Windows\Microsoft.Net\Framework\v2.x\Config
--><configuration>
<appSettings/>
<connectionStrings>
<add name="ConnectionString1" connectionString="Data Source=SERVERNAME\SQLSERVER;Initial Catalog=DBNAME;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<!--
 Set compilation debug="true" to insert debugging
 symbols into the compiled page. Because this
 affects performance, set this value to true only
 during development.
 --><compilation debug="false"
</compilation>
<!--
 The <authentication> section enables configuration
 of the security authentication mode used by
 ASP.NET to identify an incoming user.
 --><authentication mode="Windows"/>
<!--
 The <customErrors> section enables configuration
 of what to do if/when an unhandled error occurs
 during the execution of a request. Specifically,
 it enables developers to configure html error pages
 to be displayed in place of a error stack trace. <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
 <error statusCode="403" redirect="NoAccess.htm" />
 <error statusCode="404" redirect="FileNotFound.htm" />
 </customErrors>
 --></system.web>
</configuration>

You will have to replace SERVERNAME with you server name and DBNAME with your db name.

How to implement a data base design for multi language support?

Hi,
I have searched the web for several hours now to find a reference solution
based on the available features of Sql Server 2005 to implement a data base
with multi language support.
I would like to create a database that is able to store multiple
translations (text in different languages) for the same original value.
--> tblProducts, Field ProductDescription --> One field, manny languages
If I query this table/field with a current UI-Culture from
ASP.net/WindowsForms it should be 'intelligent' and return the value based on
the UI-Culture. It should work like the culture stuff in the GUI...
I am lookig for a solution, where I can store all the language relevant
informations in the same field. Do I have to use an XML-field or a user
defined field? Is there something like a "best practise" for this kind of
database design available?
I would like to avoid a 1 to many relation for all culture relevant fields
in my database...
Thanks for any hint,
DirkAt previous company i worked at they had multi language support and
they used what was like a codes table. w/ a language type ID
associated w/ all the words on the various pages. depending on the
language type code it would pull in the words to the page. so they
just stored 10 differenet sets of language codes in the db. they
brought it in fresh everytime w/ a select statement.|||Hi Thomas,
thanks for your reply. I'm sure this approch works but I would like to make
it easier. With your solution I have to join the tables every time I need a
lagnuage relevant value.
I was thinking about using the Sql Server 2005 FieldType 'xml' and store a
xml strcture in every relevant field like
<values>
<value culture='en-us'>
car
</value>
<value culture='de-de'>
Auto
</value>
</values>
I just want to hear some solution from others just to make it no so
complicated...
Thanx,
Dirk

How to implement a data base design for multi language support?

Hi,
I have searched the web for several hours now to find a reference solution
based on the available features of Sql Server 2005 to implement a data base
with multi language support.
I would like to create a database that is able to store multiple
translations (text in different languages) for the same original value.
--> tblProducts, Field ProductDescription --> One field, manny languages
If I query this table/field with a current UI-Culture from
ASP.net/WindowsForms it should be 'intelligent' and return the value based o
n
the UI-Culture. It should work like the culture stuff in the GUI...
I am lookig for a solution, where I can store all the language relevant
informations in the same field. Do I have to use an XML-field or a user
defined field? Is there something like a "best practise" for this kind of
database design available?
I would like to avoid a 1 to many relation for all culture relevant fields
in my database...
Thanks for any hint,
DirkAt previous company i worked at they had multi language support and
they used what was like a codes table. w/ a language type ID
associated w/ all the words on the various pages. depending on the
language type code it would pull in the words to the page. so they
just stored 10 differenet sets of language codes in the db. they
brought it in fresh everytime w/ a select statement.

How to implement a data base design for multi language support?

Hi,
I have searched the web for several hours now to find a reference solution
based on the available features of Sql Server 2005 to implement a data base
with multi language support.
I would like to create a database that is able to store multiple
translations (text in different languages) for the same original value.
--> tblProducts, Field ProductDescription --> One field, manny languages
If I query this table/field with a current UI-Culture from
ASP.net/WindowsForms it should be 'intelligent' and return the value based on
the UI-Culture. It should work like the culture stuff in the GUI...
I am lookig for a solution, where I can store all the language relevant
informations in the same field. Do I have to use an XML-field or a user
defined field? Is there something like a "best practise" for this kind of
database design available?
I would like to avoid a 1 to many relation for all culture relevant fields
in my database...
Thanks for any hint,
Dirk
At previous company i worked at they had multi language support and
they used what was like a codes table. w/ a language type ID
associated w/ all the words on the various pages. depending on the
language type code it would pull in the words to the page. so they
just stored 10 differenet sets of language codes in the db. they
brought it in fresh everytime w/ a select statement.

How to implement a custom Order Column?

Hi,
the table look like this:
id, area, areaorder, areatext
1, group1, 1, Group1Text A
2, group1, 2, Group1Text B
3, group2, 1, Group2Text A
4, group2, 2, Group2Text B
We need to be able to keep the order in each group, but also the ability to
insert and update within a group
eg. "INSERT group1, 2, Group1Text NEW" so that it will look like this:
1, group1, 1, Group1Text A
5, group1, 2, Group1Text NEW
2, group1, 3, Group1Text B
The same if we UPDATE "UPDATE SET AreaOrder=1 WHERE ID = 2" (all Order IDs
need to be adjusted to make space I guess).
What would be a good way to go about doing this?
Thanks a lot
PatrickPatrick,see if it helps you
CREATE TABLE #Test
(
col1 INT NOT NULL PRIMARY KEY,
col2 VARCHAR(20),
col3 INT NOT NULL,
col4 VARCHAR(20)
)
INSERT INTO #Test VALUES (1,'group1', 1, 'Group1Text A')
INSERT INTO #Test VALUES (2,'group1', 2, 'Group1Text B')
INSERT INTO #Test VALUES (3,'group2', 1, 'Group1Text A')
INSERT INTO #Test VALUES (4,'group2', 2, 'Group1Text B')
INSERT INTO #Test VALUES (5,'group1', 3, 'Group1Text C')
SELECT * FROM #Test ORDER BY col2
DROP TABLE #Test
"Patrick Wolf" <ppjwolf@.bigfoot.com> wrote in message
news:%23W1JA%23lSFHA.1268@.TK2MSFTNGP14.phx.gbl...
> Hi,
> the table look like this:
> id, area, areaorder, areatext
> 1, group1, 1, Group1Text A
> 2, group1, 2, Group1Text B
> 3, group2, 1, Group2Text A
> 4, group2, 2, Group2Text B
> We need to be able to keep the order in each group, but also the ability
to
> insert and update within a group
> eg. "INSERT group1, 2, Group1Text NEW" so that it will look like this:
> 1, group1, 1, Group1Text A
> 5, group1, 2, Group1Text NEW
> 2, group1, 3, Group1Text B
> The same if we UPDATE "UPDATE SET AreaOrder=1 WHERE ID = 2" (all Order IDs
> need to be adjusted to make space I guess).
> What would be a good way to go about doing this?
> Thanks a lot
> Patrick
>|||The best way to implement this would be a trigger.
Something along the lines of:
CREATE TRIGGER trgi_i_sometable ON some_table INSTEAD OF INSERT
AS
UPDATE sometable s
SET areaorder = areaorder + (SELECT COUNT(*) FROM inserted i
WHERE i.area = s.area AND i.areaorder <= s.areaorder)
INSERT INTO sometable (id, area, areaorder, areatext)
SELECT id, area, areaorder, areatext FROM inserted
CREATE TRIGGER trgi_iu_sometable ON some_table AFTER DELETE
AS
UPDATE sometable s
SET areaorder = areaorder - (SELECT COUNT(*) FROM deleted d
WHERE d.area = s.area AND d.areaorder < s.areaorder)
You can also do this with updates, but that's is going to be a little less
trivial, as you have to take into account rows that swap etc. It would
probably be the easiest if you replaced the one update with a delete and
insert:
CREATE TRIGGER trgi_u_sometable ON some_table INSTEAD OF UPDATE
AS
DELETE s
FROM sometable s
INNER JOIN deleted d
OM s.id = d.id
INSERT INTO sometable (id, area, areaorder, areatext)
SELECT id, area, areaorder, areatext FROM inserted
(You have to test that, because I am not 100 % sure if an instead of trigger
will fire another instead of trigger.)
Jacco Schalkwijk
SQL Server MVP
"Patrick Wolf" <ppjwolf@.bigfoot.com> wrote in message
news:%23W1JA%23lSFHA.1268@.TK2MSFTNGP14.phx.gbl...
> Hi,
> the table look like this:
> id, area, areaorder, areatext
> 1, group1, 1, Group1Text A
> 2, group1, 2, Group1Text B
> 3, group2, 1, Group2Text A
> 4, group2, 2, Group2Text B
> We need to be able to keep the order in each group, but also the ability
> to insert and update within a group
> eg. "INSERT group1, 2, Group1Text NEW" so that it will look like this:
> 1, group1, 1, Group1Text A
> 5, group1, 2, Group1Text NEW
> 2, group1, 3, Group1Text B
> The same if we UPDATE "UPDATE SET AreaOrder=1 WHERE ID = 2" (all Order IDs
> need to be adjusted to make space I guess).
> What would be a good way to go about doing this?
> Thanks a lot
> Patrick
>|||In addition ,I'd create Groups table to be joined with a Group_Text table.
That way you violate 2SF you have a data that not depends on PK.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23BBkvCmSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Patrick,see if it helps you
> CREATE TABLE #Test
> (
> col1 INT NOT NULL PRIMARY KEY,
> col2 VARCHAR(20),
> col3 INT NOT NULL,
> col4 VARCHAR(20)
> )
> INSERT INTO #Test VALUES (1,'group1', 1, 'Group1Text A')
> INSERT INTO #Test VALUES (2,'group1', 2, 'Group1Text B')
> INSERT INTO #Test VALUES (3,'group2', 1, 'Group1Text A')
> INSERT INTO #Test VALUES (4,'group2', 2, 'Group1Text B')
> INSERT INTO #Test VALUES (5,'group1', 3, 'Group1Text C')
> SELECT * FROM #Test ORDER BY col2
> DROP TABLE #Test
>
> "Patrick Wolf" <ppjwolf@.bigfoot.com> wrote in message
> news:%23W1JA%23lSFHA.1268@.TK2MSFTNGP14.phx.gbl...
> to
IDs
>|||Thanks very much for your input :)
It seems to work (I programmed another trigger for instead of update).
I have trouble though understanding how this works?
UPDATE sometable s
> SET areaorder = areaorder + (SELECT COUNT(*) FROM inserted i
> WHERE i.area = s.area AND i.areaorder <= s.areaorder)
How is this different from this?
DECLARE @.targetOrder as smallint,@.targetArea as int
SELECT @.targetOrder = areaOrder, @.targetArea=area FROM inserted
UPDATE sometable s SET areaorder = areaorder + 1 WHERE areaorder >=
targetOrder and area = targetArea
Thanks and all the best
Patrick|||My statement works when multiple rows are inserted, while your statement
only works for one row. An insert/delete/update statement can affect
multiple rows, but will only fire the trigger once for the statement, not
once for every row. So you have to write trigger so that they can handle
multiple rows, unless you put in some specific code that rolls back
statements that affect multiple rows, like:
CREATE TRIGGER ....
AS
IF @.@.ROWCOUNT > 1
BEGIN
RAISERROR ('No multirow updates allowed!, 16,1)
ROLLBACK TRAN
RETURN
END
... rest of trigger
but you only need that in rare cases. In most cases you can write the
trigger to handle multirow inserts/updates/deletes.
Jacco Schalkwijk
SQL Server MVP
"Patrick Wolf" <ppjwolf@.bigfoot.com> wrote in message
news:O23HD$uSFHA.1896@.TK2MSFTNGP14.phx.gbl...
> Thanks very much for your input :)
> It seems to work (I programmed another trigger for instead of update).
> I have trouble though understanding how this works?
> UPDATE sometable s
> How is this different from this?
> DECLARE @.targetOrder as smallint,@.targetArea as int
> SELECT @.targetOrder = areaOrder, @.targetArea=area FROM inserted
> UPDATE sometable s SET areaorder = areaorder + 1 WHERE areaorder >=
> targetOrder and area = targetArea
> Thanks and all the best
> Patrick
>|||Thanks very much for your answer.
Since I feel that you got a good point here :) I still have one challenge.
I would like to write these statments myself and I dont totaly understand
how yours works:
Would you mind pointing out how the COUNT(*) works here?

> UPDATE sometable s
Thanks a lot
Patrick
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:u17O7bxSFHA.2812@.TK2MSFTNGP09.phx.gbl...
> My statement works when multiple rows are inserted, while your statement
> only works for one row. An insert/delete/update statement can affect
> multiple rows, but will only fire the trigger once for the statement, not
> once for every row. So you have to write trigger so that they can handle
> multiple rows, unless you put in some specific code that rolls back
> statements that affect multiple rows, like:
> CREATE TRIGGER ....
> AS
> IF @.@.ROWCOUNT > 1
> BEGIN
> RAISERROR ('No multirow updates allowed!, 16,1)
> ROLLBACK TRAN
> RETURN
> END
> ... rest of trigger
> but you only need that in rare cases. In most cases you can write the
> trigger to handle multirow inserts/updates/deletes.
> --
> Jacco Schalkwijk
> SQL Server MVP
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Since there is no such thing in the relational as a magical universal
id, can I assume that you want this column for display purposes, in
violation of the basic principle of a tiered archtiecture? Is the real
key (area, area_order)?
If you keep designing things like this, you will wind up with triggers
(procedural code!!) and Normal Form problems that will destroy your
data integrity.|||Hi,
yes you are right area, areaorder are the real ids. In the real application
area is an int and has foreign key constraint to the parent table which
defines the areas. I just did the sample for simplicity.
The grid I work with has trouble with combined primary keys so it seemed
easier to just use an id column even though it has not much meaning.
Thanks for your suggestions
Patrick
----
---
CREATE TABLE Test3
(
id INT NOT NULL PRIMARY KEY IDENTITY (1,1),
area VARCHAR(10) NOT NULL,
areaorder INT NOT NULL,
areatext VARCHAR(20) NOT NULL
)
INSERT INTO test3 VALUES ('group1', 1, 'Group1Text A')
INSERT INTO Test3 VALUES ('group1', 2, 'Group1Text B')
INSERT INTO Test3 VALUES ('group2', 1, 'Group1Text A')
INSERT INTO Test3 VALUES ('group2', 2, 'Group1Text B')
INSERT INTO Test3 VALUES ('group1', 3, 'Group1Text C')
----
---
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1114612727.507792.92340@.l41g2000cwc.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Sample data is also a good idea, along with clear
> specifications.
> Since there is no such thing in the relational as a magical universal
> id, can I assume that you want this column for display purposes, in
> violation of the basic principle of a tiered archtiecture? Is the real
> key (area, area_order)?
> If you keep designing things like this, you will wind up with triggers
> (procedural code!!) and Normal Form problems that will destroy your
> data integrity.
>

How to implement a change log for SQL database?

I need a log showing all changes in a few tables in a database to be able to show who has changed what and when (without this the customer is always claiming the database changes itself magically all by itself all the time *sigh*)

Is there a best practise way to do this? I'm not even sure if I change the SQL doing every insert/update/delete or if I rather install some triggers to track changes.

Thanks,
Sam

Triggers are the most common method for populating audit tables.

|||As mentioned, the most common way is by using Triggers.

If you're just looking for a quick and easy audit solution, I can recommend this script by Nigel Rivett.

You could also use the log file and something like Log Navigator to see who performed which changes.
|||

To expand on the 'Trigger' suggestions:

Create a 'Audit' table, exact same schema as the production table -

EXCEPT, remove all CONSTRAINTs, IDENTITY properties, and PRIMARY KEY constraints.

ADD a column for ChangeDate datetime DEFAULT getdate()

ADD a column for ChangeUser varchar(50) DEFAULT system_user

ADD a column for ActionType char(1) (Values will be 'U' or 'D'.)

Create a TRIGGER on the production table for UPDATE, DELETE

How to implement "AND NOT IN" in a many-to-many relationship dimension?

Hello,

I have 2 tables, reason and complain. 1 complain can have multiple reasons and a reason can be linked to multiple complains.

Reason Complain_ID

A 1

A 2

B 1

B 3

C 1

D 2

In the analysis Services, I have a Reason dimension, and when I browse for the number of complains per reason, the browser shows:

Reason Complain Count

A 2

B 2

C 1

D 1

But, how can I query the Analysis Service to count only complains for Reason B, C, D, BUT NOT count complains that includes Reason A... Something like "... AND NOT IN...." that we can do in SQL.

The result I'm expecting is something like the following:

Reason Complaini Count

B 1 (only complain_id 3 does not include Reason A)

Thank you very much,

Sincerely,

Annie

I found the document "The Many-to-Many Revolution" by Marco Russo, and it solved my problem.

Thanks everyone~~

How to implement "AND NOT IN" in a many-to-many relationship dimension?

Hello,

I have 2 tables, reason and complain. 1 complain can have multiple reasons and a reason can be linked to multiple complains.

Reason Complain_ID

A 1

A 2

B 1

B 3

C 1

D 2

In the analysis Services, I have a Reason dimension, and when I browse for the number of complains per reason, the browser shows:

Reason Complain Count

A 2

B 2

C 1

D 1

But, how can I query the Analysis Service to count only complains for Reason B, C, D, BUT NOT count complains that includes Reason A... Something like "... AND NOT IN...." that we can do in SQL.

The result I'm expecting is something like the following:

Reason Complaini Count

B 1 (only complain_id 3 does not include Reason A)

Thank you very much,

Sincerely,

Annie

I found the document "The Many-to-Many Revolution" by Marco Russo, and it solved my problem.

Thanks everyone~~

How to impersonate user for data on another server?

Hello all,

I have a quick question. The sql reporting server is on server A, and the actual data for the report is on a sql server 2005 called server B. When I am actually developing my report and click preview, everything works perfectly using windows authentication. However, whenever I publish and try to view the report, i get this error:

An error has occurred during report processing.

Cannot create a connection to data source 'serverB'.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.How do I change the login for this datasource while still using windows authentication and not mixed mode?

ThanksGo to the data source properties in the Reporting Services Manager and you can put in a Windows or SQL Server user name and password for the data source.

How to impersonate the user and send those credentials?

I want to create only one user on the domain, and my application should send that user and password to the reportviewer, in that way I wont have to give permissions to every user individually .

Does anyone have the lines of code to do this?

Check the WindowsIdentity class (particularly WindowsIdentity.Impersonate): http://msdn2.microsoft.com/en-us/library/system.security.principal.windowsidentity(VS.80).aspx

-- Robert

How to impersonate the user and send those credentials?

I want to create only one user on the domain, and my application should send that user and password to the reportviewer, in that way I wont have to give permissions to every user individually .

Does anyone have the lines of code to do this?

Try one of these articles:

http://www.odetocode.com/Articles/215.aspx

http://www.odetocode.com/Articles/216.aspx|||Hey I STRUGGLED with this for some time and from grabbing other snippets of code and doing some of my own troubleshooting this is what I came up with and it works for me. Please note that in the second line of code you will need the IMPORTS of YOUR web service. I am using Webhost4life as my provider and this code works for me so I hope it helps someone out there to get started. If anyone has enhancements please let me know.

Imports Microsoft.VisualBasicImports com.mysite4now.sql332reportingImports Microsoft.Reporting.WebFormsImports System.Security.PrincipalPartialClass reportviewerInherits System.Web.UI.PagePublic Class ReportViewerCredentialsImplements IReportServerCredentialsPrivate _userNameAs String Private _passwordAs String Private _domainAs String Public Sub New(ByVal userNameAs String,ByVal passwordAs String,ByVal domainAs String) _userName = userName _password = password _domain = domainEnd Sub Public ReadOnly Property ImpersonationUser()As System.Security.Principal.WindowsIdentityImplements Microsoft.Reporting.WebForms.IReportServerCredentials.ImpersonationUserGet Return Nothing End Get End Property Public ReadOnly Property NetworkCredentials()As System.Net.ICredentialsImplements Microsoft.Reporting.WebForms.IReportServerCredentials.NetworkCredentialsGet Return New Net.NetworkCredential(_userName, _password, _domain)End Get End Property Public Function GetFormsCredentials(ByRef authCookieAs System.Net.Cookie,ByRef userNameAs String,ByRef passwordAs String,ByRef authorityAs String)As Boolean Implements Microsoft.Reporting.WebForms.IReportServerCredentials.GetFormsCredentials userName = _userName password = _password authority = _domainReturn Nothing End Function End Class Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadIf Not IsPostBackThen Dim rptPathAs String rptPath = Request("Path")Dim rsAs New ReportingService ReportViewer1.ServerReport.ReportServerCredentials =New ReportViewerCredentials("username","password","") ReportViewer1.ServerReport.ReportServerUrl =New Uri("yourservername") ReportViewer1.AsyncRendering =False ReportViewer1.ShowDocumentMapButton =True ReportViewer1.DocumentMapCollapsed =False ReportViewer1.ServerReport.ReportPath = rptPathEnd If End SubEnd Class

|||

Hey, can i ask you for this line:
ReportViewer1.ServerReport.ReportServerCredentials = New ReportViewerCredentials("username", "password", "")

How you get the the password variable? if it from your database or from active directory?

Please advice, thank you.

|||

you can put it in, web.config or from a database. don't know abt active directory.

I have worked with this, and we put it in web.config.

i haven't tried for active directory.

How to impersonate known user to AS 2000

I am developing an application in C# 2.0 that will access a AS 2000 cube. I want the application to access the cube with a known account. I do not want to use XMLA.

My ConnectionString looks like this:

Data Source=MyServer; Catalog=GL; User ID=ReportUser; Password=mypass

My code looks like this:

AdomdConnection Connection = new AdomdConnection(ConnectionString);
Connection.Open();

ReportUser is a local account on the server. The cube has a single role where ReportUser is a member.

When trying to open the connection I get an exception that the connection was actively refused by the server.

Help?

Thanks

Sorry - you can't do that unless you set up http authentication and use basic auth for your virtual directory. (please use https)

And that takes Enterprise edition in 2000 (but standard in 2005!).

How to ignore timepart when using LookUp with date.

I want to lookup the datekeys from my datedimension. However it does not work because matching for same dates does not work because of different timeparts. The dates in my lookup-table have a 00:00:00 timepart, but the dates in my input table have a non-zero timepart. How can I ignore the timeparts or make the timeparts zero?

Regards,
HenkI found a workaround:

add a derived column component before and cast to DT_DBDATE first and than back to DT_DBTIMESTAMP to get rid of the timepart: (DT_DBTIMESTAMP)(DT_DBDATE)DateColumn.

I am still interested in a solution that doesn't require an extra component.

Henk|||Can you not use T-SQL to change the data at source?

-Jamie|||Yes that's true, but the sports of SSIS is in doing it without coding T-SQL Smile

How to ignore sp_runwebtask errors?

Hi,
I have some tables in my database that, upon modification, write a file
to disk with data. I have create the triggers that handle this, using
the sp_makewebtask stored procedure.
For these triggers to work, I needed to login from my application with
integrated security. This is not a problem and works fine.
Sometimes I also need to do some maintenance on the tables, and for
this purpose I cannot use integrated security (because I connect
through vpn an am not on the actual machine). When i then change
something on the tables I get an error: "SQL Web Assistant: Could not
open the output file".
The fact that if I'm doing maintenance, the file is not created, is not
a problem, so I hope that one of the following solutions is possible:
- Build error handling into the trigger that simply ignores the error
- Detect in the trigger that the current user is not a windows user,
and then skip the creation of the file
- something else.
What could be a solution for my problem?
BTW We are using SQL 2000 on Windows 2003 Server.
Regards, FelixFelix,

> - Detect in the trigger that the current user is not a windows user,
> and then skip the creation of the file
You can use SUSER_SNAME() to determine this.
IF SUSER_SNAME() = 'MySQLServerLogin' ...
Robert
"felix planjer" <fplanjer@.gmail.com> wrote in message
news:1143541278.508476.286150@.e56g2000cwe.googlegroups.com...
> Hi,
> I have some tables in my database that, upon modification, write a file
> to disk with data. I have create the triggers that handle this, using
> the sp_makewebtask stored procedure.
> For these triggers to work, I needed to login from my application with
> integrated security. This is not a problem and works fine.
> Sometimes I also need to do some maintenance on the tables, and for
> this purpose I cannot use integrated security (because I connect
> through vpn an am not on the actual machine). When i then change
> something on the tables I get an error: "SQL Web Assistant: Could not
> open the output file".
> The fact that if I'm doing maintenance, the file is not created, is not
> a problem, so I hope that one of the following solutions is possible:
> - Build error handling into the trigger that simply ignores the error
> - Detect in the trigger that the current user is not a windows user,
> and then skip the creation of the file
> - something else.
> What could be a solution for my problem?
> BTW We are using SQL 2000 on Windows 2003 Server.
> Regards, Felix
>

How to ignore error and continue trasactional replication

Hi,
I have a trasactional replication setup in SQL 2000 with SP3. Accidentally,
I deleted a row in the suscriber table. When I deleted the same row in the
publisher database, I have error in the replication monitor "the row was not
found at the Subcriber when applying the replicated command".
Please let me know how do I get rid of this error and continue replication.
I don't want to reinitialize the subscriber again because the table is big
and takes lot of time.
I appreciate your help!!
Thanks
Chinna.
gonzo - cowboy- to hell with database consistency approach - right click on
your failed agent, select agent profiles, and then select the continue on
data consistency profile.
more cautious approach. enable logging for your distribution agent, and
restart it. Find out the row which you deleted. Get this row from teh
publisher and manually construst the insert statement and put it back in the
subscriber. Restart your distribution agent.
check out this kb article for more info on how to do the logging.
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chinna Kondaveeti" <Chinna Kondaveeti@.discussions.microsoft.com> wrote in
message news:4A2DEA01-1131-41EF-B432-6C26E923EBFA@.microsoft.com...
> Hi,
> I have a trasactional replication setup in SQL 2000 with SP3.
Accidentally,
> I deleted a row in the suscriber table. When I deleted the same row in the
> publisher database, I have error in the replication monitor "the row was
not
> found at the Subcriber when applying the replicated command".
> Please let me know how do I get rid of this error and continue
replication.
> I don't want to reinitialize the subscriber again because the table is big
> and takes lot of time.
> I appreciate your help!!
> Thanks
> Chinna.
>
|||Thanks to everyone who helped me. It worked!!. I have changed back to default.
I appreciate your help!!
Thanks
Chinna.
"Hilary Cotter" wrote:

> gonzo - cowboy- to hell with database consistency approach - right click on
> your failed agent, select agent profiles, and then select the continue on
> data consistency profile.
> more cautious approach. enable logging for your distribution agent, and
> restart it. Find out the row which you deleted. Get this row from teh
> publisher and manually construst the insert statement and put it back in the
> subscriber. Restart your distribution agent.
> check out this kb article for more info on how to do the logging.
> http://support.microsoft.com/default...&Product=sql2k
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Chinna Kondaveeti" <Chinna Kondaveeti@.discussions.microsoft.com> wrote in
> message news:4A2DEA01-1131-41EF-B432-6C26E923EBFA@.microsoft.com...
> Accidentally,
> not
> replication.
>
>

How to ignore a dimension in MDX?

So my fact table looks like this:

SurveyID ( --> Survey Dimesion)
GroupID( --> Group Dimension)
StatusID ( --> Status Dimension ... e.g. Complete, InProgress, New)
GroupCount (the actual measure)

How would I create a calculated member that would return the "overall" count for a specific group in a survey (e.g. ignore Status dimension) regardless if the user is slicing the data by Status or not?

Thanks again - wgpubs

Try this ("ignoring" the status dimension - your exact naming may differ):

CREATE MEMBER CURRENTCUBE.MyCount AS

'([Status].[StatusID].[All StatusID],[Measures].[GroupCount])';

|||tried this ... but if I include the ResponseStatus dimension in my slicer it still filters on ResponseStatus.

In my slicer I got survey, group and response status ... the kind of report I'm trying to generate in SSRS needs to looks something like this:

Survey Group Count OverallCount

... where Count should use the ResponseStatus dimension (so that users can filter out certain statuses and what not thru a RS parameter) while OverallCount should not.|||My suggestion should work, if I understand you correctly. Please post your exact MDX query.|||Yah I thought it would too ... but no go. here is the query:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Overall Group Count]
AS ([Response Status].[Response Status].[All], [Measures].[Group Count]),
VISIBLE = 1 ;

How to identify which version we have?

How do I tell if I am running SQL2K Enterprise Edition or Standard Edition? We have a 2-node cluster. Does that imply Enterprise Edition, or could Standard do that as well? Thanks for any info.

If it's SQL 2000, only Enterprise is supported in a cluster configuration...if SQL 2005, Standard is also supported.

You can run the following to find your the edition of the server you are running...just connect via Query Analyzer to the instance you want to verify and execute:

select SERVERPROPERTY('Edition')

|||Thanks, that was very helpful. We are kicking around upgrade questions. The price for 2005 Enterprise is very much higher than Standard, but because we have 2000 Enterprise we were wondering if we needed that version in 2005 flavor as well. It looks like, with just a dual-node cluster, 2005 Standard fills the functionality that 2000 Enterprise had at least for clustering. Of course we don't get online recovery, online indexing, etc, but at least we could move to SQL 2005 Standard, get similar clustering functionality to what we have now and save $50K. That would take a very big bite out of our budget.

How to identify which edition im i running?

Good day,
I would like to identify which EDITION of reporting services im running,
enterprise or standard. I know that you can check the version by going to
http://localhost/reportserver. but I want to know the edition.
thanks in advance
Oren ZipporiHi Oren,
The edition you are running is similar to the edition of SQL Server ou
are running it on.
I=2Ee., if you are running SQL Server Standard Edition, then your
Reporting Services Edition is Standard.
Kind regards,
Bj=F6rn|||Hello Oren,
You can do it programmatically like this using the webservice API:
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.ServerInfoHeaderValue = new ServerInfoHeader();
rs.ListChildren("/", false);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersion);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerEdition);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersionNumber);
-Chris
> Good day,
> I would like to identify which EDITION of reporting services im
> running,
> enterprise or standard. I know that you can check the version by going
> to
> http://localhost/reportserver. but I want to know the edition.
> thanks in advance
> Oren Zippori

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

How to identify when the values are going on particular record

Hi,
I want to identify, when the values are modified/updated on the particular
row in a table, i am not using triggers in this table.
Please advise me, any options in SQL Profiler Trace to find out this.
rgds,
SouraSouRa
Which version of SQL Server you are using?
In SQL Server 2005 there is new OUTPUT clause to track changes
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> Hi,
> I want to identify, when the values are modified/updated on the particular
> row in a table, i am not using triggers in this table.
> Please advise me, any options in SQL Profiler Trace to find out this.
> rgds,
> Soura|||Hi,
I am using Sql Server 2000, any options in Sql 2000
rgds,
Soura
"Uri Dimant" wrote:
> SouRa
> Which version of SQL Server you are using?
> In SQL Server 2005 there is new OUTPUT clause to track changes
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> > Hi,
> >
> > I want to identify, when the values are modified/updated on the particular
> > row in a table, i am not using triggers in this table.
> >
> > Please advise me, any options in SQL Profiler Trace to find out this.
> >
> > rgds,
> > Soura
>
>

How to identify when the values are going on particular record

Hi,
I want to identify, when the values are modified/updated on the particular
row in a table, i am not using triggers in this table.
Please advise me, any options in SQL Profiler Trace to find out this.
rgds,
SouraSouRa
Which version of SQL Server you are using?
In SQL Server 2005 there is new OUTPUT clause to track changes
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> Hi,
> I want to identify, when the values are modified/updated on the particular
> row in a table, i am not using triggers in this table.
> Please advise me, any options in SQL Profiler Trace to find out this.
> rgds,
> Soura|||Hi,
I am using Sql Server 2000, any options in Sql 2000
rgds,
Soura
"Uri Dimant" wrote:

> SouRa
> Which version of SQL Server you are using?
> In SQL Server 2005 there is new OUTPUT clause to track changes
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
>
>

How to identify when the values are going on particular record

Hi,
I want to identify, when the values are modified/updated on the particular
row in a table, i am not using triggers in this table.
Please advise me, any options in SQL Profiler Trace to find out this.
rgds,
Soura
SouRa
Which version of SQL Server you are using?
In SQL Server 2005 there is new OUTPUT clause to track changes
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> Hi,
> I want to identify, when the values are modified/updated on the particular
> row in a table, i am not using triggers in this table.
> Please advise me, any options in SQL Profiler Trace to find out this.
> rgds,
> Soura
|||Hi,
I am using Sql Server 2000, any options in Sql 2000
rgds,
Soura
"Uri Dimant" wrote:

> SouRa
> Which version of SQL Server you are using?
> In SQL Server 2005 there is new OUTPUT clause to track changes
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
>
>

how to identify what consumes cpu in sql server

Hi all
I use 64 bit 2005 server with 8cpu and 8G of memory.
This server is accessed by large number of intensive or not so intensive programs.
I had eliminated all inefficient queries by means of sql profiler. What I see now is 30 procs or so runining in 1 second. They are all pretty simple and as I said use indexes. cpu column for most show 0, reads show 10 - 50 - pretty good.
But... my cpu utilization is 75% in avg. across of all 8 cpu's. I really can't find an answer for it.
If procs run so efficient, where does cpu go? Disk queue length is 0.04 or less - seems very good.
Task manager shows that all of it 75% attributed to sql server.
So which resources besides sql queries use so much cpu? Do I have to look at some other areas and which ones where cpu could be used besides sql queries themselves.

Thank you, Gene.

You could have other issues besides CPU that are causing CPU pressure, or it could be that your queries are very efficient, but they are being run so frequently as to cause CPU pressure. Try running these DMV queries to get a better handle on what is going on.

-- Check CPU Pressure

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)

,'%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)

,'%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

FROM sys.dm_os_wait_stats

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

-- Get Top 50 executed SP's ordered by avg worker time

SELECT TOP 50 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count', ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',

qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',

qs.total_worker_time AS 'TotalWorkerTime',

qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS Age,

ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

--WHERE qt.dbid = 5 -- Filter by database

ORDER BY qs.total_worker_time/qs.execution_count DESC

-- Get Top 50 executed SP's ordered by calls/sec

SELECT TOP 50 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',

qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',

qs.total_worker_time AS 'TotalWorkerTime',

qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',

qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',

--qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',

qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'

, qt.dbid

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

--WHERE qt.dbid = 5 -- Filter by database

ORDER BY qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) DESC

|||

Or try the SQL 2005 Performance Dashboard, it's pretty powerful

http://www.sql-server-performance.com/bm_performance_dashboard_2005.asp

|||

Hi Glen

It's very interesting material you sent. Thank you so much. i will need time on Monday to analyze it.

Great technical stuff! Gene.

How to identify User Objects within sysobjects ?

I am querying master..sysobjects table for user defined objects. I have
been relying on category field to show if a given object is a user defined
object or system object. My understanding was that a category value of
zero (0) is a user object. Apparently this is not always true. I have
seen value 16 for some. Where this is documented if any ? BOL does not
talk about the values. Or may be there is a better way to find this out
other than Category field.
I appreciate in advance for any suggestions.
MacYou can use the OBJECTPROPERTY function for this:
SELECT <column list>
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
--
Jacco Schalkwijk
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>|||"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
Are you looking for specific objects?
SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
etc.
I'm not sure if that will help you out or not.
Rick Sawtell
MCT, MCSD, MCDBA|||Excellent! Thanks Jacco for the reply. That must be it. I should check to
see if this is supported in earlier releases as well.
Mac
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23wkkjWJ2EHA.2676@.TK2MSFTNGP12.phx.gbl...
> You can use the OBJECTPROPERTY function for this:
> SELECT <column list>
> FROM sysobjects
> WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> >I am querying master..sysobjects table for user defined objects. I have
> > been relying on category field to show if a given object is a user
defined
> > object or system object. My understanding was that a category value of
> > zero (0) is a user object. Apparently this is not always true. I have
> > seen value 16 for some. Where this is documented if any ? BOL does not
> > talk about the values. Or may be there is a better way to find this
out
> > other than Category field.
> >
> > I appreciate in advance for any suggestions.
> > Mac
> >
> >
>|||Thanks Rick. I was looking for user-stored procedures in the master db. I
can use the TYPE to filter the object types but to know whether it is system
defined or user defined object, I received an answer that ObjectProperty
function can be used to find that out.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> >I am querying master..sysobjects table for user defined objects. I have
> > been relying on category field to show if a given object is a user
defined
> > object or system object. My understanding was that a category value of
> > zero (0) is a user object. Apparently this is not always true. I have
> > seen value 16 for some. Where this is documented if any ? BOL does not
> > talk about the values. Or may be there is a better way to find this
out
> > other than Category field.
> >
> > I appreciate in advance for any suggestions.
> > Mac
> >
> >
> Are you looking for specific objects?
> SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> etc.
> I'm not sure if that will help you out or not.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||> I was looking for user-stored procedures in the master db.
Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects in
the master db (sysobjects is going away eventually, and it is not
recommended to use these tables directly if you can avoid it).
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTINE_NAME),
'IsMSShipped') = 0
--
http://www.aspfaq.com/
(Reverse address to reply.)
I
> can use the TYPE to filter the object types but to know whether it is
system
> defined or user defined object, I received an answer that ObjectProperty
> function can be used to find that out.
> "Rick Sawtell" <quickening@.msn.com> wrote in message
> news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> >
> > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> > >I am querying master..sysobjects table for user defined objects. I
have
> > > been relying on category field to show if a given object is a user
> defined
> > > object or system object. My understanding was that a category value
of
> > > zero (0) is a user object. Apparently this is not always true. I
have
> > > seen value 16 for some. Where this is documented if any ? BOL does
not
> > > talk about the values. Or may be there is a better way to find this
> out
> > > other than Category field.
> > >
> > > I appreciate in advance for any suggestions.
> > > Mac
> > >
> > >
> >
> > Are you looking for specific objects?
> >
> > SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> > etc.
> >
> > I'm not sure if that will help you out or not.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
>|||Thanks. I should look into that.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVER44J2EHA.2316@.TK2MSFTNGP15.phx.gbl...
> > I was looking for user-stored procedures in the master db.
> Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects
in
> the master db (sysobjects is going away eventually, and it is not
> recommended to use these tables directly if you can avoid it).
> SELECT ROUTINE_NAME
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE='PROCEDURE'
> AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTINE_NAME),
> 'IsMSShipped') = 0
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
> I
> > can use the TYPE to filter the object types but to know whether it is
> system
> > defined or user defined object, I received an answer that
ObjectProperty
> > function can be used to find that out.
> >
> > "Rick Sawtell" <quickening@.msn.com> wrote in message
> > news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > >
> > > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > > news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> > > >I am querying master..sysobjects table for user defined objects. I
> have
> > > > been relying on category field to show if a given object is a user
> > defined
> > > > object or system object. My understanding was that a category value
> of
> > > > zero (0) is a user object. Apparently this is not always true. I
> have
> > > > seen value 16 for some. Where this is documented if any ? BOL does
> not
> > > > talk about the values. Or may be there is a better way to find
this
> > out
> > > > other than Category field.
> > > >
> > > > I appreciate in advance for any suggestions.
> > > > Mac
> > > >
> > > >
> > >
> > > Are you looking for specific objects?
> > >
> > > SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for
procs
> > > etc.
> > >
> > > I'm not sure if that will help you out or not.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > >
> >
> >
>

How to identify User Objects within sysobjects ?

How to Identify user defined data types

How can you tell which datatypes in a given database are user defined
(with a query, not by looking in Enterprise Manager)? This is for SQL
Server 2000.SELECT domain_name
FROM information_schema.domains

--
David Portas
SQL Server MVP
--|||Bruce (sandell@.pacbell.net) writes:
> How can you tell which datatypes in a given database are user defined
> (with a query, not by looking in Enterprise Manager)? This is for SQL
> Server 2000.

SELECT * FROM systypes WHERE xusertype > 255

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to identify unused databases

Hi Folks,
client company has around 25 sql servers .
Each server has got 5 to 10 databases.
Some of the databases are old.
We don't know whether any applications using old databases or not.
Is there any way we can find the unused databases in past 3 months or past 1
year
using some Lastupdatedatetime or last action on sql server system tables
using T-SQL scripts.
If no application is accessing the database and no actions performed on
that database from past 1 year then we need to delete that databases.
How to identify unused databases?
Any kind of help is greatly appreciated.
Thanks
KumarKumar wrote:
> Hi Folks,
> client company has around 25 sql servers .
> Each server has got 5 to 10 databases.
> Some of the databases are old.
> We don't know whether any applications using old databases or not.
> Is there any way we can find the unused databases in past 3 months or
> past 1 year
> using some Lastupdatedatetime or last action on sql server system
> tables using T-SQL scripts.
> If no application is accessing the database and no actions performed
> on that database from past 1 year then we need to delete that
> databases.
> How to identify unused databases?
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
You can create a trace on the server. Include only the SQL:StmtStarting
and RPC:Starting events. Include only the minimum number of columns:
EventClass, SPID, DatabaseID. You could exclude system databases like
master, msdb, and tempdb.
To cut down the rows a little, you could use SQL:BatchStarting, but if a
batch has "USE" statements and accesses more than one database, the
DatabaseID will only refer to the database that was used at execution
time.
If you let the trace run for a couple of hours (or a day or two), you
should have a pretty good picture of what databases were accessed on
each server.
Best to do this using a server-side trace which you can script from
Profiler using the File - Script Trace menu option and stopping manually
using sp_trace_setstatus.
David Gugick
Quest Software
www.imceda.com
www.quest.com

How to identify the Reporting Services Edition

Hi all, I need to know the reporting services, if it is Enterprise, Standard
or Developer. I found only documents that tell me how to identify the
version, but not the edition.
Anyone knows how to do this'
Thank you very much!!!
Alexandre Calderaro
Avanade Italy
MSCDBASorry,
After post the question I found the answer in the most obvious place:
Control Panel, Add Remove Programs...
Anyway
Thanks
"Alex" wrote:
> Hi all, I need to know the reporting services, if it is Enterprise, Standard
> or Developer. I found only documents that tell me how to identify the
> version, but not the edition.
> Anyone knows how to do this'
> Thank you very much!!!
> Alexandre Calderaro
> Avanade Italy
> MSCDBA
>|||For 2000, the version information is located at the bottom of the main page
of your Report Server
RTM: 8.00.743.00
SP1 : 8.00.878.00
SP2 : 8.00.1038.00
The edition information is stored in the following registry key:
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Reporting
Services\ProductCode
Standard: {B19FEFE7-069D-4FC4-8FDF-19661EAB6CE4}
Enterprise: {33FE9EED-1976-4A51-A7AF-332D9BBB9400}
Developer: {2879CA50-1599-4F4B-B9EC-1110C1094C16}
Evaluation: {7C93251A-BFB4-4EB8-A57C-81B875BB12E4}
Med Bouchenafa
"Alex" <Alex@.discussions.microsoft.com> a écrit dans le message de news:
92C3C825-D8BB-43C5-9342-F4807D12D157@.microsoft.com...
> Hi all, I need to know the reporting services, if it is Enterprise,
> Standard
> or Developer. I found only documents that tell me how to identify the
> version, but not the edition.
> Anyone knows how to do this'
> Thank you very much!!!
> Alexandre Calderaro
> Avanade Italy
> MSCDBA
>

how to identify the fixpack level?

hi,
for sql server 2000, how can we find the fixpack(service pack) level installed on this sql server?
is there any command, or any gui tool to identify the level?
tnksSET NOCOUNT ON
SELECT CONVERT(CHAR(25),@.@.SERVERNAME) AS 'SQL SERVER',
SUBSTRING(@.@.VERSION,23,4) AS 'PRODUCT VERSION',
SUBSTRING(@.@.VERSION,35,3) AS 'BUILD NUMBER',
CASE SUBSTRING(@.@.VERSION,35, 3)
WHEN '194' THEN 'NO SP'
WHEN '384' THEN 'SP1'
WHEN '534' THEN 'SP2'
WHEN '760' THEN 'SP3'

ELSE 'Unknown - may be a Hot-Fix version or script out of date'
END AS 'SERVICE PACK'
set nocount off|||thanks philio,
how about service pack 3a, is there an id also to distinguish it from sp3??|||SP3 and SP3a have identical build numbers (760).

Also, you can use this:

SELECT
cast(@.@.microsoftversion / power(2, 24) as varchar(2)) + '.00.' + cast(@.@.microsoftversion & 0xffff as varchar(4)) as VERSION

To derive version info...

hmscott
thanks philio,
how about service pack 3a, is there an id also to distinguish it from sp3??

how to identify stored procedure dependencies?

Hello,
I need to find where an sp is called from another sp or how many sp's a main
sp is calling. Here is something that I tried with no luck. I know that
this sp is calling other sp's or is being called by other sp's. How can I
get a list of sp's related to this'
--this does not return anything for me even though I know there are
dependencies
select DISTINCT OBJECT_NAME([id]) Proce FROM sysdepends WHERE
OBJECT_NAME([depid]) in (select specific_name from
information_schema.routines where routine_type = 'sp_Compare_Add')
order by Proce
Thanks,
Rich1. See what the undocumented procedure master..sp_MSdependencies tells
you. However, this may sometimes miss references.
2. You can query the syscomments table like so:
select object_name(id) as obj_name from syscomments where text like
'%usp_mysp%'
3. Run a SQL Profiler event trace.
http://msdn.microsoft.com/library/d...
ethowto15.asp
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D0ADF637-E524-4A83-96C0-7E343C13899F@.microsoft.com...
> Hello,
> I need to find where an sp is called from another sp or how many sp's a
> main
> sp is calling. Here is something that I tried with no luck. I know that
> this sp is calling other sp's or is being called by other sp's. How can I
> get a list of sp's related to this'
> --this does not return anything for me even though I know there are
> dependencies
> select DISTINCT OBJECT_NAME([id]) Proce FROM sysdepends WHERE
> OBJECT_NAME([depid]) in (select specific_name from
> information_schema.routines where routine_type = 'sp_Compare_Add')
> order by Proce
>
> Thanks,
> Rich|||Here is a query which will give you the listings for procedure to procedure
dependencies:
select object_name([id]) as [Procedure],
object_name([depid]) as [Depends on Procedure]
from dbo.[sysdepends]
where objectproperty([id], 'IsProcedure') = 1
and objectproperty([depid], 'IsProcedure') = 1
and objectproperty([id], 'IsMSShipped') = 0
Order by [id], [depid]
Note that there is the possibility due to late binding data could be missing
here
HTH-
--Tony
"Rich" wrote:

> Hello,
> I need to find where an sp is called from another sp or how many sp's a ma
in
> sp is calling. Here is something that I tried with no luck. I know that
> this sp is calling other sp's or is being called by other sp's. How can I
> get a list of sp's related to this'
> --this does not return anything for me even though I know there are
> dependencies
> select DISTINCT OBJECT_NAME([id]) Proce FROM sysdepends WHERE
> OBJECT_NAME([depid]) in (select specific_name from
> information_schema.routines where routine_type = 'sp_Compare_Add')
> order by Proce
>
> Thanks,
> Rich|||Thanks very much.
2. You can query the syscomments table like so:
select object_name(id) as obj_name from syscomments where text like
'%usp_mysp%'
This comment did the trick. I located what I needed.
"JT" wrote:

> 1. See what the undocumented procedure master..sp_MSdependencies tells
> you. However, this may sometimes miss references.
> 2. You can query the syscomments table like so:
> select object_name(id) as obj_name from syscomments where text like
> '%usp_mysp%'
> 3. Run a SQL Profiler event trace.
> http://msdn.microsoft.com/library/d...enethowto15.asp
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:D0ADF637-E524-4A83-96C0-7E343C13899F@.microsoft.com...
>
>|||Hi , you can also do this
CREATE PROC sp_search_code
(
@.SearchStr varchar(100),
@.RowsReturned int = NULL OUT
)
AS
BEGIN
SET NOCOUNT ON
SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object
name',
CASE
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
THEN 'Extended stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
THEN 'Trigger'
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
THEN 'Table-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
THEN 'Inline function'
END AS 'Object type',
'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) +
'''' AS 'Run this command to see the object text'
FROM syscomments c
INNER JOIN
sysobjects o
ON c.id = o.id
WHERE c.text LIKE '%' + @.SearchStr + '%' AND
encrypted = 0 AND
(
OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
)
ORDER BY 'Object type', 'Object name'
SET @.RowsReturned = @.@.ROWCOUNT
END

Friday, February 24, 2012

How to implement Application Roles for an application?

how to implement a recursive algorithm as iterative?

How to implement a foreign key

How to implement a DropDownList w/o a Table

How to implement a data base design for multi language support?

How to implement a data base design for multi language support?

How to implement a data base design for multi language support?

How to implement a custom Order Column?

How to implement a change log for SQL database?

How to implement "AND NOT IN" in a many-to-many relationship dimension?

How to implement "AND NOT IN" in a many-to-many relationship dimension?

How to impersonate user for data on another server?

How to impersonate the user and send those credentials?

How to impersonate the user and send those credentials?

How to impersonate known user to AS 2000

How to ignore timepart when using LookUp with date.

How to ignore sp_runwebtask errors?

How to ignore error and continue trasactional replication

How to ignore a dimension in MDX?

How to identify which version we have?

How to identify which edition im i running?

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

How to identify when the values are going on particular record

How to identify when the values are going on particular record

How to identify when the values are going on particular record

how to identify what consumes cpu in sql server

How to identify User Objects within sysobjects ?

How to identify User Objects within sysobjects ?

How to identify User Objects within sysobjects ?

How to Identify user defined data types

How to identify unused databases

How to identify the Reporting Services Edition

how to identify the fixpack level?

how to identify stored procedure dependencies?

hide system tables

Blog Archive

About Me