|
laziale wrote: I don't know exactly where that record is, in which table. So, cause I am a beginner in SQL server, with the knowledge what I have, I did this:
SELECT * FROM dbTest WHERE VALUES ='some value';
and it gives me error, near VALUES, so I don't know how to find it,
Well, if dbTest is the name of the database then it won't work. You must specify the name of the table. If you don't know your database well enough to know where things are then you should be asking a colleague for some sort of ER diagram and data dictionary (documentation) to show you where things are.
|
|
|
|
|
no, I know the name of the database, the name is dbo_Customers, but that database has around 30 tables, and lots of records inside, so any help on some query, to find the text, like 'sometext' will be helpfull. Thanks for replying
|
|
|
|
|
laziale wrote: but that database has around 30 tables, and lots of records inside, so any help on some query, to find the text, like 'sometext' will be helpfull. Thanks for replying
You write 30 queries. Or you find out where the data is held.
As I said, you need to ask for a ER diagram and data dictionary to find out how the database is structured and what it all means. Randomly fishing around inside tables for some value is really not the way to go.
|
|
|
|
|
You have to give table name in select statement and if you want to search sometext from multiple tables, you should use multiple querires for multiple tables.
Do good and have good.
|
|
|
|
|
can you give me some example please. Thanks
|
|
|
|
|
select * from tablename where columnname = 'sometext';
Please tell what is "Values" in your query?
It must be the column name in your table on which you want to find the sometext.
Do good and have good.
|
|
|
|
|
the value is 'noordzeeweg' but the think is that we don't know exactly in which table or column exactly is the record, and we have around 30 tables with lots of columns and records inside. Thx for helping
|
|
|
|
|
laziale wrote: the value is 'noordzeeweg' but the think is that we don't know exactly in which table or column exactly is the record, and we have around 30 tables with lots of columns and records inside.
You say "we don't know" which implies that no one knows where this data is in your database. I therefore have to advise you to start creating documentation for your database. It is totally absurd to be running a database and not know what sort of information is in it and where it is.
All I can say is that the value hints that it might be the name of a road or street. Do you have any tables that hold that kind of information?
|
|
|
|
|
Hi Friends..
Can any body help me on....Database Migration.First time I am going to do this.
what approach i should follow.or any best approach you have implemented.
Oracle to SQL Server Migration
Dasrimk
|
|
|
|
|
Heres the scenario...
An ASP Page calls a c# component registered using regasm /tlb seehash.dll
I know the registration of the dll has gone fine because for testing purposes the dll just returned a simple string 'IamSeeHash' which got displayed on the asp page. Then I went back to my seehash.dll, uncommented the following line
Database db = DatabaseFactory.CreateDatabase();
and recompiled the dll and re-registered it again.
now I get the error 'Object reference not set to an instance of an object.'
I figure this could be because the config file cant be located by the asp process, so I created a copy of the config files in the SystemRoot%\System32 directory as 'dllhost.exe.config' and also 'asp.dll.config'
another copy named as 'index.asp.config' in the asp pages root directory. still I get the same error
I even went back to my seehash.dll and modified the line to add the specific database name
Database db = DatabaseFactory.CreateDatabase("MyDb");
then it says cant locate the database in the configuration file
What is going wrong?  Please help. 
Thanks.
<b>+Heres my config file +</b>
<configuration>
<configSections>
<section
name="dataConfiguration"
type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings,
Microsoft.Practices.EnterpriseLibrary.Data" />
</configSections>
<connectionStrings>
<add
name="MyDb"
providerName="System.Data.SqlClient"
connectionString=
"server=(local)\SQLEXPRESS;
database=MyDb;
UID=sa;PWD=usa;" />
</connectionStrings>
<dataConfiguration defaultDatabase="MyDb"/>
</configuration>
<b>+Heres my folder structure +</b>
For the asp page
C:\Inetpub\wwwroot\simpleAspSite
For the dlls (all referenced dlls of the datablock are in this directory)
c:\mydlls\seehash.dll
I am continually amazed by how little code is required to use atomic clocks in satellites 11,000 miles above my head.
|
|
|
|
|
|
Hi,
I have a table called tblUserDetail which contains the following attributes:
- ID
- ManagerID (which will refer to ID)
- etc...
Each user can have only 1 manager, but each user can have 0 or more staff.
Now, I have the following structure
A G
---|--- ---|---
| | | | |
B C D H I
|
E
|
F
Therefore, if I choose person "A" (for example), it will allow me to add person "G" as person "A" staff.
If I choose person "F" it will also allow me to add person "G" as person "F" staff.
Now, I can handle 3 level of hierarchy, but I don't know how to do recursive in SQL statement.
Here is my SQL Statement:
eg.
thisUserID = [current selected user]
thisUserManagerID = [the selected manager id]
strSQL = "SELECT UserID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE " +
"UserID<>" + thisUserID.ToString() + " AND ManagerID<>" + thisUserID.ToString() + " AND " +
"UserID<>" + thisUserManagerID.ToString() + " AND ManagerID=-1 AND " +
"(NOT EXISTS(" +
"SELECT * FROM tblUserDetail u2 WHERE u2.UserID=" + thisUserManagerID.ToString() + " AND " +
"NOT EXISTS(" +
"SELECT * FROM tblUserDetail u3 WHERE u1.UserID<>u2.ManagerID" +
")" +
")" +
")";
Currently, if I choose person "F" it will allow me to add "A" and "G" as person "F" staff because it doesn't handle more than 3 level of hierarchy.
Now, this works perfectly for 3 level of hierarchy, but I need more than 3 level. Is there a way to do recursive statement within SQL statement?
Cheers 
|
|
|
|
|
SQL Server 2005 provides common table expressions (CTE) for recursion. You can view more here[^]
Regards,
Mehroz
|
|
|
|
|
Hi Syed,
I am having a trouble to bind between fields. Here is a simple example
WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 WHERE u2.UserID<>u1.ManagerID
)
SELECT * FROM NewUserDetail
And I got this message:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "u1.ManagerID" could not be bound.
I need to bind value to recursively go through all the users so the user will not be able to select its manager or manager of its manager.
Any idea on how to tackle this problem?
Cheers
|
|
|
|
|
Seems you didnt get the cte syntax right. The second query inside the cte syntax generally joins with the cte itself, not the first query.
Lets have a cte that lists your user and all its subordinates
WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 WHERE u2.ManagerID=NewUserDetail.UserID
)
SELECT * FROM NewUserDetail
|
|
|
|
|
The problem resides in your recursive call. The line is "SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 WHERE u2.ManagerID=NewUserDetail.UserID"
Here you have to add a inner join to the NewUserDetail table.
I am also giving you an example. Hopefully it will solve your problem. The example is given below:
WITH TemplateTopic(parent_id, child_id, child_name, child_type, Level)AS
(
-- Anchor member definition
SELECT a.parent_id,a.child_id, a.child_name, a.child_type, 0 AS Level
FROM tbltemplate_topics a Where Child_Id = 16
UNION ALL
---- Recursive member definition
SELECT a.parent_id,a.child_id, a.child_name, a.child_type,
Level + 1 AS Level
FROM tbltemplate_topics a
INNER JOIN TemplateTopic AS d
ON a.parent_id = d.child_id
)
-- Statement that executes the CTE
SELECT * FROM TemplateTopic
(Make changes in your code accordingly)
Do good and have good.
|
|
|
|
|
Oh...thanks for pointing. I missed the inner join part, dont know why..
Here is the modified cte
WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 Inner Join NewUserDetail Manager WHERE u2.ManagerID=Manager.UserID
)
SELECT * FROM NewUserDetail
|
|
|
|
|
Hi Syed,
I tried your example and it gave me this error
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'WHERE'.
Any idea?
Thanks again
|
|
|
|
|
Hi Syed,
Nevermind, I figured out the syntax with the help of the example given by Silent Eagle (thanks Silent Eagle). It should read:
WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT u2.UserID, u2.ManagerID, u2.FirstName + ' ' + u2.LastName As [FullName] FROM tblUserDetail u2 Inner Join NewUserDetail AS Manager ON u2.UserID<>Manager.ManagerID
)
But it again gave me another error on execution.
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
That is wierd because I did re-check and make sure there is no recursive employee-manager relation.
Any idea?
Thanks again
|
|
|
|
|
I see what is the problem.
I shouldn't put
u2.UserID<>Manager.ManagerID
instead I should put
u2.UserID=Manager.ManagerID
Thanks Syed and Silent Eagle for your help.
Cheers
|
|
|
|
|
IIf(IsEmpty(DLookUp("[Doctor Name (MD)]","tblQueryData","[PKey]='1'")),([tblMasterReferrals].[Doctor Name (MD)]) Like "*",DLookUp("[Doctor Name (MD)]","tblQueryData","[PKey]='1'")) Or Is Not Null
I have this strip of code in the Expression Builder for the criteria row of a column in an Access Database Query. My objective for this is to have this code filter the records by an entry in the first row of a table, in a column with a matching name. Also, if there is no entery for that column in the table then show all records, or no filter in that column. I have six six different columns in one query i would like to do this to. I have found that if i have a criteria entry in the the table for the query and i change that column's Expression Builder Code to say Or Is Null it works, it filters the query on that table entry. But if i take that entry out of the table, it will return nothing unless I change the Expression Builder Code to say Or Is Not Null, I want the query to return unfiltered records for that column if there is no entry in the table. Can anyone tell me how i can mess these two to make a fuctional query??
Lost!
|
|
|
|
|
hi,
i have created a database where information on the web page is pulled out from the database using asp.net and have an sql server database. however i need to add javascript functionality to it. the javascript functionality i want to add is a timed text one. does anyone have any suggestions as to how i can achieve this and what is the best way to do it, eg, use dataview, gridview and how i can link the javascript to it. if anyone thinks it would be easier for me to post up some of my code then i will upon request. i hope i have posted this is the right place, if not i will move it to the asp.net bit.
thanks for any help given, it is much needed
|
|
|
|
|
This has nothing to do with databases except for a brief mention of a database being involved. It seems that what you are asking is better suited to the ASP.NET forum.
|
|
|
|
|
Is there a database question in there? Like Colin said, you might be better off in the ASP.NET or Web Development forums.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
ok thanks, i wasnt sure where to post it, so iv posted on the asp.net bit now
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
