|
This looks like a Permission exception. Try to give your aspnet user permission and the "Administrator" user of the machine you are running the package.
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I have a recursive problem.
I have a query that select some columns but one of these colums should be a value that is recursive.
I have a documentname and based on this name I can retrieve the the folderstructure, but this folderstructure is recursive. How can i create 1 query that returns the documentName and the full folderstructure (which is found if the foldername is '').
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
you need to expand your question with code sample and data sample.
as my first questions would be is the data heirachical? or is it based on columns through out the row?
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
Do you mean ,with a given root node to get a whole tree ?
|
|
|
|
|
No its an endnode that should retrieve the way back to rootnode
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
To give more details.
1 table called documents holds documentnames and have a link to
1 table called documentfolders and this table has a link to documentid and folderid
1 table called folders and holders foldernames, folderid and parentfolderid.
structure to retrieve is
folder1/folder2/folder3/foldern for the complete path in which the document can be found.
If I only ask documentname and foldername it would be simple. but once I have to foldername I should retrieve the previous level of foldername recusrively until no foldername is found. Basically it knows 'foldern' but should find the way back to root ('folder1' in example)
So my query should do something like
SELECT DocumentName, GetVirtualPath(documentID, FOLDERID) AS virtualPath
FROM Documents as d
Join Documentfolders as df on df.documentID = d.documentID
Join Folders as f on f.folderID = df.folderID
GetVirtualPath(documentID, FOLDERID) should be the recursive call.
In Word you can only store 2 bytes. That is why I use Writer.
modified on Thursday, October 14, 2010 8:49 AM
|
|
|
|
|
You could try writing a function that calls itself and returns the parent folder name for a given parent folder id. Eventually you should end up at root, where the parent folder id should be null and that would end the recursive call. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Since this system is build and delivered by an external party I am not allowed to build a function in this database (warranty story....)
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
if your using SQL 2005 or later. Have a look into recursive CTE (common table expressions)
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
It is an Interbase database but hat can do CTE as well.
But the select results is multiple rows and not 1 varchar with the result like desired.
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Since you cannot make changes to the database and you don't want the information in it's natural state, rows from the CTE then you need to do the manipulation in your client app. Take the results of the CTE and massage it to meet your needs.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
WITH
ParentChildRels
(ParentID, ChildID, KeyField, [Path], HierarchyLevel, Doc)
AS
(
SELECT
ParentID, ChildID, KeyField, CAST('root/' + Folder AS nvarchar(255)) AS Path, 1 AS HierarchyLevel, Doc
FROM
(
SELECT
dbo.Folder.ParentID, dbo.Folder.ID AS ChildID, dbo.Folder.Folder, dbo.[Document].DocumentID AS KeyField, dbo.[Document].[Document] AS Doc
FROM
dbo.[Document]
RIGHT OUTER JOIN
dbo.Folder
ON
dbo.[Document].FolderID = dbo.Folder.ID
) AS x
WHERE
(ParentID = 0)
UNION ALL
SELECT
r.ParentID, r.ChildID, r.KeyField, CAST(RTRIM(pr.Path) + '/' + r.Folder AS nvarchar(255)) AS Path, pr.HierarchyLevel + 1 AS HierarchyLevel, r.Doc
FROM
(
SELECT
Folder_1.ParentID, Folder_1.ID AS ChildID, Folder_1.Folder, Document_1.DocumentID AS KeyField, Document_1.[Document] AS Doc
FROM
dbo.[Document] AS Document_1
INNER JOIN
dbo.[Folder] AS Folder_1
ON
Document_1.FolderID = Folder_1.ID
) AS r
INNER JOIN
ParentChildRels AS pr
ON r.ParentID = pr.ChildID
)
SELECT DISTINCT TOP (100) PERCENT
ParentID, ChildID, KeyField, RTRIM(RTRIM([Path]) + '/' + Doc)
FROM
ParentChildRels AS ParentChildRels_1
WHERE Keyfield is not null
ORDER BY
RTRIM(RTRIM([Path]) + '/' + Doc), ParentID, ChildID, KeyField
I thought to share it with you all if you ever come into the same situation
In Word you can only store 2 bytes. That is why I use Writer.
modified on Monday, October 18, 2010 4:59 AM
|
|
|
|
|
mates,
need help. i'm stuck on sql query.
SELECT * FROM TABLE1
WHERE MYFLG =0
AND MYFLG =(SELECT CASE WHEN COLUMN1 = 1 AND COLUMN2 = 0 THEN 1 ELSE 0 END AS MYFLG FROM TABLE1)
got an error: Invalid column name 'MYFLG'.
I know the error because it is an alias name in case statement which is not totally exist in table1 but how will i correct this one.
My aim is only select those MYFLG = 0 in my case statement.
C# γ³γΌγMicrosoft End User
2000-2008
γγγγγγγγγγγγγγγγγγγγγγγγγγγγ
The best things in life are free
γγγγγγγγγγγγγγγγγγγγγγγγγγγγ
|
|
|
|
|
I have not tried running my suggestions -
1) Try w/ brackets as below
SELECT * FROM TABLE1
WHERE MYFLG =0
AND MYFLG =(SELECT (CASE WHEN COLUMN1 = 1 AND COLUMN2 = 0 THEN 1 ELSE 0 END) AS MYFLG FROM TABLE1)
2) What are you trying to accomplish w/ this query? Seeing your query, I guess you could simplify it as
SELECT * FROM TABLE1 WHERE (MYFLG = 0 OR (COLUMN1 = 1 AND COLUMN2 = 0))
MYFLG = 0 AND MYFLG = ... doesn't make sense. How could MYFLG could be both 0 and 1? It should be an "or" I guess
I may be wrong, in that case, try to explain your question in detail...
Cheers,
Karthik
|
|
|
|
|
I guess "MYFLG" is not a column of table1.
what result he wants to get is that rows "
not in where COLUMN1 = 1 AND COLUMN2 = 0 "
If so,Why don't use this
Select * from TABLE1 where PK not in ( Select PK From TABLE1 Where COLUMN1 = 1 AND COLUMN2 =0)
modified on Wednesday, October 13, 2010 10:37 PM
|
|
|
|
|
I don't think so, if you see the posters' query, it has
WHERE MYFLG =0
Cheers,
Karthik
|
|
|
|
|
but he alse got this Error infomation "got an error: Invalid column name 'MYFLG'".
|
|
|
|
|
@Karthik. A
thank you for your suggestion. yes. Alegria_Lee is correct MYFLG is not a column it is an alias from my case statement. sorry for my explanation not in details.
anyway. this is the actual query.
SELECT CASE WHEN SMPTABLE.RPFLG = 1 AND SMPTABLE.NDFLG= 0 THEN
1 ELSE 0 END AS MYFLG
FROM [TBMASTER] ,SMPTABLE,ANLTABLE,WKTABLE
WHERE (SMPTABLE.CERTCODE BETWEEN 1 AND 5 OR
SMPTABLE.CERTCODE BETWEEN 11 AND 15 or
SMPTABLE.CERTCODE = 17) AND
(SMPTABLE.DDATE IS NOT NULL) AND
(SMPTABLE.CERTNNO = 0 OR
SMPTABLE.CERTNNO IS NULL) AND ((SMPTABLE.DIVCODE = 1) OR
(SMPTABLE.DIVCODE = 5))
AND (ANLTABLE.ROWNO = 1) AND (ANLTABLE.KMKCD <> 1998)
AND [TBMASTER].SAMPLECD = SMPTABLE.SAMPLECD
AND ANLTABLE.SHINO=SMPTABLE.SHINO
AND SMPTABLE.ORDNO = ANLTABLE.ORDNO
AND WKTABLE.ORDNO =SMPTABLE.ORDNO
AND ((WKTABLE.SECT=31) OR (WKTABLE.SECT =51))
AND ([ANLTABLE].ENVFLG = 0)
the result of MYFLG temporary columns are
MYFLG
0
0
0
0
1
1
0
1
0
; then i want to query again the result for those MYFLG = 0.
that is what my approach is subquery.
SELECT (SELECT).
:: i will try the NOT IN statement given by Alegria_Lee.
C# γ³γΌγMicrosoft End User
2000-2008
γγγγγγγγγγγγγγγγγγγγγγγγγγγγ
The best things in life are free
γγγγγγγγγγγγγγγγγγγγγγγγγγγγ
|
|
|
|
|
Here is it
WITH myTable as
(
--Put here all your query
)
select * from myTable where MYFLAG=0
For more about
WITH
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
this is what i'm looking for.
thank you so much. i tried already and it's working. great job!
C# γ³γΌγMicrosoft End User
2000-2008
γγγγγγγγγγγγγγγγγγγγγγγγγγγγ
The best things in life are free
γγγγγγγγγγγγγγγγγγγγγγγγγγγγ
|
|
|
|
|
You are welcome. 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Hello guys.
First up, please know that it has been almost a decade since I have been anywhere close to a database, be it at work or outside. Anyway I never did anything substantial there except have a table and read/write data. So go easy on flaming if this turned out to be a n00b question or suchlike.
I have to model/analyze some data for some research and I need your help to see how I can go about that.
First some definitions
1. There is a set called S subscripted with n that I write as Sn where 1 <= n <= 100
2. Every Sn (i.e S for all n) is a subset of a larger set called US
3. None of the Sn is intersecting
4. It should be possible to name every Sn
Now the problem
1. I have An and Bn which have one to one mapping
2. I have Cn that has many to one mapping with Bn (each element of Bn maps to one or more elements of Cn)
3. There are also other sets like Dn, En, Fn etc that have one to one mapping with Bn only and are unrelated to any other set directly
I want to be able to do these
1. Given any element from UA, the relevant An (the set) must be got. If An exists, then the corresponding Bn element must be got
2. Similarly, given any element from UB, the relevant Bn must be got. If Bn exists, either from this step or step (1), all the corresponding Cn must be got along with the corresponding ones from Dn, En, Fn etc (the sets that must be considered will be told )
3. Alternatively, any Sn must be extracted by name and all the relations for all its elements must got (i.e if An then Bn as per (2), if others then Bn only)
[EDIT] Some stats - Each set will have at most 500 elements and the super set will have at most 2000. And the set count may be at most 100*50=5000 in all.
I will be having an app for the front end that will do the creating, editing and querying of the database. I will be using MS Access for the backend as it is kinda βfreeβ and already there. I will refresh on my SQL commands as I know that it will be necessary for interacting programmatically.
So can you guys please help me? I need broad ideas like designing it etc more than the "code" itself. I wouldnβt mind if you suggest any other βfreeβ databases that can make my solution simpler. I suppose using ODBC or DAO (thatβs what I last remember) is going to act as a black box and I can use any dbase.
And oh, I donβt know .NET. Will it be a chore then?
Feel free to ask me back anything about my problem that you couldnβt understand.
Thanks for your time.
|
|
|
|
|
It sounds like your dataset is not that large, so storing the data in Access sounds fine.
You will be amazed at how easy it is to develop queries in Access to get the results you need, if you find the data to be too large or you need to share this database with others, you may have to upgrade to something more substantial, ie MS-SQL.
Start with Access, it should give you enough to prototype with ... maybe even solve your problem completely.
Good luck.
|
|
|
|
|
Thanks for your response.
I think each set will have at most 500 elements and the super set will have at most 2000. And the set count may be about 100 in all. So I might be able to use Access.
Could you please give some pointers on the design? Some brief thing about tables etc will surely help me.
|
|
|
|
|
1. if setting up servers for transactional replication, is it possible for the subscriber server to delay the publisher server?
For example, the publisher server is under heavy load and inserting/deleting a large amount of rows; I realize that the publishing server will be a little slower due to sending these updates to the subscriber server(s), but does the publishing server have to wait or stop processing while the subscribing servers are committing the same transactions?
Or are the sent transactions to the subscriber queued and processed at its own pace - not impacting/stopping the publishing server? I understand that a distributor server cab queue these commands to the subscribers, but does the action of queuing ever pause the production/publisher server in any way?
2. When doing queries on a subscriber database (since it's read only), does the database become unavailable while committing transactions sent from the distributor or publishing server(s)?
Thanks for any help. Just looking to gain some insight on replication.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
