Enable Table Dependency In AdventureWorks Db?
Feb 20, 2007
I have been having trouble with enabling table dependencies using the aspnet_regsql utility on the Product table in the AdventureWorks database. It is in the Production in the normal dbo schema. When I run the utility to enable the table dependency for SQL cache notifications it fails because it tries to prefix the reference to the table with dbo.
Now I know it just adds a trigger for INSERT, UPDATE and DELETE where it runs a stored procedure to update the main table dependency table, but if I do that manually it requires that the trigger also run in the Production schema. I am not sure if this will still work with SQL query notifications.
http://msdn2.microsoft.com/en-us/library/ms124719.aspx
Please reply if you can help.
View 2 Replies
ADVERTISEMENT
Sep 11, 2006
Hey, I can't enable caching on my local computer..I use daspnet_regsql -S "localhostSQLEXPRESS" -E -d books -edand aspnet_regsql -S "AdminSQLEXPRESS" -E -d books -edto try to start but i get a login error...cannot open database "books" requested by login. The login failed. Login failed for user Computer2/Adminunable to connect to SQL database for cache dependency registrationI tested connection with vwd and sql express management and it connects so I don't know what i'm doing wrong since I followed the direction and I googled for answer but not coming up with anything that's helping me..anyone have this problem and know how to resolve so i can cache some data output on my project
View 2 Replies
View Related
Dec 28, 2007
Hi All,
We have a very convoluted ETL system which is pulling unnecessary data. First thought is to restrict everything so that only the columns/tables that are necessary are brought back. We have a tons of reporting stored procedures that depend on ETL tables, is there anyway we can find out which column/tables these stored procedures are using?
Thanks!
View 3 Replies
View Related
Apr 24, 2008
Hello,
I have ran into a problem when i'm getting table names in c#.
I have tried the sql command:
select name from sysobjects where type='U' order by name
So it returns the names without a problem. But when I try to access to some tables using their names (to retrieve the columns) I sometimes get the error "Invalid object name" following by the name of the table.
So I realized the if the table was not under dbo user then i would get this error.
for example: "select name from ContactCreditCard"
gives me error because in database management tool it is shown like this: Sales.ContactCreditCard
How can i fix this problem? How to get that prefix of the tables?
Thank you,
View 8 Replies
View Related
Jun 5, 2015
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
View 3 Replies
View Related
May 29, 2006
I am working the Books Online documentation for the full-text search feature of SQL Server 2005 Express Advanced and having a problem following the instructions.
I made sure to choose the "Full Text Search" option during installation of VB 2005 Express Advanced.
I downloaded, installed, and attached the AdventureWorks database successfully.
I checked to ensure that the database was enabled for full-text search, but could not follow the instructions for indexing a table within the database. Here are the instructions from Books Online:
To enable a table for full-text indexing
Expand the server group, expand Databases, expand User Databases, and expand the database that contains the table you want to enable for full-text indexing.
Right-click the table that you want to enable for full-text indexing.
Select Full-Text index, and then click Enable Full-Text indexing.
Another document notes:
To create a full-text index on a table, the table must have a single, unique not null column. For example, consider a full-text index for the Document table in Adventure Works in which the DocumentID column is the primary key column.
When I right-click the Document table (Production.Document) in the AdventureWorks database, there is no option to "Select Full-Text Index" or "Enable Full Text indexing".
Am I missing something here?
How do I get the the table indexed for full text search?
View 5 Replies
View Related
Aug 27, 2007
Hi
I am creating a report on a database where some of the table names start
with the @ sign - ie @table1.
Reporting services picks this up as a parameter, instead of a table name in
my query, even though I am encapsulating the table name in square brackets
eg. [@table1]
I have several data sets in the report that i am using to populate valid
parameters. These datasets are all variations of queries from tables that
have @ as the first character.
When i then try to run the report i get an error message as follows:
"The report parameter pool has a default value or a valid value that depends
on the report parameter SD_POOLCONTRACTS. Forward dependencies are not valid"
This is frustrating as SD_POOLCONTRACTS is not a report parameter but one of
the database tables that has @ for it's first character.
here is the query that i use to obtain the valid values for the pool report
parameter that i am trying to set up.
SELECT distinct u_poolcode as Pool
FROM OCRD INNER JOIN
OCRG ON OCRD.GroupCode = OCRG.GroupCode INNER JOIN
CRD1 ON OCRD.CardCode = CRD1.CardCode inner JOIN
[@SD_POOLCONTRACT] INNER JOIN
[@SD_POOLCONTRCT_LINE] ON [@SD_POOLCONTRACT].DocEntry
= [@SD_POOLCONTRCT_LINE].DocEntry INNER JOIN
[@SD_CONTRACTS] ON [@SD_POOLCONTRCT_LINE].U_DocNo =
[@SD_CONTRACTS].DocNum ON
case when len(ocrd.fathercard) = 0 then ocrd.cardcode
else ISNULL(OCRD.FatherCard, OCRD.CardCode) end = [@SD_CONTRACTS].U_CardCode
WHERE (OCRD.CardType = 'c') AND (OCRG.GroupName LIKE N'producer%') AND
(CRD1.AdresType = 'b')
ORDER BY OCRG.GroupName, FatherCard, OCRD.CardCode
Any help would be appreciated.
View 1 Replies
View Related
Aug 28, 2015
I'm currently stuck with a table that has 350 mil records. Querying this table is insanely slow so I had a better look at existing yearly partitioning. I already managed to partition on a month level which increased the performance/querrying a lot. I did this on the staging table where I used an alter statement to split the 2015 partition by 12 months.
However, in our project we used Data Vault. This means that we have 4 tables (hub, sathub, link, satlink), all carrying 350 mil records. The problem is that altering the partition function does not work. The server cannot handle this action. What the best way is to do this, without having to drop/reload all tables.
View 17 Replies
View Related
Jan 3, 2008
Hi all,
I executed the following T-SQL code from a tutorial book and executed it in my SQL Server Management Studio Express (SSMSE):
--PivotTable.sql--
USE Adventureworks
GO
SELECT ShiftID, Name
FROM HumanResources.Shift
SELECT EmployeeID, ShiftID, Name
FROM HumanResources.Employee, HumanResources.Department
WHERE Employee.DepartmentID = Department.DepartmentID
--Compute the number of employees by
--department name and shift
SELECT Name, [1] AS 'Day', [2] AS 'Evening',
[3] AS 'Night'
FROM
(SELECT e.EmployeeID, edh.ShiftID, d.Name
FROM HumanResources.Employee e
JOIN HumanResources.EmployeeDepartmentHistory edh
ON e.EmployeeID = edh.EmployeeID
JOIN HumanResources.Department d
ON edh.DepartmentID = d.DepartmentID) st
PIVOT
(
COUNT (EmployeeID)
FOR ShiftID IN
( [1], [2], [3])
) AS spvt
ORDER BY Name
--For display in book
SELECT Name, [1] AS 'Day', [2] AS 'Evening',
[3] AS 'Night'
FROM
(SELECT e.EmployeeID, edh.ShiftID, CAST(d.Name AS nvarchar(26)) 'Name'
FROM HumanResources.Employee e
JOIN HumanResources.EmployeeDepartmentHistory edh
ON e.EmployeeID = edh.EmployeeID
JOIN HumanResources.Department d
ON edh.DepartmentID = d.DepartmentID) st
PIVOT
(
COUNT (EmployeeID)
FOR ShiftID IN
( [1], [2], [3])
) AS spvt
ORDER BY Name
IF EXISTS(SELECT name FROM sys.tables WHERE name = 'pvt')
DROP TABLE pvt
GO
--Create a table that saves the result of a pivot with employee
--names instead of numbers for column values
SELECT VName, [164] 'Mikael Q Sandberg', [198] 'Arvind B Rao',
[223] 'Linda P Meisner', [231] 'Fukiko J Ogisu'
INTO pvt
FROM
(SELECT PurchaseOrderID, EmployeeID, v.Name as 'VName'
FROM Purchasing.PurchaseOrderHeader h
JOIN Purchasing.Vendor v
ON h.VendorID = v.VendorID) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) pvt
ORDER BY VName
GO
--Show an excerpt FOR VName starting with A
SELECT TOP 5 * FROM pvt
WHERE VName LIKE 'A%'
GO
--For display in book
SELECT TOP 5 CAST(VName AS NVARCHAR(22)) 'VName',
[Mikael Q Sandberg], [Arvind B Rao],
[Linda P Meisner], [Fukiko J Ogisu]
FROM pvt
WHERE VName LIKE 'A%'
GO
--VendorID for Advanced Bicycles is 32
--Four PurchaseOrderID column values exist in PurchaseOrderHeader
--with VendorID values of 32 and EmployeeID values of 164
SELECT VendorID, Name FROM Purchasing.Vendor WHERE Name = 'Advanced Bicycles'
SELECT PurchaseOrderID FROM Purchasing.PurchaseOrderHeader WHERE VendorID = 32 and EmployeeID = 164
--Unpivot values
SELECT TOP 8 VName, Employee, OrdCnt
FROM
(SELECT VName, [Mikael Q Sandberg], [Arvind B Rao],
[Linda P Meisner], [Fukiko J Ogisu]
FROM pvt) p
UNPIVOT
(OrdCnt FOR Employee IN ([Mikael Q Sandberg],
[Arvind B Rao], [Linda P Meisner], [Fukiko J Ogisu])
)AS unpvt
GO
--For display in book
SELECT TOP 8 CAST(VName AS nvarchar(28)) 'VName', CAST(Employee AS nvarchar(18)) 'Employee', OrdCnt
FROM
(SELECT VName, [Mikael Q Sandberg], [Arvind B Rao],
[Linda P Meisner], [Fukiko J Ogisu]
FROM pvt) p
UNPIVOT
(OrdCnt FOR Employee IN
([Mikael Q Sandberg], [Arvind B Rao],
[Linda P Meisner], [Fukiko J Ogisu])
)AS unpvt
GO
--Query to check unpivoted values
SELECT TOP 2 *
FROM pvt
ORDER BY VName ASC
GO
--For display in book
SELECT TOP 2 CAST(VName AS NVARCHAR(22)) 'VName',
[Mikael Q Sandberg], [Arvind B Rao],
[Linda P Meisner], [Fukiko J Ogisu]
FROM pvt
ORDER BY VName ASC
GO
IF EXISTS(SELECT name FROM sys.tables WHERE name = 'pvt')
DROP TABLE pvt
GO
========================================
I got the following error messages and results:
Msg 207, Level 16, State 1, Line 7
Invalid column name 'DepartmentID'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'ShiftID'.
(86 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(1 row(s) affected)
(4 row(s) affected)
(8 row(s) affected)
(8 row(s) affected)
(2 row(s) affected)
(2 row(s) affected)
=================================================
I do not know why I got these 2 errors and how to correct them. Please help and advise me how to correct the mistakes and obtain the completely printed-out correct results.
Thanks in advance,
Scott Chang
View 3 Replies
View Related
Aug 31, 2007
Hi,
I am using SQL SERVER 2005 Express. I am trying to set up Enable a Table for Full-Text Indexing. I am following these instructions:
How to: Enable a Table for Full-Text Indexing (SQL Server Management Studio)
View 2 Replies
View Related
Jan 3, 2002
hi folks,
How to disable/enable an Identity Column in a table with the help of an sql
statement.
Thanks in advance.
Vineet Agarwal
View 1 Replies
View Related
Apr 9, 2002
I have transaction replication setup on two SQL7 boxes and a nightly job runs a procedure that need to alter a table and disable a trigger. Since replication has been set up the disable doesn't work. Any way around this.
View 2 Replies
View Related
Aug 14, 2007
Is it possible to make the execution of one job dependent on the successful execution of a previous job?
View 2 Replies
View Related
Oct 28, 2007
Goodday all
I wonder if anyone can help.
I'm trying to work through a tutorial using a sample database , Adventureworks. When I drag a table "Address" onto my page to set up a grid veiw , and start debugging , the error message reads "invalid object name Address"
On the database explorer the table has (Person) next to the name Address. This I think has something to do with the schema. I cannot get it right to change anything , no matter what I try. I have used this data base in a windows application and it works fine. No (Person ) attached to the table name.
Has anyone got any ideas.
Thanks
Rob
View 3 Replies
View Related
Sep 6, 2007
I've download and installed the sample database but the Server can't see it .. it doesn't appear in the User databases. I installed using the defaults. Anyone ha d the same problem and solved it?
View 1 Replies
View Related
Nov 24, 2006
Hello, 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 Related
May 31, 2007
Hello 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
View 3 Replies
View Related
Feb 8, 2008
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?
View 1 Replies
View Related
Dec 29, 2000
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
View 1 Replies
View Related
Jul 23, 2005
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 Related
Jun 5, 2007
I 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...
View 1 Replies
View Related
May 25, 2007
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
View 4 Replies
View Related
Nov 29, 2006
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
View 1 Replies
View Related
Nov 9, 2007
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
View 14 Replies
View Related
Dec 21, 2006
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
Dec 5, 2007
--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
View 1 Replies
View Related
Nov 9, 2006
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
View 4 Replies
View Related
Sep 16, 2007
I downloaded and installed the .msi from codeplex. After installation I open SQL Server Management Studio but don't see AdventureWorksDB in the list of databases. How is this possible? Should I not be seeing this new database? Do I have to do something else?
View 1 Replies
View Related
Dec 20, 2005
Is it a good template for, say, limited CRM applications ?
View 1 Replies
View Related
Mar 21, 2007
I'm interested in AdventureWorks querying. I thought of get a start from someone specifically on this db.
View 4 Replies
View Related
Jun 3, 2007
I am unable to attach the AdventureWorks database in management studio. I installed it when I installed SQL Server 2005 but can not find the mdf or ldf files anywhere. When I inserted the installation disks again to do a custom instal, and selected AdventureWorks, the response came back that it was already installed. What do I need to do to be able to attache this db in the management studio?
Many thanks in advance for your help.
View 9 Replies
View Related
Apr 11, 2008
Display all EmployeeName,EmployeeTitle,ManagerName,ManagerTitle.
View 9 Replies
View Related
