Hello,
We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
Regards,
Vnk.
Hello,
We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails in job, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
Regards,
Vnk.
I want find all dependent objects related to a table. I am using -
SELECT DISTINCT so.name
INTO #tmp
FROM dbo.sysobjects so
JOIN dbo.SysComments sc
ON sc.id = so.id
WHERE sc.text LIKE '%tablename%'But, I want all those SP/functions/views that use the output of this query and so on...
Eg.
Table -> used in usp1 -> usp1 used in usp2 ...and so on
Is it possible to make the execution of one job dependent on the successful execution of a previous job?
View 2 Replies View RelatedHi!
View 7 Replies View RelatedHello, I have caching enabled in my application and I am using SqlCacheDependency to cache my tables. Well, the cache works fine but when the table is updated the information in my cache does not update. I DO have the broker service started and running but not sure what else i am missing. I am using sql server 2005.
View 1 Replies View RelatedHello all.. i'm having a major issue with my Sql Express 2005 database :(
I'm using wicked code sitemap to allows for a sitemap to be stored in a database.. it uses sql cache dependency to invalidated the cache bla bla
Problem:
After i update a record / add new record the database generates a SqlQueryNotificationStoredProcedure
But it never gets executed. I've done tests on the code it's calling everything accordingly so i'm assuming the problem lies in a configuration setting of the database
done alot of searching around the net and i've found this
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username
i'm using Windows Authentication how do i run this and will this solve the problem
Thank you in advance
Chris
I'm working off of the example shown here:
http://www.c-sharpcorner.com/UploadFile/mosessaur/sqlcachedependency01292006135138PM/sqlcachedependency.aspx?ArticleID=3caa7d32-dce0-44dc-8769-77f8448e76bc
The tutorial shows that an entry must be made in web.config for a sqlCacheDependency node in web.config. When a dependency is added, they set an attributed called "connectionStringName" that references a connectionString established earlier in the web.config. My question is twofold:
1). First, I tried setting up my connection string using the connectionStrings node (my other apps use the AppSettings node), but when I try to extract the value in my code using the following syntax:
connStr = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["devConnStr"].ConnectionString);
I get the following compilation error: "ConfigurationManager does not exist in the class or namespace System.Configuration";
Am I extracting it with the wrong code? Does that setup exists in .NET v 1.4? No, I cannot use .NET v 2.0 for various reasons.
2). If I cannot use ConnectionStrings in that way and must use AppSettings, how do I set up SqlCacheDependency node to recognize that connection string?
Hi,
I've problem in replicating data thru SNAPSHOT as I've the tables with Foreign Keys at subscriber end. The Truncate table is not working because these tables
were referenced by a FK. Even for recreating table during snapshot is also same problem. Any suggestions?
Thanks
TT
I am under the impression that either there are deficiencies in the way SQLServer 2000 is keeping track of depedencies or perhaps the dependenciessimply need to be re-evaulated / recompiled.What is the best and/or easiest way to ensure that the dependency info isaccurate?Is there a way to tell MSSQL to rebuild/reexamine them all?Thanks in advance to the MVPs and other generous and knowledgable posterswho have helped me recently.Chad
View 2 Replies View RelatedI am attempting to duplicate a nifty feature that one of my colleagues used:
We have parameters where a user can select a client or an individual account. There are available values for each parameter from two queries. I tried to have the available values in the account list dependent on the user's selection in the client list by passing the client parameter's value to the query for the account data set and using it in a where clause, but I get a "forward dependency" error.
The weird thing is that my colleague tried the same strategy and it worked. We together tried to set my report up the same as his, but cannot find why his works and mine doesn't. Any ideas?
His report works when deployed from my machine, so there must be something in the actual report that is different...
Hello ,
We have a Windows Service running on 30 server that is using SQLDependency on 10 different tables to recieve data change notifications. We have noticed that when a data change occurs not all 30 servers recieve the notification. Any help that you can provide to troubleshoot this issue would be greatly appereciated.
raina
Hi,
I've got a Decision Tree model and I'm trying to browse the dependency network. Apparently, there are too many nodes and they don't all show up in the view. I know that I can find hidden nodes, but is there a way to show all w/o having to add them one by one to view? How does the UI determine which ones to filter out?
Thanks,
Mike
Hello everybody,
It's now quite some time that one particular behaviour of SSIS is really frustrating me and I would like to know if I'm the only one experiencing this problem or if other people have the same problem.
The issue I'm talking about is SSIS 'dependency on what is written in the XML files describing the flows.
Particularly with the Data Types of columns.
I'm explaining myself: Imagine your are developping a flow containing several numeric(18,0) columns...
During the flow you have to perform a lookup on an Integer Field.... Of course this operation is not allowed as a numeric is not mappable with an Integer... (This is, in my opinion, a nonsense as an implicit conversion has to be possible).
as a result of this behaviour, I decide to change the datatype (numeric) from my source query to an integer and use it in the Lookup which of course succeeds but now I have a second problem: each lookup in my flow has an error handling branch which I'm joining back using a Union transform. and there we have the second irritation: the Union transform doesn't replicate the Data Type changes that occured upwards in the flow... worse: it even has no interface to let you modify the data types like the advanced editor of some transforms or data sources. (I've just lost a complete dataflow while trying to modify it manually in the xml file directly :-( for those who are considering modifying directly the XML, don't!! You are asking for trouble and a lot of frustration when you'll switch back to the designer to see the effects )
My question is now: Am I misusing SSIS?? Is there somewhere an option to activate in order to get this behaviour fixed??
Has anyone else experienced this problem?? How are you solving this??
Are there any plans in the future to loose this dependency on the datatypes or at least add some implicit conversions??
Thanks in advance for your replies, suggestions,questions and other thaughts about this subject :-)
Alain
I have been trying to run the DependencyAnalyzer and it does not seem to be working correctly. The most that I can get it to do is just list the packages. I am trying to use it against packages that are stored as files and not in SQLServer. Has anyone had any luck with this executable? I don't know if it is because I don't have the full Visual Studio's installed on my machine. When I try to open the project, the DependencyAnalyzer and DependencyViewer are showing some errors. But I was able to run the msbuild to generate an executable with the path that is listed in the readme.txt file. Maybe, it is compiling ok but it is lacking the proper references.
View 18 Replies View Related--The Dependency row is not created!
-- I have a procedure "Example 1".
-- When I look at the sys.sql_dependencies I didn't find a row with Table1 dependency.
--If I write the procedure like "Example 2" the dependency Table1 exists in sys.sql_dependencies.
--
--DEFENENTLY A BUG. CAN YOU FIX IT?
--I REALLY NEED IT!!!!!!
Create table Table1 (id int);
GO
--Example 1
CREATE procedure a
as
create table #Test(id int)
Insert into #test
Select id from Table1
DROP TABLE #test;
GO
Select * from sys.sql_dependencies where object_id=object_id('a');
GO
--Example 2
CREATE procedure c
as
Select id into #test from Table1
DROP TABLE #test;
GO
Select * from sys.sql_dependencies where object_id=object_id('c');
DROP PROCEDURE c
DROP PROCEDURE a
DROP TABLE Table1
Hi All,
when i do a right click on a Table and go to View Dependencies, I get the option of viewing both objects that depend on this table and vice versa, but this has to be hierachial right?
Like if there are three tables A,B,C and A is dependent on B and B is dependent on C,
I am able to see only from A to B, the relation from B to C is not coming. Am I wrong? Can someone correct me on the option of view dependency?
Thanks
I have a 1:1 relationship between tables and am finding that the parentis sometimes mising the child.How do I query for what is not there?TIA
View 4 Replies View RelatedHello All,
We have an app that we do not have the source code for that is behaving badly. I'd like to find out what queries it is running in order to possibly fix the issue form the SQL server side of things. Anyone know what table/view I should select off of to find the queries that have been run recently?
Thanks in advance!
Hi everyone,
Can anyone let meknow how do i enable a service broker. I am trying to enable a service broker for an issuetracker application to get change of events in my database. When ever i try enabling it using the ALTER DATABASE [ databse] set Enable_Broker. it takes abt more that 2 hrs or more but doesnt show as enabled.
Thanks in Advance,
Pawan Venugopal
Hello.I am having problems with SQL Cache dependency. I am using SQL 2005, ASP .net 2.0.Every time i try to load data from cache, this is null. It acts like someone is constantly changing everything in the db.Because of this my website makes hundreds of connections to the db instead of 5. This is a major issue that i cannot figure it out. PLEASE ADVISE.On my local machine everything seems to work just fine. On the testing server the cache is always null.Is cache dependency related - to the platform used? - to the number of IIS servers connected to the DB? - to the sql user used? Also a strange thing happens. When i change something in web.config the cache is working for about 1 min and after that it stops. Here is the code i wrote: public ManufacturerList GetAllManufacturers() { if (HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] != null) return HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] as ManufacturerList; SqlCacheDependency dep = new SqlCacheDependency(ConstantsManager.Instance.GetDefaultAsString("DB_NAME"), ConstantsManager.Instance.GetDefaultAsString("TABLE_MANUFACTURERS")); _manufacturerList = new ManufacturerList(); DataReader reader = SqlHelper.ExecuteDataReader(WebContext.ConnectionString, CommandType.StoredProcedure, "dvx_web_MANUFACTURER_LoadAll", null); while (reader.Read()) { int manufacturerID = reader.GetInt("ID"); Manufacturer findManufacturer = _manufacturerList.FindByID(manufacturerID); if (findManufacturer == null) { findManufacturer = new Manufacturer(); findManufacturer.LoadFromDataReader(reader); _manufacturerList.Add(findManufacturer); } } reader.Close(); HttpContext.Current.Cache.Insert(ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS"), _manufacturerList, dep); return _manufacturerList; } Thanks a lot.
View 1 Replies View Related Hi,I want to add Sql data dependency in GridView. Whenever any changes in database, same will be reflected in Grid. but this should work in both Sql server 2000 and sql server 2005. And when we click on "Pause" button it should stop refreshing.
PAUSE
Index
First name
Last Name
Address
1
Xyz
Abc
Asdf, asdf
2
Pqr
Asd
Sdsa, asad
Is there any way to fulfill this kind of requirement? Please suggest me optimal solution to do this.Regards,Mustakim Mansuri
Hi,
can I use sql cache dependency with ms sql 2005 express edition?
I have two nodes in my windows cluster, and i have 5 SQL instances running there. Now i want to make sure that all instances are running on just one node. That is, if one instance fails and wants to start on the other node, all other instances must also failover. My idea was to use disc dependenies to achieve this, but i can just set a dependency on discs that belong to the same instance...
View 4 Replies View RelatedHi, I have a question concerning microsoft data mining in sql server 05.
The thing is: when i create a new AS project in my visual studio define data source view and create model, let it be decition tree, i can see "dependency network" where i can figure how one column relate to another.
I want to use this knowledge in my C# application in order to choose right column to my next task. How do i get the result using dmx or amo?? Do you have any idea?
Hello, I have the SQLDependency working OK (i.e. the notification comes back when the data is changed), however I now want to use an SELECT statement that contains a GROUP BY as follows :-
select [dbo].[Investment].[ClientId]
from [dbo].[Investment]
group by [dbo].[Investment].[ClientId]
As you can see it is very simple and according to the restrictions placed on the statements possible for the notification it seems to fit within the specifications -
http://msdn2.microsoft.com/en-US/library/ms181122.aspx
However when this is submitted it comes back 'Invalid'
Note that the field ClientId (in the above example) is *not* a nullable field as per the rules
I have tried numerous variations on the above (including adding a SUM() which is also within the rules as per the above link), but to no avail. Can anyone tell me why ?
Many thanks
Travis
Hello,
How do I get n and only items predicted by a specific item(s) either directly or indirectly as shown in the dependency network diagram?
For instance, the predict function won€™t work for me - because running this query on AdvantureWorks:
SELECT PREDICT([Association].[Products], 5)
From [Association]
NATURAL PREDICTION JOIN
(SELECT (SELECT 'Touring Tire Tube' AS [Model]) AS [Products]) AS t
Returns Sport-100 as a second result, although it is not predicted by any mean by Touring Tire Tube as shown in the dependency network diagram.
My query should have returned just one row - Touring Tire.
Hi,
I have a bunch of code for integrating reporting services 2000 with our application, which has been running fine for quite a while. We've recently encountered a problem and i can't quite figure it out.
There is a set of code that creates controls for entering parameter values for our reports, which takes an array of ReportParameters as an argument. We loop through the parameters and create controls as appropriate. The check to see if we need to create a dropdownlist of parameters populated through a query looks a bit like this:
if (oParam.ValidValues != null)
{
//Create ddl based on the list of valid values.
}
This is where i am encountering a problem. One of our reports, that has been running fine until recently is now not getting a dropdownlist created for it's second parameter. When i run into the code, i can see that this parameter now thinks that it has a dependency on the first parameter (which it does not), and the list of ValidValues is null.
If i go into the report designer and change the order of the two parameters in the report, then it works fine, the list is populated and there seems to be no dependency. If i change it back, it is broken again. I've checked the query that populates the list, it is not parameterised and is a very simple kind of SELECT A, B FROM TABLEA. I've looked in the report parameters and i can see nothing there either.
Your help would be very much appreciated here. I am running SSRS with SP2 applied.
Paddy
Hi
i have a stored proc that searches through stored proc etc text and returns the name if some text is found in it.
I would like to extend this so whne the stored procedure name is retrieved it checks to see if that stored proc is used in another stored proc. Thus building up a tree of dependencies (i dont want to use the sysdepend function). im sure there must be TSQL around that does this or could someone show me how to do this. My stored porc sample is below
TIA
Stu
DECLARE @cSearch varchar(100)
SET @cSearch='%'+LTRIM(RTRIM(@p_cCallName))+'%'
-- sql Server 2005 ?
IF CHARINDEX('9.00',@@VERSION)<>0
SELECT DISTINCT so.name
FROM sys.objects so
INNER JOIN sys.Sql_modules sc
ON sc.object_id=so.object_id
WHERE so.type IN('P','TR','FN')
AND sc.definition LIKE @cSearch
AND so.name<>@p_cCallName
ORDER BY name
Hi
I am trying to find when a name has been entered more than once into 1 database table.
I'm currently doing something like this (can't remember exactly, not at work)
SELECT COUNT(*) AS Cnt, Name
FROM tblTable
GROUP BY Name
ORDER BY Cnt Desc
This brings back all the Names in the database and tells me which are duplicates but I want to just have the results of the duplicate values and not the single values.
Hope you can help.
Thanks
I have a table with several foreign key relationships. I am scripting with PHP and was wondering if anyone knows how to query the database to show the relationships that a table has. I have been using MySQL, but for a number of projects have to use MS SQL.
Any help will be greatly appreciated.
Hi
I need help in finding the query which will provide the following resultset from the below table..
Table :
create table product_stocks(product_id int , product_type varchar(20) , no_of_units int)
Data:
insert into product_stocks values(1,'A',30)
insert into product_stocks values(2,'A',70)
insert into product_stocks values(3,'A',60)
insert into product_stocks values(4,'A',40)
insert into product_stocks values(1,'B',90)
insert into product_stocks values(2,'B',60)
insert into product_stocks values(3,'B',70)
insert into product_stocks values(4,'B',40)
insert into product_stocks values(1,'C',40)
insert into product_stocks values(2,'C',50)
insert into product_stocks values(3,'C',80)
insert into product_stocks values(4,'C',90)
Result Set:
product_type product_id no_of_units
--------------- ------------- --------------
A 2 70
A 3 60
A 4 40
B 1 90
B 3 70
B 2 60
C 4 90
C 3 80
C 2 50
i.e The result set gives the top 3 products in each product_type based on the no_of_units.
thanks
If I run sample query below against an adventureworks database
where can I find query cost?
USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;
Does estimated subtree cost for in the actual execution plan (when right click SELECT operator in the execution plan) is considered query cost?