Count number of records in related tables - Database Discussion Boards

Re: HOW TO MERGE IN ONE! [modified]

Blue_Boy24-Apr-08 9:52

Blue_Boy

24-Apr-08 9:52

hope this will help you...

select count(*) from myTable <br />
where [columndate] >= substring(convert(varchar,columnName,20),1,10)+' 12:00:00.000' and<br />
[columndate] <= substring(convert(varchar,columnName,20),1,10)+' 12:00:00.000'

I Love T-SQL

modified on Thursday, April 24, 2008 4:26 PM

SQL 2000 ----- Extracting Rows from Delimited Strings

n1coltsfan24-Apr-08 2:35

n1coltsfan

24-Apr-08 2:35

The query I am passing the two letter strings like "UV+ 131.0000+ 0.0000" work fine when it goes to 3 and up like "TLOV+2048.0000+ 0.0000" it starts messing up the right and middle strings and showing the + sign delimiter.

Please Help !!!

select *,left(raw_payload,charindex('+',raw_payload,1)-1),
substring(left(raw_payload, 1-len(left(raw_payload,charindex('+',raw_payload,1)+1)),
len(raw_payload)),charindex('+',raw_payload,1)* charindex('+',raw_payload,1)-1 ),substring(right(raw_payload,charindex('+',raw_payload,1)*3),charindex('+',right(raw_payload,charindex('+',raw_payload,1)*3),1)+1,len(raw_payload))
from raw_data.dbo

Fieldname: raw_payload

"S9000+"
"UV+ 131.0000+ 0.0000"
"UV+ 132.0000+ 0.0000"
"UV+ 140.0000+ 0.0000"
"UV+ 141.0000+ 0.0000"
"UV+ 142.0000+ 0.0000"
"UVN+ 524.0000+ 0.0000"
"UVN+ 525.0000+ 0.0000"
"UVN+ 527.0000+ 0.0000"
"UVN+ 528.0000+ 0.0000
"TLOV+2044.0000+ 0.0000"
"TLOV+2045.0000+ 0.0000"
"TLOV+2046.0000+ 0.0000"
"TLOV+2047.0000+ 0.0000"
"TLOV+2048.0000+ 0.0000"
"TLOV+2049.0000+ 0.0000"
"OTLWV+2201.0000+ 0.0000"
"OTLWV+2202.0000+ 0.0000"
"OTD/ROV+2401.0000+ 0.0000"
"OTD/ROV+2402.0000+ 0.0000"
"OTD/ROV+2403.0000+ 0.0000"
"OTD/ROV+2404.0000+ 0.0000"
""OTD/ROV+2432.0000+ 0.0000"
"OTD/ROV+2436.0000+ 0.0000"
"OTD/ROV+2443.0000+ 0.0000"
"OTD/ROV+2444.0000+ 0.0000"
"OTD/ROV+2445.0000+ 0.0000"
"OTD/ROV+2446.0000+ 0.0000"
"G59WO+5324.0000+ 36.6190"
"TLMLV+5601.0000+ 0.0000"
"TLMLV+5602.0000+ 20.0000"
"TLMLV+5603.0000+ 0.0000"
"TLMLV+5605.0000+ 0.0000"
"TLMLV+5606.0000+ 75.0000"
"TLMLV+5607.0000+ 175.0000"

Re: SQL 2000 ----- Extracting Rows from Delimited Strings

Blue_Boy24-Apr-08 8:36

Blue_Boy

24-Apr-08 8:36

dude I can give you solution but it is "terrible code" how did i solved.
here it is

select *,<br />
substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1))) as a1,<br />
<br />
left(substring(raw_payload,3+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1)))),len(raw_payload)),<br />
charindex('+',<br />
substring(raw_payload,4+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1)))),len(raw_payload)))) as a2,<br />
<br />
substring(<br />
substring(raw_payload,4+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1))))+<br />
len(left(substring(raw_payload,3+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1)))),len(raw_payload)),<br />
charindex('+',<br />
substring(raw_payload,4+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1)))),len(raw_payload))))),len(raw_payload)),1,<br />
len(substring(raw_payload,4+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1))))+<br />
len(left(substring(raw_payload,3+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1)))),len(raw_payload)),<br />
charindex('+',<br />
substring(raw_payload,4+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1)))),len(raw_payload))))),len(raw_payload)))-1) as a3<br />
 from  raw_data.dbo

I Love T-SQL

Re: SQL 2000 ----- Extracting Rows from Delimited Strings

n1coltsfan25-Apr-08 4:42

n1coltsfan

25-Apr-08 4:42

left and middle work great right or the third column does not...

Re: SQL 2000 ----- Extracting Rows from Delimited Strings

Blue_Boy25-Apr-08 5:01

Blue_Boy

25-Apr-08 5:01

can you fix that with C# code or you must to fix it with T-SQL?

I Love T-SQL

Selection between ranges in sql server 2005

salon24-Apr-08 2:00

salon

24-Apr-08 2:00

I want to select data from ranges like

Column1 Column2
25000 10
50000 20
75000 30

I want records like upto 25000, I get 10
upto 50000, I get 20
upto or more than 75000, I get 30

Can anybody suggest me a simple query for that?

Thanks in advance,

Re: Selection between ranges in sql server 2005

Broken Bokken24-Apr-08 2:37

Broken Bokken

24-Apr-08 2:37

Use a sql case statement. http://doc.ddart.net/mssql/sql70/ca-co.htm[^]

SQL

SELECT Column1, 
CASE
WHEN Column1 <= 25000 THEN 10
WHEN Column1 > 25000 AND Column1 <= 50000 THEN 20
ELSE 30
END AS Column2
FROM MyTable

Broken Bokken

You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod

http://www.brokenbokken.com

Count number of records in related tables

Kevin Brydon24-Apr-08 0:54

Kevin Brydon

24-Apr-08 0:54

Hi.

I have database structure like this:

Organizations(OrganizationID, Name)
Members(MemberID, OrganizationID, Name)
Courses(CourseID, OrganizationID, Name)

I need to return the Organization Name, the number of members of that organization and the number of courses in that organization in one sql statement so I'd have a table like

[OrganizationName] [Number of Members] [Number of courses]
[Org A] [10] [22]
[Org B] [92] [11]
...

Is it possible without using a stored procedure?

Can anyone help me out? Any help would be much appreciated.

Re: Count number of records in related tables

Ashfield24-Apr-08 1:30

Ashfield

24-Apr-08 1:30

One way:

select o.Name, (select count(*) as MembersCount from Members m where m.OrganizationID= o.OrganizationID),(select count(*) as CoursesCount from Courses c where c.OrganizationID = o.OrganizationID)<br />
from Organization o

There are other alternatives depending on what database you are using

Bob
Ashfield Consultants Ltd

Re: Count number of records in related tables

Kevin Brydon24-Apr-08 2:49

Kevin Brydon

24-Apr-08 2:49

Great. Thanks very much!

Re: Count number of records in related tables

Ashfield24-Apr-08 3:18

Ashfield

24-Apr-08 3:18

No problem.

Bob
Ashfield Consultants Ltd

Diff b/W ADO & ADO.net

sonia.sardana23-Apr-08 23:46

sonia.sardana

23-Apr-08 23:46

I know only two differneces.
1) ADO wroks on Connected architecture, while ADO.Net on DisConnected Architecture.
2)ADO is used to create Client Side Cursors while ADo.net create both client as well as server.

IF FRNDS U KNOW DIFFERENCES OTHER THAN THESE PLZ DO REPLY

Re: Diff b/W ADO & ADO.net

Mycroft Holmes24-Apr-08 20:19

Mycroft Holmes

24-Apr-08 20:19

Notice the deluge of responses your question elicited.

1. Learn to spell
2. Learn to type english - your location is the US so presumable english is your 1st language
3. Learn to use google - see the 2nd response to this
clickety[^]

Never underestimate the power of human stupidity
RAH

Timeout Exception [modified]

Vimalsoft(Pty) Ltd23-Apr-08 23:25

Vimalsoft(Pty) Ltd

23-Apr-08 23:25

Good Morning All

I have a ASP Classic Application,that Connects to SQL Server, Sudddenly it gives a Timeout Exception like this

[Microsoft][ODBC SQL Server] Timeout Expired

What is Wrong

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

modified on Thursday, April 24, 2008 6:06 AM

Re: Timeout Exception

Mike Dimmick24-Apr-08 4:15

Mike Dimmick

24-Apr-08 4:15

To stop queries from taking a very long time to return and consuming resources on the server unnecessarily, and to stop them from blocking client software for an unbounded length of time, ODBC and other database communication libraries include timeouts on both the connection to the server and on executing a command.

Given your description I'd imagine you're using the ADO objects with the MSDASQL provider, possibly through a DSN, using the Microsoft SQL Server ODBC driver. You might get better results using the SQLOLEDB provider to remove some of the layers. In either case, you should be able to use the Command object's CommandTimeout property to set a more sensible timeout for your operation. The default timeout is 30 seconds.

If instead you're looking to reduce the amount of time taken to execute a given query, you need to look carefully at how the query is written and what indexes you have to support that query. Ideally you want a single index which contains all the columns that appear in your WHERE clause which are searchable (that is, using simple comparison operators to compare the column's actual value against some provided value). LIKE is searchable if you're searching for a prefix, that is, the wildcards appear after some static text (e.g. 'CP%'). Do be aware that the more indexes you have, the longer INSERT and UPDATE operations will take. Generally you have to plan your set of indexes across all the operations that will take place on a table.

Finally one reason that queries start to take longer amounts of time is if historical data accumulates in the table. Consider archiving data to a different table or database when it is no longer current, or even deleting it outright.

DoEvents: Generating unexpected recursion since 1991

Re: Timeout Exception

Vimalsoft(Pty) Ltd24-Apr-08 20:09

Vimalsoft(Pty) Ltd

24-Apr-08 20:09

Thanks

i will look into that

Re: Timeout Exception

GuyThiebaut24-Apr-08 5:52

GuyThiebaut

24-Apr-08 5:52

I think Mike has explained things very well - one thing I will add is that the setting timeout value to 0(zero) will usually set it to an infinite timeout i.e. you will not get a timeout (you may need to check your documentation on this).

We do this on our Access databases as ODBC can be very slow.

Regards

Guy

Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)

Re: Timeout Exception

Vimalsoft(Pty) Ltd24-Apr-08 20:03

Vimalsoft(Pty) Ltd

24-Apr-08 20:03

Thank you very much guys,

This Application was wrote in ASP Classic and i have no control over the code, and i always set the timeout of the command object to "0" for infinite.

Thanks for the Advice it make sense..

Thanks

Dropping all Tables

Broken Bokken23-Apr-08 16:10

Broken Bokken

23-Apr-08 16:10

I am working on a database create script for SQL server 2005. It's getting to be pretty large (20 tables so far) and the gist of the script is to dropall the tables, create the tables, and then add in any default data. I have the last two pieces working correctly, and previously I was dropping each table individually. I found some code to help drop all tables and modified it.

SQL

DECLARE @Count int

--############################################################################################
--		Drop Entities
--############################################################################################


SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'

WHILE @Count > 0
BEGIN
		EXEC sp_MSforeachtable "DROP TABLE ? PRINT '--- Dropped Table ? ---' ";	
		
		SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'
END

IF @Count = 0
BEGIN
	SET @Message = '--- Tables dropped successfully ---' 
END
ELSE
BEGIN
	SET @Message = '--- Error dropping tables ---'
END

PRINT @Message

The problem is that because I have foreign keys, the scrip throws errors. I tried a try-catch but then the query hangs. Ideally, I want the while loop to ignore errors, or at least not display them so that any errors displayed are errors with the create script. I do not want the exec within the while to rollback, which is what I believe the try-catch is doing. Any ideas how to do this?

Broken Bokken

You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod

http://www.brokenbokken.com

Re: Dropping all Tables

Harini N K23-Apr-08 20:44

Harini N K

23-Apr-08 20:44

Try the following SQL statement:

First drop all the child tables and then go for parent tables:

USE NORTHWIND

<br />
SELECT <br />
OBJECT_NAME(c.parent_object_id) ParentTableName,<br />
OBJECT_NAME(c.referenced_object_id) ChildTableName<br />
FROM   <br />
sys.foreign_key_columns c  <br />
INNER JOIN   <br />
sys.columns a ON a.object_id = c.referenced_object_id AND a.column_id = c.referenced_column_id  <br />

Hope this helps!!

Harini

Re: Dropping all Tables

Broken Bokken24-Apr-08 2:25

Broken Bokken

24-Apr-08 2:25

I used your solution, however, you must drop the parent tables first. The Child tables are the ones that error when you try to drop them. Here is the code in case anyone else wants to know how to do this. Just change {DatabaseName} to the name of the database you want to drop all the tables in.

SQL

USE {DatabaseName}
SET NOCOUNT ON

DECLARE @Message	varchar (1000)
DECLARE @TableName	varchar (100)
DECLARE @Count int

--############################################################################################
--		Drop Entities
--############################################################################################


---############### drop related entities first ###############---
SELECT @Count = Count(c.parent_object_id) 
	FROM sys.foreign_key_columns c
	INNER JOIN sys.columns a ON a.object_id = c.referenced_object_id 
	AND a.column_id = c.referenced_column_id	

---loop for tertiary or greater relationships
WHILE @Count > 0
BEGIN
	
	DECLARE tableCursor cursor FOR 
		SELECT OBJECT_NAME(c.parent_object_id) 
		FROM sys.foreign_key_columns c
		INNER JOIN sys.columns a ON a.object_id = c.referenced_object_id 
		AND a.column_id = c.referenced_column_id

	OPEN tableCursor

	FETCH NEXT FROM tableCursor INTO @TableName

	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			EXEC ('DROP TABLE ' + @TableName)
			PRINT '--- Dropped Table ' + @TableName + ' ---'
		END TRY
		BEGIN CATCH
		--eat any errors
		END CATCH

		FETCH NEXT FROM tableCursor INTO @TableName
	END

	CLOSE tableCursor
	DEALLOCATE tableCursor

SELECT @Count = Count(c.parent_object_id) 
	FROM sys.foreign_key_columns c
	INNER JOIN sys.columns a ON a.object_id = c.referenced_object_id 
	AND a.column_id = c.referenced_column_id
END

---############### drop the rest of the tables once the relationships have been dropped ###############---
SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'

WHILE @Count > 0
BEGIN
		--only parents left, so drop using msforeachtable
		EXEC sp_MSforeachtable "DROP TABLE ? PRINT '--- Dropped Table ? ---' ";	

		SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'
END

IF @Count = 0
BEGIN
	SET @Message = '--- Tables dropped successfully ---' 
END
ELSE
BEGIN
	SET @Message = '--- Error dropping tables ---'
END

PRINT @Message
GO

Broken Bokken

You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod

http://www.brokenbokken.com

Missing table in SQL Server

blakey40422-Apr-08 22:59

blakey404

22-Apr-08 22:59

an overnight datasync has failed because of a missnig table in SQL Server, does anyone know if there is a log anywhere which might show where it went or who dropped it?

Thanks

Re: Missing table in SQL Server

Marek Grzenkowicz22-Apr-08 23:36

Marek Grzenkowicz

22-Apr-08 23:36

What recovery model are you using?

Re: Missing table in SQL Server

blakey40422-Apr-08 23:49

blakey404

22-Apr-08 23:49

Simple

not my decision I've only been here a week...

Re: Missing table in SQL Server

Marek Grzenkowicz23-Apr-08 1:27

Marek Grzenkowicz

23-Apr-08 1:27

Is it SQL Server 2005?

If it is, you can try this[^].

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.