|
In normal circumstances, Referencial Integrity (RI) is used on table-level, by defining relationships FKs and PKs....but in special circumstances RI can be implemented at Stored-Procedure (SP) level.
Example:
In proper LDM:
table1.col1 = PK
table2.col1 = PK
table2.col2 = FK
Relationship created and its done!
In this special case:
If table1 and table2 are to linked, in SPs where-ever table1.col1 is inserted or changed or updated, table2.col2 is also changed!
The problem of this approach is, we have to keep all the relationships intact manually and there is no record of which table is related to which table!!!
So the problem is, if we have such system implementation and we dont know what relationships exist (offcourse we didnt implement the system) how can we find it out?
Can we do that finding the update on each table in each SP code?
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
If this is SQL server, you could use sp_depends on each table to get a list of the stored procedures that reference each table, but still a lot of manual work to determine if the reference is RI or not.
No design documents I guess.
|
|
|
|
|
Thanks for the reply! Very right, no documentation!
Can you please drill-down on manual work? Like what sort of work!  I am willing to do that but I am out of ideas (reasonable ideas i.e.)!
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Hi all,
Is there anyway to start Syschronizing process from SQL Query ?
I'm using SQL 2000 Merge replication and Synchronizing time is 11:00 AM (Once A Day).
I'm looking for some ways to use SQL query to do synchronizing.
Can somebody help me , please ?
Thanks
Richard
|
|
|
|
|
SQL Server has system stored procedures which allow you to script anything you need to in order to automate replication: http://msdn2.microsoft.com/en-us/library/ms147302.aspx[^]
It's all in BOL. The above link is a good place to start, it even has a script in it which starts merge replication. But I suggest you read through the docs to determine exactly the settings you want to use.
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
Dear Mark,
Thanks a lot. That's exactly what I am looking for.
Thanks again.
Cheers !
Richard
|
|
|
|
|
Anybody who worked on sql server reporting, can you please tell me how to improve performance of any report.
Thanks & Regards
Mishra
|
|
|
|
|
Your question is a little too broad for anyone to be able to answer. The performance of your reports is directly coupled to the performance of the queries used to supply the data for your reports. So the improve the performance of any report you could tune your query.
If you've already tuned your query look into the administration options which allow you to schedule the report to run every so often and cache the output, for example you could have it run every night. As long as you don't need real-time data, which most reports don't absolutely need, then your users will not have to wait so long for the report and it won't bring down your database when you have multiple users running the same report.
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
I m using this SP in SQL 2005 and Getting this error plz help me
Warning: Null value is eliminated by an aggregate or other SET operation
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[systek_salesRpt]
@username varchar(50) = NULL,
@RoleID int = NULL,
@ItemTitle varchar(50) = NULL,
@jumpid int
AS
BEGIN
CREATE TABLE #saletemp
(
SubscriptionID int,
Noofsales int,
subscriberID int ,
OwnerID int,
Rolename varchar(50),
Type varchar(50),
Itemid int,
Totalprice float,
Commission float,
RoleID int,
ItemTitle varchar(50),
CreatedDate DateTime,
Username varchar(50)
)
INSERT INTO #saletemp(SubscriptionID,Noofsales,subscriberID,OwnerID,Rolename,Type,Itemid,Totalprice,Commission,RoleID,ItemTitle,CreatedDate,UserName)
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(D.VideoID,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
D.VideoTitle as ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'videos')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='videos')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
INNER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleName,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,A.RoleID,B.CreatedDate,H.UserName
UNION ALL
------------------------------------------------------------------series-------------------------------------------------------------------------
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(E.SeriesId,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
E.seriesTitle as ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'series')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='series')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
LEFT OUTER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleName,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,A.RoleID,E.seriesTitle,B.CreatedDate,H.userName
UNION ALL
-----------------------------------------------------------group id----------------------------------------------------------------------------
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(F.GroupID,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
F.GroupName AS ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'Groups')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='Groups')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
LEFT OUTER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleName,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,C.RoleID,A.RoleID,F.GroupName,B.CreatedDate,H.username
----------------------------------------------------removing duplicate subscrptionid---------------------------------------------------------
DECLARE @Count int
DECLARE @SubsCriptionID AS int
DECLARE vendor_cursor CURSOR
FOR
SELECT SubscriptionID,Count(SubscriptionID) - 1 FROM #saletemp Where RoleName <> 'smartIcast' GROUP BY SubscriptionID HAVING Count(subscriptionid) > 1
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @SubsCriptionID,@Count
WHILE @@FETCH_STATUS = 0
BEGIN
SET ROWCOUNT @Count
DELETE FROM #saletemp WHERE #saletemp.SubscriptionID = @SubsCriptionID
SET ROWCOUNT 0
FETCH NEXT FROM vendor_cursor INTO @SubsCriptionID,@Count
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor
----------------------------------------------------------------------------------------------------------------------------------------------
IF @jumpid = 0
BEGIN
SELECT subscriptionid,noofsales,subscriberid,ownerid,Rolename,Type,itemID,
'$' + (CAST(ROUND(TotalPrice,2) AS Varchar)) AS TotalPrice,
'$' + (CAST(ROUND(Commission,2) AS Varchar)) AS Commission,
RoleID,ItemTitle,CreatedDate,UserName
FROM #saletemp
END
IF @jumpid = 1
BEGIN
SELECT subscriptionid,noofsales,subscriberid,ownerid,Rolename,Type,itemID,
'$' + (CAST(ROUND(TotalPrice,2) AS Varchar)) AS TotalPrice,
'$' + (CAST(ROUND(Commission,2) AS Varchar)) AS Commission,
RoleID,ItemTitle,CreatedDate,UserName
FROM #saletemp
Where
RoleID = @RoleID
OR
ItemTitle LIKE '%'+ @ItemTitle + '%'
OR
username LIKE '%' + @username + '%'
END
DROP TABLE #saletemp
END
|
|
|
|
|
hyderalishah wrote: Warning: Null value is eliminated by an aggregate or other SET operation
This is a warning, notan error. It is because you are doing calculations on columns which can be null. It is warning you that those values (i.e. the null) will not be included. For sum(), count() and so on this is nt usually important, but it will be gor avg etc.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I created a stored procedure in MySQL which returned one row in the result set. Since there is only one row in the table, this it what was supposed to happen. I had to add anouther column to the table, which I did without any problem. Then I modified the SELECT...FROM...WHERE query to include the column, no errors so it should be working correctly. The problem is that now it is returning 25 rows in the result set, all containing the same information. If I add another row to the table, it returns 50 rows - 25 copies of each row in the table.
How is that possible?
INTP
"Program testing can be used to show the presence of bugs, but never to show their absence."Edsger Dijkstra
|
|
|
|
|
Can we see your query?
I Love T-SQL
|
|
|
|
|
|
WHERE locations.id = _locationID
This _locationID is parameter? If it is not parameter then must be table and u must specify column like :
WHERE locations.id = _locationID.Column
Also can u add in WHERE clause column destiny with any condition?
I Love T-SQL
|
|
|
|
|
Sorry I had to delete that mess. The 5 WHERE id compares and 5 SELECT IF() were the cause of the 25 returns - 5 x 5. The solution I came up with was to LEFT JOIN the series of required rows and remove the comparisons from the WHERE.
FROM locations,
loc_staticdata,
loc_staticdata_waters AS sw
LEFT JOIN textures AS d1 ON sw.surfaceTextureID = d1.id
LEFT JOIN textures AS d2 ON sw.shoreTextureID = d2.id
LEFT JOIN textures AS d3 ON sw.envMapOverTextureID = d3.id
LEFT JOIN textures AS d4 ON sw.envMapUnderTextureID = d4.id
LEFT JOIN textures AS d5 ON sw.specularMapTextureID = d5.id
WHERE locations.id = _locationID
AND locations.staticDataID = loc_staticdata.id
AND sw.staticDataID = loc_staticdata.id;
I am not sure if it is the best solution, but it works.
Thanks for the attempt. 
INTP
"Program testing can be used to show the presence of bugs, but never to show their absence."Edsger Dijkstra
|
|
|
|
|
I am glad that u found solution, the most important is to work your query, the performance gonna be second with which we have to lookup...
Happy query-ing... 
I Love T-SQL
|
|
|
|
|
hi!
I am having problem with my logic. i have set of records like below
id name num
100 A 1
100 A 2
100 A 3
100 A 4
100 A 46
100 A 47
200 A 3
200 A 4
200 A 5
300 A 1
the data is already ordered by id, name and num. I need to find min(num) and max(num) and group the records for serialised num's for example above record set should be transformed to 4 records as
id name num_min num_max
100 A 1 4
100 A 46 47
200 A 3 5
300 A 1 1
I am having really difficult time as in how my while/if else should work.
I am trying to solve using temp tables/while loop/if else statement. I am not allowed to use cursors. could someone help me how should i do it.
I put all the data in a temp table #tempsrc
set rowcount 1
get all 4 values in a variable
@id, @name, @num
delete the record
if @num+1<> select num from #tempsrc
begin
delete record
end
else
begin
end
while....
if
here is where my problems are could someone point me to right direction
many thanks in forward.
|
|
|
|
|
It doesn't matter what order they are in as long as you are not worried about holes in the order. This is a simple GROUP BY query.
SELECT
id,
name,
MIN(num) AS firstNum,
MAX(num) AS lastNum
FROM
MyTable
GROUP BY
id,
name
|
|
|
|
|
holes in the order is what i am worried about. if its
100 A 1
100 A 2
100 A 3
100 A 4
100 A 46
100 A 47
as mentioned the min and max shouldn't be 1 and 47
it should produce two record records with min max 1,4 and 46,47 respectively.
i thought it was clear what i wanted from my earlier post.
thanks
|
|
|
|
|
if your data looks like this?
100 A 1<br />
100 A 2<br />
100 A 3<br />
100 A 4<br />
100 A 45<br />
100 A 46<br />
100 A 47
then your result have to be like this?
100 A 1<br />
100 A 4<br />
100 A 45<br />
100 A 47
I Love T-SQL
|
|
|
|
|
no i have to have seperate column so its
100 A 1 4
100 A 45 47
for
100 A 1
100 A 4
100 A 45
100 A 47
|
|
|
|
|
If you are worried about the holes then just use correlated subqueries to find the ranges. A WHERE clause would be a pain since you would end up with a tremendous set of loopped queries if the data was of any real size. See if you can grasp this nasty beast:
SELECT
ID,
Name,
num AS StartNumber,
(SELECT
min(num)
FROM
(SELECT
ID,
name,
num
FROM
TestTable b
WHERE
NOT EXISTS (SELECT
*
FROM
TestTable
WHERE
ID = a.ID AND
Name = a.Name AND
num = b.num+1)) tmp
WHERE
num >= a.num) AS EndNumber
FROM
TestTable a
WHERE
NOT EXISTS (SELECT
*
FROM
TestTable
WHERE
ID = a.ID AND
Name = a.Name AND
num = a.num-1)
I tested it with your data set so it works with SQL 2000. Be sure to change the table name (TestTable) and the column names if your database is set up as case sensitive.
The concept comes from finding the start numbers:
SELECT
ID,
name,
num
FROM
TestTable a
WHERE
NOT EXISTS(SELECT * FROM TestTable WHERE
ID = a.ID AND Name = a.Name AND
num = a.num-1)
and the last numbers:
SELECT
ID,
name,
num
FROM
TestTable b
WHERE
NOT EXISTS(SELECT * FROM TestTable WHERE
ID = b.ID AND Name = b.Name AND
num = b.num+1)
You just have to find a way to put the two queries together in a neat correlated package.
|
|
|
|
|
thank you so much for your response. i tried to use this concept in my query at the end i do a group by id,name and num but it takes really long time to do an insert to a table. could you please tell me why its taking such a long time than normal group by query.
thanks
|
|
|
|
|
If you are running this on a large table, you will force a table scan for each portion of the query. Highly inefficient! If this query is run each time you insert a record, you will need to support it with an index.
CREATE INDEX MyIndexName
ON MyTableName
(
id,
name,
num
)
This will slightly slow down a direct INSERT/UPDATE/DELETE but, it should greatly speed up the query to find the holes. This will net you a significant gain if you are running the query on every INSERT.
|
|
|
|
|
Hi, thanks for looking
Im developing a website
On a page i have a listing of products
Products are returned by a really anidated query:
1. I have a view that returns products (Sorted by name)
2. A function then takes this results and makes some calculations and returns a table again (This function is called by many stored procedures and this is the cause of anidated query, not that i like to anidate)
3. The table is then filtered with where clauses by a stored procedure wich is the one that return results to a sqldatasource
My db has a collation of Traditional_Spanish_CI_AS
I added asp.net membership wich has a collation of Modern_Spanish_CI_AS
When i generate the scripts and try to execute them on the remote server, i get an error that says collation conflict, So i tried removing all collations and i executed the scripts succesfully
But now, i have that sorting problem
What could be the cause of this?
Thanks in advance
Alexei Rodriguez
modified on Thursday, April 17, 2008 8:37 PM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
