Database

ChandraRam13-Mar-08 2:10

ChandraRam

13-Mar-08 2:10

Snaider wrote:
Just only ASCII text?? So why is there a Date format for .dbf file...??? I tried to insert '20070922', "20070922" and this didn't work as well...

It's been a while since I have seen a DBF file... I think the Date type in a DBF file is a 10-char storage. Maybe you could try '03/13/2008' (depending upon your system locale)

Khawar Abbas113-Mar-08 2:43

13-Mar-08 2:43

Try this

Insert Into Test2 values ('09/22/2007');

and check the data in database.

Do good and have good.

Snaider13-Mar-08 5:54

Snaider

13-Mar-08 5:54

It didn't worked...but I've just find out how to do it:

Insert Into Results.dbf Values (CtoD([06/18/2005]))

Only by this way it worked.

Thx for everybody's help Laugh | :laugh:

building a query for finding a value

perryf_0013-Mar-08 7:52

perryf_00

13-Mar-08 7:52

Dang. I'm stuck doing DBF stuff again!

But to answer your question, you can use the "CTOD" function as you've seen. Or you can use {} as the field delimiters for an actual date field. So:

Insert into Results({09/22/2007})

laziale12-Mar-08 22:42

12-Mar-08 22:42

hi! I have a problem with sql server. I want to find a record from a database, with like 30 tables in it, and lots of records. 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, any help will be welcomed, so I can resolve this issue. Thanks ahead

Colin Angus Mackay12-Mar-08 23:45

Colin Angus Mackay

12-Mar-08 23:45

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.

Upcoming FREE developer events:
* Developer Day Scotland

Recent blog posts:
* Mixins in C#3.0

My website | Blog

laziale12-Mar-08 23:48

12-Mar-08 23:48

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

Colin Angus Mackay13-Mar-08 1:39

Colin Angus Mackay

13-Mar-08 1:39

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.

Upcoming FREE developer events:
* Developer Day Scotland

Recent blog posts:
* Mixins in C#3.0

My website | Blog

Khawar Abbas113-Mar-08 0:02

13-Mar-08 0:02

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.

laziale13-Mar-08 0:10

13-Mar-08 0:10

can you give me some example please. Thanks

Khawar Abbas113-Mar-08 0:19

13-Mar-08 0:19

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.

laziale13-Mar-08 0:33

13-Mar-08 0:33

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

Oracle to SQL Server 2005 Migration

Colin Angus Mackay13-Mar-08 1:45

Colin Angus Mackay

13-Mar-08 1:45

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?

Upcoming FREE developer events:
* Developer Day Scotland

Recent blog posts:
* Mixins in C#3.0

My website | Blog

dasrimk12-Mar-08 21:23

dasrimk

12-Mar-08 21:23

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

Classic ASP with Enterprise Library -Data Block

Neville12-Mar-08 21:12

Neville

12-Mar-08 21:12

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? Confused | :confused:

Please help. Sigh | :sigh:

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.

Finding SQL Server Licensing Information

sandeepkavade12-Mar-08 21:10

sandeepkavade

12-Mar-08 21:10

hi all,
I want to retrive the licensing information such as Licensing count, which
licensing is currently in use, whats its type (per processer or per user),
licensing code etc. From where i can retrive the information? I want to do it
for Sql Server 2005 and 2000. I read the blog "Tracking License Information
in SQL 2005" on
http://blogs.msdn.com/sqlblog/archive/2006/11/10/tracking-license-information-in-sql-2005.aspx[^]
but it doesnt help me at all.

How do hierarchy SELECT statement...

RYU^^12-Mar-08 12:46

12-Mar-08 12:46

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 Smile | :)

Syed Mehroz Alam12-Mar-08 19:38

Syed Mehroz Alam

12-Mar-08 19:38

SQL Server 2005 provides common table expressions (CTE) for recursion. You can view more here[^]

Regards,
Mehroz

RYU^^12-Mar-08 20:25

12-Mar-08 20:25

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 Smile | :)

Syed Mehroz Alam12-Mar-08 20:52

Syed Mehroz Alam

12-Mar-08 20:52

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

Khawar Abbas112-Mar-08 21:53

12-Mar-08 21:53

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.

Syed Mehroz Alam12-Mar-08 23:22

Syed Mehroz Alam

12-Mar-08 23:22

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

RYU^^13-Mar-08 12:33

13-Mar-08 12:33

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 Smile | :)

RYU^^13-Mar-08 12:41

13-Mar-08 12:41

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 Smile | :)

RYU^^13-Mar-08 12:52