EXPERT: Implement Dijkstra's Algorithm -&> Need Lots Of Help Implementing!

Jan 9, 2008

This is such a complex question and I'm 99.9% sure it requires usage of Dijkstra's algorithm in order to determine the shortest path. :(

I have tried to build this myself (yes, I've viewed enough examples on the web, but since they dont exactly do what I want AND I'm rather new to this advanced SQL AND my boss would really like this asap I feel forced to call upon the community)

Basically I need a query which analyzes the relationships between 2 persons and returns the shortest path(S!) I have provided the data that is required to perform any tests below. The example I provide match with the given data.

I know for sure that such a query has been written before since for example LinkedIN uses something similar...so if anyone has this off the shelf for me great!
If not, I would really really appreciate it if someone could provide a completely worked out example. I'll even give special thanks to that person on our future website :)
So, many thanks ahead for whoever takes up this challenge! :)

CASE:
-----------------------------------------------------------------------------
I have tables with friend relationships and tables with userdata.

Lets say im logged in as Peter (usercode 5).
Now if I (as user Peter) view the profile of Andre (usercode 51), I want to determine the relationship that exists between me and Andre.

When the users would have a direct relationship, eg. between Peter (5) and John (6)  I want returned:
col1 col2 col3     col4
5     Peter 6     John

When the users would have a indirect relationship, witch EXACTLY 1 person in between, like between John (6) and Jack (48).
So I can go from John to Jack in exactly 2 steps via multiple persons, in this case I want the following rows returned (max 4):
col1 col2 col3     col4 col5     col6
6     John 11     Hans 48     Jack
6     John 15     Hans 48     Jack

When the users would have a indirect relationship, witch MORE than 1 persons in between, like between Peter (5) and Andre (48), I want returned:
col1 col2 col3     col4 col5     col6 col7    col8
5     Peter 11     Hans 48     Jack 51     Andre

In any case when there are multiple paths from person A to person B, I only want the shortest paths returned to a maximum of 4
Since this query will be called may times by different users at the same time concurrency issues also need to be taken into account (e.g. usage of temp tables)

with the entire query the maximum amount of steps that should be checked is 6, so maximum 6 persons in between 2 persons.
So if a viewed user is more than 6 steps away from the viewing user I want no results returned.
E.g. when Peter (5) views the profile of Simon (7), no relationship exists through any other person, and an empty dataset should be returned.

-----------------------------------------------------------------------------

I have the following tables and data:

CREATE TABLE [dbo].[tblFriends](
    [UserCodeOwner] [int] NOT NULL,
    [UserCodeFriend] [int] NOT NULL,
    [createdate] [datetime] NOT NULL CONSTRAINT [DF_tblFriends_createdate]  DEFAULT (getdate())
) ON [PRIMARY]

CREATE TABLE [dbo].[tblUserData](
    [UserID] [uniqueidentifier] NOT NULL,
    [UserCode] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [DisplayName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

) ON [PRIMARY]

 INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (5,'peter',':-D Peter ;-)')
 INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (6,'john','J ;-)')
 INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (7,'simon','Simon :-D')
 INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (11,'hans','Hans :-)')
 INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (15,'Jane','Jane3')
 INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (28,'jean','jean')
 INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (48,'Jack','Jack')
 INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (51,'Andre','Andre')

 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (5,11)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (5,6)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (6,11)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (6,5)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (6,15)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (7,28)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (11,6)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (11,5)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (11,15)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (11,48)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (15,6)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (15,11)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (15,48)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (28,7)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (48,11)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (48,51)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (48,15)
 INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (51,48)
 
 

View 2 Replies


ADVERTISEMENT

Dijkstra's Shortest Path Algorithm

Jan 8, 2007

Here it is, the long lasted algorithm I promised.., -- delete previous map
exec dbo.uspdijkstrainitializemap

-- create a new map
exec dbo.uspdijkstraaddpath 'a', 'b', 4
exec dbo.uspdijkstraaddpath 'a', 'd', 1
exec dbo.uspdijkstraaddpath 'b', 'a', 74
exec dbo.uspdijkstraaddpath 'b', 'c', 2
exec dbo.uspdijkstraaddpath 'b', 'e', 12
exec dbo.uspdijkstraaddpath 'c', 'b', 12
exec dbo.uspdijkstraaddpath 'c', 'f', 74
exec dbo.uspdijkstraaddpath 'c', 'j', 12
exec dbo.uspdijkstraaddpath 'd', 'e', 32
exec dbo.uspdijkstraaddpath 'd', 'g', 22
exec dbo.uspdijkstraaddpath 'e', 'd', 66
exec dbo.uspdijkstraaddpath 'e', 'f', 76
exec dbo.uspdijkstraaddpath 'e', 'h', 33
exec dbo.uspdijkstraaddpath 'f', 'i', 11
exec dbo.uspdijkstraaddpath 'f', 'j', 21
exec dbo.uspdijkstraaddpath 'g', 'd', 12
exec dbo.uspdijkstraaddpath 'g', 'h', 10
exec dbo.uspdijkstraaddpath 'h', 'g', 2
exec dbo.uspdijkstraaddpath 'h', 'i', 72
exec dbo.uspdijkstraaddpath 'i', 'f', 31
exec dbo.uspdijkstraaddpath 'i', 'j', 7
exec dbo.uspdijkstraaddpath 'i', 'h', 18
exec dbo.uspdijkstraaddpath 'j', 'f', 8

-- resolve route
exec dbo.uspdijkstraresolve 'a', 'i'This is the outputFromToCost
----------
ab 4
bc 6
cj18
jf26
fi37

Peter Larsson
Helsingborg, Sweden

View 20 Replies View Related

How To Implement Alter Database Implement Restrictions On SQL2K ?

Dec 28, 2007

Hi Guyz

it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ?
Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2

"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:

Explicitly denying the ALTER DATABASE statement.


A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.


If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."

thanks in advance.

View 4 Replies View Related

Dijkstra's Algoritm Problem

Mar 5, 2007

I want to make it possible that I can see the path from one user to another and via whom the route went:I want to pass the startnode and the endnode and then retreive the route between them.In case I go from 5 to 15 (see table below), I want to have the following returned:5-6-155-7-15In case I go from 5 to 18 I want to have returned:5-6-15-9-185-7-15-9-18always up to a maximum distance of 6 steps.I have the following table definitiontblFriendsOwnerCode  intFriendCode intthis table contains content like:



5
6

5
11

5
7

6
5

6
12

6
15

6
11

7
15

7
5

9
15

9
18

11
5

11
6

12
6

13
6

15
6

15
7

15
9

18
9
As you can see, I store each relationship twice.I have created the following SP to retreive the info, but dont know how I can get from these results to the results I desire as described above....STORED PROCEDURE
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo
ALTER PROCEDURE [dbo].[myspShortestPath]--shortest path based on algorythm of Dijkstra @ViewingUserCode int, @ViewedUserCode int, @MaxDistance int=100, @MinDistance intASBEGIN
    -- Automatically rollback the transaction if something goes wrong.        SET XACT_ABORT ON        BEGIN TRAN        -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;
    -- Create a temporary table for storing the estimates as the algorithm runs    CREATE TABLE #UserList    (        UserCode Int NOT NULL,    -- The City Id  UserName nvarchar(50),        Estimate Int NOT NULL,    -- What is the distance to this city, so far?        Predecessor nvarchar(max),    -- The city we came from to get to this city with this distance.  PredecessorCodes nvarchar(max),        Done bit NOT NULL        -- Are we done with this city yet (is the estimate the final distance)?    )
    -- Fill the temporary table with initial data    INSERT INTO #UserList (UserCode, UserName, Estimate, Predecessor,PredecessorCodes, Done)    SELECT UserCode, UserName, 2147483647, '', '', 0 FROM tblUserData
    -- Set the estimate for the city we start in to be 0.    UPDATE #UserList SET Estimate = 0 WHERE UserCode = @ViewingUserCode    IF @@rowcount <> 1    BEGIN        RAISERROR ('Couldn''t set start user', 11, 1)         ROLLBACK TRAN                RETURN    END
    DECLARE @FromUser Int, @CurrentEstimate Int,@FromUserName nvarchar(50)
    -- Run the algorithm until we decide that we are finished    WHILE 1=1    BEGIN        -- Reset the variable, so we can detect getting no records in the next step.        SELECT @FromUser = NULL
        -- Select the UserCode and current estimate for a city not done, with the lowest estimate.        SELECT TOP 1 @FromUser = UserCode, @FromUserName = UserName, @CurrentEstimate = Estimate        FROM #UserList WHERE Done = 0 AND Estimate < 2147483647                        -- Stop if we have no more unvisited, reachable cities.        IF @FromUser IS NULL BREAK
        -- We are now done with this city.        UPDATE #UserList SET Done = 1 WHERE UserCode = @FromUser
        -- Update the estimates to all neighbour cities of this one (all the cities        -- there are roads to from this city). Only update the estimate if the new        -- proposal (to go via the current city) is better (lower).        UPDATE #UserList SET #UserList.Estimate = @CurrentEstimate + 1,            --keep the other predecessors as well   #UserList.Predecessor = replace(@FromUserName, ' ', '')+','+#UserList.Predecessor,   #UserList.PredecessorCodes = replace(str(@FromUser), ' ', '')+','+#UserList.PredecessorCodes        FROM #UserList INNER JOIN tblFriends ON #UserList.UserCode = tblFriends.UserCodeFriend        WHERE tblFriends.UserCodeOwner = @FromUser AND (@CurrentEstimate + 1) <= #UserList.Estimate     END   
    SELECT ud1.UserName AS UserNameFriend,ud1.UserCode, Estimate AS Distance, PredecessorCodes, Predecessor  FROM #UserList    INNER JOIN tblUserData ud1 ON #UserList.UserCode = ud1.UserCode WHERE #UserList.Estimate<=@MaxDistance AND #UserList.Estimate>=@MinDistance ORDER BY Distance ASC        -- Drop the temp table.    DROP TABLE #UserList        COMMIT TRAN
END
 

View 2 Replies View Related

SQL PLUS Expert

Jun 14, 2002

I am looking into a position requiring "SQL PLUS Expert" - anbody out there heard of this - is it a querying tool a la query analyzer of PL SQL.

View 2 Replies View Related

SQL PLUS Expert

Jun 14, 2002

I am looking into a position requiring a "SQL PLUS Expert" - anybody out ther heard of this - is it a querying tool a la query analyzer or PL SQL ?

View 1 Replies View Related

SQL Expert Please Help!!

Dec 11, 2006

Hello,

Lets just say that I have really only logged into phpmyadmin once and messed up my forum.

What I was trying to do? Install a shoutbox (chatroom type software).
When I logged into phpmyadmin I clicked the phpbb_config tab on the left hand side. Then I clicked the SQL tab and pasted the code required for the shoutbox accordingly and hit the "Go" button. But what I did next is what created problems.

I thought I may have put extra spaces or loaded the code wrong in the SQL area because the shoutbox did not work properly after everything was installed. So while in phpmyadmin and after clicking phpbb_config agian, instead of hitting SQL, I hit the "Empty" button thinking that I could just erase what I had previously inserted and then try inserting it again to make sure the code was inserted properly.

If you havn't figured it out by now, I clearly don't have a clue what I am doing, what exactly I did, or what I can do to fix it.

Click on the link to see the error..

http://www.cflzone.com/forum/index.php

Can someone that knows what they are doing please help me???

thanks,

- Reider

View 3 Replies View Related

Need Expert Help And Advice. Thank You.

Feb 27, 2007

Hello,Consider I have a String:Dim MyString As String = "Hello"or an Integer:Dim MyInteger As Integer = 100or a class which its properties:Dim MyClass As New MyCustomClass
MyClass.Property1 = "Hello"
MyClass.Property2 = Unit.Pixel(100)
MyClass.Property3 = 100Or even a control:Dim MyLabel As Label
MyLabel.Id = "MyLabel"
MyLabel.CssClass = "MyLabelCssClass" Is there a way to save a String, an Integer, a Boolean, a Class, a Control in an SQL database?Something like:  Define something (Integer, String, Class, Control, etc)  Save in SQL 2005 Database  Later in code Retrive from database given its IDIs this possible?How should I do this?What type of SQL 2005 table field should be used to store the information?Thanks,Miguel 

View 1 Replies View Related

Expert Help Needed

Feb 20, 2001

Has anyone any suggestions how I might find the most recent of a series of dates from different tables? I am joining tables which all have a date_altered field and I am only interested in displaying the most recent of these. Eg.

select a.x,b.y,c.z, a.date_altered, b.date_altered, c.date_altered
from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)

What I would like is in effect a function Highest() Eg.

select a.x,b.y,c.z, highest(a.date_altered, b.date_altered, c.date_altered) as last_alteration
from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)

I am using SQL Server 7 so cannot write myself a function to achieve this - frustrating, as it is something I could have done 4 years ago when I used mostly Oracle.
Many thanks

View 2 Replies View Related

Need SQL-EXPERT, For My Problem

Jul 12, 2004

Fact is:

I have two tables Orderposreg and Temp1 (both in [My Database]), Orderposreg is from 1994, Temp1 from 1995 and i need to Update the old table with the new one.

This should my Query do: I need it to Update old primary keys, or if the row does not exist to insert the new primary key into the Orderposreg.

But if I start the Query i get this failure message:
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Temp1' does not match with a table name or alias name used in the query.

It seems that the colums do not have the same name, but the column of both tables have exactly the same column name and data type just the table name is different.

For your information the whole Code:


Code:


declare @error varchar, @rowcount varchar

select count(*) from dbo.temp1
if (@@error <> 0)
begin
Print 'An error occurred reading the temporary import table'
goto exitpoint
end

begin transaction

update dbo.Orderposreg
set dbo.Orderposreg.Ordernr = dbo.Temp1.Ordernr,
dbo.Orderposreg.Pos = dbo.Temp1.Pos,
dbo.Orderposreg.Produktnr = dbo.Temp1.Produktnr,
dbo.Orderposreg.Artbenämn = dbo.Temp1.Artbenämn,
dbo.Orderposreg.Artikelgrupp = dbo.Temp1.Artikelgrupp,
dbo.Orderposreg.Förs_pris = dbo.Temp1.Förs_pris,
dbo.Orderposreg.Kvant = dbo.Temp1.Kvant,
dbo.Orderposreg.Total_Intäkt = dbo.Temp1.Total_Intäkt,
dbo.Orderposreg.SSort = dbo.Temp1.Sort,
dbo.Orderposreg.Datum = dbo.Temp1.Datum,
dbo.Orderposreg.Utskriven = dbo.Temp1.Utskriven,
dbo.Orderposreg.Levtid_Ö = dbo.Temp1.Levtid_Ö,
dbo.Orderposreg.Levtid_L = dbo.Temp1.Lev_kvant,
dbo.Orderposreg.Inköpskostnad = dbo.Temp1.Inköpskostnad,
dbo.Orderposreg.Vikt_tol = dbo.Temp1.Vikt_tol,
dbo.Orderposreg.AntalSt = dbo.Temp1.AntalSt,
dbo.Orderposreg.Spec_nr = dbo.Temp1.Spec_nr,
dbo.Orderposreg.Spec_utgåva = dbo.Temp1.Spec_utgåva,
dbo.Orderposreg.ID_Beräknad = dbo.Temp1.ID_Beräknad,
dbo.Orderposreg.Bredd = dbo.Temp1.Bredd,
dbo.Orderposreg.Tjocklek = dbo.Temp1.Längd,
dbo.Orderposreg.ValsnGrad_kod = dbo.Temp1.ValsnGrad_kod,
dbo.Orderposreg.Kant_Kod = dbo.Temp1.Kant_Kod,
dbo.Orderposreg.Planhets_kod = Temp1.Yt_kod,
dbo.Orderposreg.LevForm_kod = dbo.Temp1.LevForm_kod,
dbo.Orderposreg.Rakhets_kod = dbo.Temp1.Rakhets_kod,
dbo.Orderposreg.BreddTol_kod = dbo.Temp1.Breddtol_kod,
dbo.Orderposreg.TjocklTol_kod = dbo.Temp1.TjockTol_kod,
dbo.Orderposreg.LängdTol_kod = dbo.Temp1.LängdTol_kod,
dbo.Orderposreg.Stål_kod = dbo.Temp1.Stäl_kod,
dbo.Orderposreg.TotaltSek = dbo.Temp1.TotaltSek,
dbo.Orderposreg.Tullstatnr = dbo.Temp1.Tullstatnr,
dbo.Orderposreg.Fakturera = dbo.Temp1.Fakturera,
dbo.Orderposreg.Leveransstatus = dbo.Temp1.Leveransstatus,
dbo.Orderposreg.Momsbelopp = dbo.Temp1.Momsbelopp,
dbo.Orderposreg.Total_inkl_moms = dbo.Temp1.Total_inkl_moms,
dbo.Orderposreg.KloMPS = dbo.Temp1.KloMPS,
dbo.Orderposreg.ShopFloor = dbo.Temp1.ShopFloor,
dbo.Orderposreg.Status = dbo.Temp1.Status,
dbo.Orderposreg.Stålkod = dbo.Temp1.Stålkod,
dbo.Orderposreg.Kontonyckel = dbo.Temp1.Kontonyckel,
dbo.Orderposreg.PlanLevDat = dbo.Temp1.PlanLevDat,
dbo.Orderposreg.Legtillägg = dbo.Temp1.Legtillägg,
dbo.Orderposreg.StålGrp = dbo.Temp1.StålGrp,
dbo.Orderposreg.KundNr = dbo.Temp1.KundNr,
dbo.Orderposreg.FKundNr = dbo.Temp1.FKundNr,
dbo.Orderposreg.Godsmärke = dbo.Temp1.Godsmärke,
dbo.Orderposreg.LagerMtr = dbo.Temp1.LagerMtr,
dbo.Orderposreg.Sortkvant = dbo.Temp1.Sortkvant,
dbo.Orderposreg.Sortpris = dbo.Temp1.Sortpris,
dbo.Orderposreg.KundsProdNr = dbo.Temp1.KundsProdNr,
dbo.Orderposreg.Godsmärke2 = dbo.Temp1.Godsmärke2,
dbo.Orderposreg.RegDatum = dbo.Temp1.RegDatum,
dbo.Orderposreg.PlanBetDag = dbo.Temp1.PlanBetDag,
dbo.Orderposreg.Säkring = dbo.Temp1.Säkring
where dbo.Orderposreg.Ordernr_o_pos = dbo.Temp1.ordernr_o_pos

select @error = @@error,@rowcount=@@rowcount
if @error <> 0
begin
print ' Error updating - Error number '+@error+'. Rolling back'
--this reverses your updates
rollback
--this jumps to the end
goto exitpoint
end

print @rowcount+' rows updated'

insert into dbo.Orderposreg
(Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring)
select
Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring
from dbo.Temp1
where dbo.Temp1.Ordernr_o_pos not in (select Ordernr_o_pos from dbo.Orderposreg)
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
begin
print ' Error inserting - Error number '+@error
print ' Rolling back updates and inserts'
--this reverses your updates and inserts
rollback
--this jumps to the end
goto exitpoint
end

print @rowcount+' rows were inserted'
--this saves your data
commit

exitpoint:




Thanks a million for somebody who helps me!!!

View 2 Replies View Related

Help Me Please ! My Question For An Expert !

Mar 16, 2004

Hi !
I have been worked with VC++, MS SQL SERVER, Transact-SQL for
3 years. I made an axtended stored procedure (xp_test) which returns
an recordset.
From Query Analizer, I can see the recordest : exec xp_test

I want to make an User Defined Function - MyTest which return
the recordset that it is returned by xp_test after its execution.
Something like that :

CREATE function dbo.MyTest ( )
RETURNS @table ...
AS
BEGIN
exec master.. xp_test table1 output -- can I do this ?

RETURN table1
END

Table and table1 are the same design.

Thank you very much !

View 4 Replies View Related

Need Expert Help And Advice. Thank You.

Feb 27, 2007

Hello,

I am using Enterprise Library Data Access and SQL 2005 application block and I need to know if it is possible to do the following:

Consider I have a String:

Dim MyString As String = "Hello"

or an Integer:

Dim MyInteger As Integer = 100

or a class which its properties:

Dim MyClass As New MyCustomClass
MyClass.Property1 = "Hello"
MyClass.Property2 = Unit.Pixel(100)
MyClass.Property3 = 100

Or even a control:

Dim MyLabel As Label
MyLabel.Id = "MyLabel"
MyLabel.CssClass = "MyLabelCssClass"

Is there a way to save a String, an Integer, a Boolean, a Class, a Control in an SQL database?

Something like:

Define something (Integer, String, Class, Control, etc)

Save in SQL 2005 Database

Later in code Retrive from database given its ID

Is this possible?

How should I do this?

What type of SQL 2005 table field should be used to store the information?

Thanks,
Miguel

View 4 Replies View Related

Is Ther An Expert?

Oct 16, 2007

Is there a expert here on full text indexing on 2005 sql server express????

View 8 Replies View Related

What Is Expert SQL Knowledge?

Jul 23, 2005

I do a lot of hiring for my company and a lot of the people I interviewsay that they are experts at SQL queries, but when I give them somethingsimple just beyond the typical SELECT type of queries, they choke.For example I have a table that looks like this:PK_ID - primary keyPARENT_ID - a FK to another row in the same tableThis essentially is a tree structure. I will ask interviewees to writea query that will return all the rows that have 2 direct children. Noone knows how to do this.I don't get it. I have done queries which boggle the mind and they arefar more complex than this.Am I asking too much?--* Don VaillancourtDirector of Software Development**WEB IMPACT INC.*phone: 416-815-2000 ext. 245fax: 416-815-2001email: Join Bytes! <mailto:donv@webimpact.com>web: http://www.web-impact.com/ This email message is intended only for the addressee(s)and contains information that may be confidential and/orcopyright. If you are not the intended recipient pleasenotify the sender by reply email and immediately deletethis email. Use, disclosure or reproduction of this emailby anyone other than the intended recipient(s) is strictlyprohibited. No representation is made that this email orany attachments are free of viruses. Virus scanning isrecommended and is the responsibility of the recipient./

View 15 Replies View Related

I Need Suggestions From Some Expert

Jan 28, 2008

Hi folks, I have a very typical database for an ASP.net application. There is a table which will contain a hierarchical data..much like files-folders structure of a file system.
But we know that the table will be a giant one in production. There will be a huge collection of data need to persist in it. we are already facing some performance problem with some queries during the QA/test machine.
Currently there is a table which is keeping all file and folder information and another table maintaing their hierarchy relation using two column namely, parentID and childID.
My first question is, would it be better to keep this hierarchy relation into the same table rather using a different one? (much like managerID and empID in AdventureWorks sample?)
My Second question, what is the best way to design this kind of structure to get the highest performance benifit?

All kind of thoughts will be appreciated much! thanks

View 26 Replies View Related

Expert Sql Query

Oct 26, 2007



Hi,

I have a table describing items with 3 rows:
ID : its an identity Primary key
Type : nvarchar string for itemtype
ItemData : numeric data for an item

I would like to create a query to get the results where each returned row contains
- Type
- number of items for the given type
- concatenated string of the ItemData numbers for all items for the given type

ex: Items
1,"Big",1
2,"Big",56
3,"Small",45
4,"Big",22
Expected result:
"Big",3,"1 56 22"
"Small",1,"45"

I started with this query:

SELECT Type , COUNT(Type) AS Amount FROM Items GROUP BY Type


but i cannot figure out how to do the string concatenation (its like SUM but we dont need to add the values but we need to concatenate). I was thinking some stored procedure but this query will run on Compact SQL so thats not possible according to Compact SQL specs. Can any SQL expert help, how to do this, or is it possible to do anyhow?

Thanks for the help.

View 1 Replies View Related

Dynamic SQL Expert Pleaaaaaaaase

Apr 15, 2004

First i am designing a small database fo similar types of books ( Stories table , Short stories table , ...etc ) so i the columns in each table of them is similar , so instead of using a Stored Procedure for every table to select the TOP book i used one SP with variable table name and it works well as i pass the table name from the page , here it is :
---------------------------------------------
CREATE PROCEDURE Top1
@tblname Varchar(255)
AS

EXEC ( 'SELECT TOP 1 *
FROM ' + @tblname + '
WHERE Status="OLD"
ORDER BY ID DESC')
GO
-----------------------------------------------
The Problem now that i want to make the same thing with the INSERT STATEMENT to input new books to the table by passing the table name from the page like the SELECT one , and i `ve tried this one but gives me errors :
--------------------------------------------------------------------------------------------------
CREATE PROCEDURE AddNewStory
@tblname varchar(50),
@title varchar(50),
@Content varchar(5000) ,
@Picture varchar(200) ,
@date datetime
AS

EXEC('INSERT INTO' + @tblname + '( Sttitle , StContent, StPic, StDate )
VALUES ( '+ @Title + ',' + @Content+ ',' + @Picture+ ',' + @date + ')' )
GO
----------------------------------------------------------------------------------------------------

& th error is

Server: Msg 128, Level 15, State 1, Line 2
The name 'Dickens' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Stored Procedure: db1sql.dbo.AddNewStory
Return Code = 0

SO PLEASE HELPPPPPPPPPPP

View 4 Replies View Related

Lecco Tech SQL Expert Pro

Jun 15, 2000

Is anybody out there using SQL Expert Pro? Can you give me some feedback please?

I've been evaluating it for two weeks now and am very pleased with the results but I'm a bit hesitant to spend $5000.

Thanks!

Pete Karhatsu

View 3 Replies View Related

Expert's Help Needed!! Sp_QueryToFile

Dec 20, 2004

Idea here is to output results of a stored proc to a text file..

I am trying to use this query to execute a stored proc which in turn accepts 3 parameters. Can some one help me as I am getting syntax errors when I try this way:

EXEC sp_querytofile 'MyDB', "exec sp_myproc ''11/03/04'', ''ABK'', ''TFC'", 'c:sp_myproc_out.txt'


I tried different combination of quotes but couldn't make it work! Any one to help?

--------code for sp_QueryToFile---------

Create PROCEDURE sp_QueryToFile
( @db sysname,
@query VARCHAR(1000),
@file VARCHAR(255)
)
AS

SET NOCOUNT ON
SET @Query = 'SET NOCOUNT ON ' + @Query

EXECUTE ('master..xp_cmdshell ''osql -w8000 -S sqldev14corpappsdev -r -s" ", -h-1 -o' + @file + ' -d'+ @db + ' -Q"' + @query + '" -E''')
/* Procedure Ends */

View 2 Replies View Related

Getting A SQL Expert To Review SQL Server And DB?

Apr 3, 2007

Hi,

I posted last year when we were having problems with a new SQL box we had moved to here...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74650

...we coded round most of the issues and deadlocks and things seemed to improve for quite a while but we have recently run into performance problems again of late.

The CPUs on our SQL box often thrash away at 100% and our ColdFusion website has begun running painfully slow.

Our developers/hosts have suggested we might need to look for a 3rd party SQL guru who could look at the SQL box, do some tracing etc. and perhaps make some recomendations around optimising the DB architecture or changing the way we run certain queries. At the moment we have only gut instinct on which bits of the DB are likely to be being hit most hard.

Our website has grown from being very small to being really quite busy and it's possible we are running into shortcomings with the design of the DB that need to be tackled before we can expand further.

I'm not sure what the protocol is (I see there is a Jobs part of the site) but I wondered about the possibility of getting one of you guys in for a short while to review our server and database, for a fee of course. I'm not sure how long it would take to review that kind of data and get a feel for the usage?

We are based in the UK and whilst I guess it could be done remotely it might be easiest if the person was UK based too.

I'm as much interested in feedback about the idea (it might be not workable/a good idea for example) as I am to people offering their services.

If this post breaks any rules please let me know.

Cheers,

Tem

View 18 Replies View Related

Need Expert Help In Database Design..

Apr 4, 2007

Hello friends,I have to design a database for banking application,but it is my first db design so i need some expert advice.

Data base has some performance constraint i.e high output ,minimum query time,need to process 2 to 5 million transaction per day.

Further specification can be revealed if appropriate help is provided.

your advice will be of great help for me.

Thx

View 4 Replies View Related

Please Help! I Have Lots Of Questions.

May 6, 2007

In case some of you have read my previous posts, you may be aware that I'm writing a webboard application as a replacement for the old one.The old one currently have approximately 50000 topics in the database, each has on average 10 replies (I just check recently. I though it was only 7000 topics).I need to provide paging and sorting feature to the topic list. But I can't just SELECT all of them and let GridView do the paging/sorting, right?I have been using stored procedures to store my SQL statement for several projects now. I know how to deal with the paging feature (ROW_NUMBER), but the sorting requires me to change to change the "ORDER BY" clause.1. Can somebody tell me how to change the ORDER BY clause in the stored procedure(s) at runtime? Or does anyone have other approach?
Currently I'm thinking about moving back from store procedures to hard-code SQL statements, and then modify/generate the SQL statement for each paging / sorting. But I've learn that stored procedures give more performance and security.2. According to the situation I provided, is it worth moving from stored procedures to hard-code SQL?I'm also using 3-tier architecture approach + OOP. But I reach a conflict in my thoughts. You see, according to OOP, I'm supposed to create classes that reflect the actual objects in the real-world, right? In my case the classes are "Board, Topic, Reply, ...." According to this and 3-tier approach, I intend to use ObjectDataSource as a bridge between Presentation Logic and Business Logic. But I wonder what my datasource class should return3. Should my data source class return data objects like1st approach[DataObject(True)]pubic class TopicDataSource{         public static Topic[] GetTopicList() { }}or should it return DataSet / DataTable / DataReader like2nd approach [DataObject(True)]public class TopicDataSource{          public static DataTable GetTopicList() {}}Personally I think approach 1 is more OOP and allow for more extendability, but approach 2 might be faster.4. If I go with approach 1, how should I control which property of my data objects is read-only after it's has been inserted/created? Can I just set my data object's property to be readonly? Or do I have to set it at page level (i.e. GridView-> Columns -> BoundField -> ReadOnly=True)? Or do I set it and the page level and write a code to throw an exception in the rare case the application / user try to change it's value? Or else?Please help. These questions slow me down for days now.If there's any concepts that I misunderstood, please tell me. I'm aware that I don't know as much as some of you.I will be extremely grateful to anyone who answer any of my questions.Thanks a lot.PS. For those who think my questions are stupid, I'm very, very sorry that I bother you.

View 3 Replies View Related

Do Lots Of COUNTs

Sep 19, 2006

Hello :)

I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?

SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL

View 8 Replies View Related

Lots Of Queries For My Db

Jul 20, 2005

Hello all,I have a database in SQL Server that should save data from a CRM-likeapplication.The database consists of tables like products, services, customers,partners etc. Problem is that the users should be able to find theseitems on different properties and with or without substring finding(SQL: LIKE). Example: I want the users to be able to find a customer,providing a customerID, but also providing a customername, zipcode orjust a part of those strings.This will result in a lot of queries. I bet there are some nicesolutions to this, since I will not be the first with this situation.If anyone can help, please.Thank you in advance.Regards,Freek Versteijn

View 3 Replies View Related

Expert Question - Combining Tables And MAX()

Jul 17, 2006

I have two tables - T_Assets and T_Inspections.
I am trying to create a view where I see the date of the last inspection for each asset. Here is some sample SQL:
SELECT T_Assets.I_AssetID, T_Inspections.I_InspectionID, Max(T_Inspections.SDT_DateOfInspection)
FROM T_Assets
INNER JOIN T_Assets.InspectionID = T_Inspections.I_InspectionsID
GROUP BY T_Assets.I_AssetID, T_Inspections.I_InspectionID
 
Now, as anyone who is experienced in sql will know, this will not work. This is because the I_InspectionID is different - so the group by will not work. The results I get are basically the same as if the MAX() function was not applied.
 
Can anyone please tell me a way around this.
Jagdip

View 8 Replies View Related

Database Flexibility (Need Expert Advice)

Mar 13, 2008

Hi All Professionals Programmers,
I would like to ask a question that is very important for me. The question is how can i create a flexible data base in which i m able to create the inner levels as much as i can.
Like i have a table building, then i have another child table floor, then the floor become parent and i have its child rooms, then the rooms become parent and i have its child floor tiles etc.
you can see i am going to inner dept, so i need a flexible database because its very costly and intimadting to change the database and every time create a new table and relationships.
Hope you have understood what i am going to say and need advice of professional and expert user to resolve it.
Any concise quality material like articles, white paper etc will also be suitable for me.
Thanks in Advance

View 3 Replies View Related

Problem With Query, Looking For An Expert Solution

Dec 22, 2005

hello My SP is as follows


CREATE PROCEDURE SP_ProcessAdminResults(@UserID nvarchar(100)) As

Declare @ReqID as integer;
Declare @Assaignee as integer;
Declare @TechName as nvarchar(200);
Declare @OpenDate as datetime;
Declare @GroupID as integer;
Declare @DateActiontaken as datetime;
Declare @AssignedDate as datetime;
Declare @ActionDays as integer;
Declare @OpenDays as integer;

declare GroupCursor cursor local scroll static for select [ReqID],[Assignee],FullName,[OpenDate],[GroupID],[DateActionTaken] from V_ProcessRequestMaster where UserID=@UserID and [Status]<>'CL' order by ReqID
open GroupCursor
fetch first from GroupCursor into @ReqID,@Assaignee,@TechName,@OpenDate,@GroupID,@Da teActiontaken
while (@@fetch_status <>-1)
begin
select @AssignedDate= min(DateLogged) from LOGMASTER where
analyst=@Assaignee and reqMasterID=@ReqID and UserID=@UserID and Type='INIT'
If @AssignedDate is null
Begin
select @AssignedDate=dbo.GetActionDate(LTRIM(Rtrim(@TechN ame)),@ReqID,@UserID)
End

If @AssignedDate is null
BEGIN
set @ActionDays=0
END
Else
BEGIN
If @GroupID=438030
BEGIN
set @ActionDays=[dbo].GetBussinessdays(@AssignedDate,@DateActiontaken,5 ,6)
set @OpenDays=[dbo].GetBussinessdays(@OpenDate,getdate(),5,6)
END
Else
BEGIN
set @ActionDays=[dbo].GetBussinessdays(@AssignedDate,@DateActiontaken,1 ,7)
set @OpenDays=[dbo].GetBussinessdays(@OpenDate,getdate(),1,7)
END

END
If @ActionDays<>0 set @ActionDays=@ActionDays-1
If @OpenDays<>0 set @OpenDays=@OpenDays-1

update requestmaster set DateAssigned=@AssignedDate, ActionDays=@ActionDays, OpenDays=@OpenDays where ReqID=@ReqID and UserID=@UserID
fetch next from GroupCursor into @ReqID,@Assaignee,@TechName,@OpenDate,@GroupID,@Da teActiontaken
end
GO


The master table contains like 600,000 odd records and when i put a cursor and process the results where i need to get the business days between assigned date and action taken date the query take lot of time and eventually the front end crashes saying Time OUT expired.

Looking for an expert solution to simplify that query.

View 6 Replies View Related

Table Structure, Need Expert Advice?

Jan 31, 2005

I have asked a similar question on SQLTemp.com, but I thought I would ask here in case there are people here that don't visit SQLTeam and can help me.

I have to tables:

tbSiteworkPriceList
ItemID | Descr | Material | Labour | Travel | Boarding
-----------------------------------------------------
1 | Finishing | 0.25 | 28 | 42 | 65

tbSiteworkCostTypes
CostTypeID | Descr
-------------------
1 | Materials
2 | Labour
3 | Travel
4 | Boarding


Now, the tabels above are a sitework price list for an estimating package. The tbSitworkCostTypes table is used to trace the value of the particular item back to an account after the item is added to a takeoff. My questions are:
1) How should I tie each items variable (Material, Labour, Travel, Boarding) to its appropriate cost type?
2) Should I divide the table tbSiteworkPriceList above into 2 tables as shown below?


tbSitworkPriceList
ItemID | Descr
1 | Finishing

tbSiteworkCostItems
fkItemID | fkCostTypeID | Price
-------------------------------------------
1 | 1 | 0.25
1 | 2 | 28
1 | 3 | 42
1 | 4 | 65


This seems to be a little better in the point of view that each item variable is linked to the appropriate cost item by joining the tables tbSiteworkCostItems to tbSiteworkCostTypes. They have to be linked because after the takeoff is generated, the dollar values have to be imported into an accounting system where the account code and cost type code are determined by the "CostTypeID". Any thoughts?

Mike B

View 1 Replies View Related

Want Expert Opinion On A Way Of Storing 'relations'

Jul 20, 2005

i've a database where relations are hold in a special way which the projectleaders think of as "performant and uncomplicated" but which is veryquestionable to me:------------------------------------------------Table [Attributes]Fields [AttributeID] and [AttributeText]Table [Objects]Fields object stuff.... and [AttributeIDs] (varchar with 0-20 ids usually)in AttributeIDs there is a backslash separted list of Attribute-IDs like'3412278'so to get 20 object with a special attribute (which we need often) we doSELECT TOP 20 *FROM ObjectsINNER JOIN AttributesON (Objects.AttributeIDs LIKE ('%' + (CAST AttributeID AS varchar) + '\%'))ORDER BY ObjectTextps: to store data we need for communication we include a dozen of fields in*every* table and its content makes about 100 bytes/record------------------------------------------------i would do this stuff with a table to store the object/attributecorrelations.could someone tell me if that stuff makes any sense to an expert and how tovaluate it in regard of performance(we have big customers where that *is* anissue), design, scalability, pragmatism and sense ;)thanks in advance,ViperDK

View 1 Replies View Related

Expert's Critique On Package Design

Aug 31, 2007

All:
This proably is an unsual request. I have developed a package that runs fine and does what it is supposed to do. I am jsut not sure if I have developed it in the most efficient way. I have several years of ETL experience but only about 6 months with SSIS. I know I can benefit a lot if I had my package reviewed by an expert.

I realize that I am asking for some time committment on your part and so would understand if I do not get any takers. But if you would like to review my package and offer suggestions on its improvement please let me know. We can work on the logistics of getting the package to you.

Thanks!

View 7 Replies View Related

Deleting Lots Of Records

May 21, 2004

Apparently, deleting 7,000,000 records from a table of about 20,000,000 is not advisable. We were able to take orders at 8:00AM, but not at 7:59.

So, what's the best way of going about deleting a large number of records? Pretty basic lookup table, no relationships with other tables, 12 or so address-type fields, 4 or 5 simple indexes. I can take it down for a weekend or night, if needed.

DTS the ones to keep to another table, drop the old and rename the new table?
Bulk copy out, truncate and bring back in?
DTS to text, truncate and import back?
Other ways?

Never worked with such a large table and need a little experienced guidance.

Thanks for the help

View 1 Replies View Related

SQL Performance- Lots Of Little Tables Or One Big One?

May 25, 2004

I am planning an application where ~1000 companies will be accessing data. Should I use a key to identify the company and place all data in one table i.e (WHERE company =123) or should the application create company specific tables i.e should I have 1000 small tables with 100 records in each, or one table with 100,000 records?

View 2 Replies View Related

Does Sql Server Slow Down If It Has Lots Of Db's?

May 7, 2008

Hi,

You know how there are lots of hosted applications out there, many of them provide you with your own database (not shared).

1. If a server has 1K databases on it, will this slow down the server just due to the # of databases? (each user has their own database, but they won't be accessing it that much really).

A seperate database is required for security purposes usually.

2. Can you still open up EM with 1K+ databases?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved