SQL Server equivilent of these queries? 1

[jgoodman00]

I am attempting to use SQL server for the first time, & it is proving somewhat challenging!
I am under the impression that SQL server does not support append, delete or update queries, & that you need to use stored procedures to accomplish these in SQL server. Can anyone also recommend any online sources about SQL server so that I can at least go about learning the basics of this....

First Query: (Select Query)
SELECT DISTINCTROW PNAMSTR.*, tblHierarchy.Selected, InStr([tblHierarchy].[priority],[tblAdopt].[priority]) AS Expr1, tblAdopt.priority
FROM (tblHierarchy INNER JOIN PNAMSTR ON tblHierarchy.HierarchyID = PNAMSTR.HierarchyID) INNER JOIN tblAdopt ON PNAMSTR.RefNo = tblAdopt.RefNo
WHERE (((tblHierarchy.Selected)=Yes) AND ((InStr([tblHierarchy].[priority],[tblAdopt].[priority]))>0));

Second Query: (Append Query)
INSERT INTO RESULTS ( MSlink, x, y, max_x, max_y, RefNo, brsno, road, priority )
SELECT DISTINCTROW tblAdopt.new_mslink, tblAdopt.x, tblAdopt.y, tblAdopt.max_x, tblAdopt.max_y, Pnamstr.RefNo, tblAdopt.brsno, tblAdopt.road, tblAdopt.priority
FROM ((Pnamstr INNER JOIN Select_Features ON Pnamstr.MasterID = Select_Features.MasterID) INNER JOIN (tblAdopt INNER JOIN [Select_Features - BRSCode] ON tblAdopt.brsno = [Select_Features - BRSCode].brsno) ON Pnamstr.RefNo = tblAdopt.RefNo) INNER JOIN TblAssetBramble ON Pnamstr.MasterID = TblAssetBramble.MasterID;




Thanks people James Goodman
j.goodman00@btinternet.com

 

[jgoodman00]

I forgot to say that these queries are part of an Access97 database at present.... James Goodman
j.goodman00@btinternet.com

 

[rajeevnandanmishra]

Hi james,

Try this:
-----------
First Query:SELECT DISTINCT PNAMSTR.*, tblHierarchy.SELECTED,
CHARINDEX(tblAdopt.priority,tblHierarchy.priority) as EXP1,
tblAdopt.priority
FROM tblHierarchy INNER JOIN PNAMSTR ON
tblHierarchy.HierarchyID = PNAMSTR.HierarchyID INNER JOIN
tblAdopt ON PNAMSTR.RefNo = tblAdopt.RefNo
WHERE tblHierarchy.Selected='Yes' AND
CHARINDEX(tblAdopt.priority,tblHierarchy.priority)>0

I had not worked with access much, so please explain me that what do you mean by [Select_Features - BRSCode] in second query. Is this a separate table or what...?

 

[jgoodman00]

Yes, this is another query, which contains the following SQL:
SELECT tblAdopt.brsno, tblBrsCode.Selected
FROM tblBrsCode INNER JOIN tblAdopt ON tblBrsCode.BrsCode = tblAdopt.brsno
WHERE (((tblBrsCode.Selected)=Yes));


I didn't actually name these queries, but I am sure these are gonna cause problems for SQL server... James Goodman
j.goodman00@btinternet.com

 

[rajeevnandanmishra]

Hi james,
what happen to the first query? Have you tried it?
By the way here i am giving solution for your second query:
Second Qyery:INSERT RESULTS (mslink, x, y, max_x, RefNo,
brsno, road, priority )
SELECT DISTINCT tblAdopt.new_mslink, tblAdopt.x,
tblAdopt.y, tbladopt.max_x, tblAdopt.max_y, Pnamstr.RefNo,
tblAdopt.brsno, tblAdopt.road, tblAdopt.priority
FROM Pnamstr INNDER JOIN Select_Features ON Pnamstr.MasterID = Select_Features.MasterID
INNER JOIN tblAdopt ON Pnamstr.RefNo=tblAdopt.RefNo INNER JOIN
(SELECT tblAdopt.brsno, tblBrsCode.Selected
FROM tblBrsCode INNER JOIN tblAdopt ON tblBrsCode.BrsCode = tblAdopt.brsno
WHERE tblBrsCode.Selected='Yes') XX ON tblAdopt.brsno=XX.brsno
INNER JOIN TblAssetBramble ON Pnamstr.MasterID = TblAssetBramble.MasterID


Try this also, and let us know if it works!

 

[jgoodman00]

The first query works (I created this as a view). I have tried setting the second as a stored procedure, but it doesn't appear to like it very much! I think the query itself is correct, but there is something wrong in the create procedure statement...

CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS qrySourceBramble

INSERT RESULTS (mslink, x, y, max_x, max_y, RefNo,
brsno, road, priority )
SELECT DISTINCT tblAdopt.new_mslink as mslink, tblAdopt.x,
tblAdopt.y, tbladopt.max_x, tblAdopt.max_y, Pnamstr.RefNo,
tblAdopt.brsno, tblAdopt.road, tblAdopt.priority
FROM Pnamstr INNDER JOIN Select_Features ON Pnamstr.MasterID = Select_Features.MasterID
INNER JOIN tblAdopt ON Pnamstr.RefNo=tblAdopt.RefNo INNER JOIN
(SELECT tblAdopt.brsno, tblBrsCode.Selected
FROM tblBrsCode INNER JOIN tblAdopt ON tblBrsCode.BrsCode = tblAdopt.brsno
WHERE tblBrsCode.Selected='Yes') XX ON tblAdopt.brsno=XX.brsno
INNER JOIN TblAssetBramble ON Pnamstr.MasterID = TblAssetBramble.MasterID James Goodman
j.goodman00@btinternet.com

 

[rajeevnandanmishra]

Hi James,
If you think that the query is OK then create procedure like
CREATE PROCEDURE qrySourceBramble AS
...Query

 

[jgoodman00]

I have sorted this out by realising how I was meant to name it, but I still get an error. This is what I now have:
CREATE PROCEDURE qrySourceBrambleUpdate AS

INSERT RESULTS (mslink, x, y, max_x, max_y, RefNo,
brsno, road, priority )
SELECT DISTINCT tblAdopt.new_mslink, tblAdopt.x,
tblAdopt.y, tbladopt.max_x, tblAdopt.max_y, Pnamstr.RefNo,
tblAdopt.brsno, tblAdopt.road, tblAdopt.priority
FROM Pnamstr INNDER JOIN Select_Features ON Pnamstr.MasterID = Select_Features.MasterID
INNER JOIN tblAdopt ON Pnamstr.RefNo=tblAdopt.RefNo INNER JOIN
(SELECT tblAdopt.brsno, tblBrsCode.Selected
FROM tblBrsCode INNER JOIN tblAdopt ON tblBrsCode.BrsCode = tblAdopt.brsno
WHERE tblBrsCode.Selected='Yes') XX ON tblAdopt.brsno=XX.brsno
INNER JOIN TblAssetBramble ON Pnamstr.MasterID = TblAssetBramble.MasterID


When I try to validate the syntax, it gives an "error 107", stating that "The column prefix 'Pnamstr' does not match with a table name or alias name used in a query" This error is listed 4 times.... James Goodman
j.goodman00@btinternet.com

 

[rajeevnandanmishra]

James,
Please just run this statement, and let us know if there is any error.

SELECT DISTINCT tblAdopt.new_mslink, tblAdopt.x,
tblAdopt.y, tbladopt.max_x, tblAdopt.max_y, Pnamstr.RefNo,
tblAdopt.brsno, tblAdopt.road, tblAdopt.priority
FROM Pnamstr INNDER JOIN Select_Features ON Pnamstr.MasterID = Select_Features.MasterID
INNER JOIN tblAdopt ON Pnamstr.RefNo=tblAdopt.RefNo INNER JOIN
(SELECT tblAdopt.brsno, tblBrsCode.Selected
FROM tblBrsCode INNER JOIN tblAdopt ON tblBrsCode.BrsCode = tblAdopt.brsno
WHERE tblBrsCode.Selected='Yes') XX ON tblAdopt.brsno=XX.brsno
INNER JOIN TblAssetBramble ON Pnamstr.MasterID = TblAssetBramble.MasterID

 

[jgoodman00]

I created a view called viewBrambleUpdater which contained the SELECT statement, & then simply created a Stored Procedure which referenced this:

viewBrambleUpdater:
SELECT DISTINCT tblAdopt.new_mslink, tblAdopt.x,
tblAdopt.y, tbladopt.max_x, tblAdopt.max_y, Pnamstr.RefNo,
tblAdopt.brsno, tblAdopt.road, tblAdopt.priority
FROM Pnamstr INNDER JOIN Select_Features ON Pnamstr.MasterID = Select_Features.MasterID
INNER JOIN tblAdopt ON Pnamstr.RefNo=tblAdopt.RefNo INNER JOIN
(SELECT tblAdopt.brsno, tblBrsCode.Selected
FROM tblBrsCode INNER JOIN tblAdopt ON tblBrsCode.BrsCode = tblAdopt.brsno
WHERE tblBrsCode.Selected='Yes') XX ON tblAdopt.brsno=XX.brsno
INNER JOIN TblAssetBramble ON Pnamstr.MasterID = TblAssetBramble.MasterID


qryBrambleUpdate:
CREATE PROCEDURE qryBrambleUpdate AS
INSERT RESULTS (mslink, x, y, max_x,max_y, RefNo,
brsno, road, priority )
Select New_MSLINK as mslink, x, y, max_x, max_y, RefNo, brsno, road, priority
FROM viewBrambleUpdater
GO


The syntax check of this now runs fine, although I can't actually run the query properly because I can't find anywhere within the Enterprise Manager to run a stored procedure. What is the standard type of front-end for SQL Server databases. We will probably end up using an Access 2000/2 project, because importing our existing forms will then be quite simple...
James Goodman
j.goodman00@btinternet.com

 

[rajeevnandanmishra]

Now a days people are using a vast variety of front-ent, depending on their grasp of individual one.
By the way you can run the above stored procedure by opending Query Analyzer and then typing followingqryBrambleUpdate And then click on the green button to execute it.