Registering Error: 2012

Friday, March 30, 2012

release database unused space

Hi,
I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile & DBCC shrinkdatabase to have the database release the space. I see no reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with another method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXDEFRAG.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile & DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> > Hi,
> >
> > I have deleted 100K records from a table in a database. I ran the DBCC
shrinkfile & DBCC shrinkdatabase to
> have the database release the space. I see no reduction in the database
size.
> >
> > I have run the following;
> > DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> > GO
> > DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> > GO
> > BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> > GO
> > DBCC SHRICKDATABASE (VIRTEN, 20)
> > GO
> > Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> > GO
> >
> > Please can anyone tell me, where I am making a mistake, or provide me
with another method to release the
> space.
> >
> > Many thanks.
> >
> > Daniel
>

release database unused space

Hi,
I have deleted 100K records from a table in a database. I ran the DBCC shrin
kfile & DBCC shrinkdatabase to have the database release the space. I see no
reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with a
nother method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXD
EFRAG.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile
& DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.">
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another
method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
shrinkfile & DBCC shrinkdatabase to[vbcol=seagreen]
> have the database release the space. I see no reduction in the database
size.
with another method to release the[vbcol=seagreen]
> space.
>sql

Release Database Transaction Log Used Space After Database Backup

What are the SQL Server commands that will shrink or release database
transaction log used disk space afer the database backup has completed?
Thank You,These two things are not related. A database backup will not empty the log.
A log backup will,
however. To then shrink the log file, use DBCC SHRINKFILE. But read
http://www.karaszi.com/SQLServer/info_dont_shrink.asp first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:85D71BCF-F22C-4035-8A02-16771C713B58@.microsoft.com...
> What are the SQL Server commands that will shrink or release database
> transaction log used disk space afer the database backup has completed?
> Thank You,

Release Database Transaction Log Used Space After Database Backup

What are the SQL Server commands that will shrink or release database
transaction log used disk space afer the database backup has completed?
Thank You,These two things are not related. A database backup will not empty the log. A log backup will,
however. To then shrink the log file, use DBCC SHRINKFILE. But read
http://www.karaszi.com/SQLServer/info_dont_shrink.asp first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:85D71BCF-F22C-4035-8A02-16771C713B58@.microsoft.com...
> What are the SQL Server commands that will shrink or release database
> transaction log used disk space afer the database backup has completed?
> Thank You,

Release database lock

Actually I'd like to know how I got the lock and then you can tell me how to release it. Here's the problem (this is sql server 2000 with visual studio 2005)

I have a windows service that wakes up every 5 minutes and does some database work (reads rows from a table, process them, inserts them into another table). When the inserts are complete, the process should (I would think) release the database lock, but it doesn't. The database is accessed via a dataset object and that object is disposed of via the using/end using statement. Is there something I have to put in the connection string to avoid this behavior?

Thanks,

Bob

If a connection is open from the client it will maintain an S lock on the database. This is true even if the client is doing no work. The purpose of the lock is to prevent certain operations that might change the state of the database (like say dropping it) while there are connections relying on the current state.

Is this causing you some problem?

|||I just don't understand how the connection could possibly still be open. I would assume that by using VS 2005's generated dataset code, that disposing of a dataset object would close the connection. I certainly don't want the connection open while the process sleeps for 5 minutes (or more) at a time.|||

If you are using SqlClient (and it sounds like you are) it could very well be taking advantage of connection pooling.

You can tell for certain by using profiler to monitor the Audit Login/logout events.

|||

Well this statement is no doubt in the code:

imports System.Data.SqlClient

Is that what you mean? If so, can I close the connection manually?

|||

I figured it out shortly after my last post. I added this to my connection string:

pooling=false;timeout=45;

seems to work just fine.

Release A Install Problems

We have packaged MSDE 2000 release A with our applicaiton. A user ran the
install, but when MSDE goes to start it says, MSDE is either currport or has
been tampered with, please uninstall and reinstall MSDE, invalid package ID.
We go to unstill MSDE and it says MSDE is not installed... We tried
reinstalling, since it thought it wasn't installed and when the installer
starts, we get the same invaled package id error... Does anyone have any
ideas, or thoughts on what is happening or what I can do to fix this? Thanks,
Brian
Did you define a strong password for SA using the SAPWD parameter? MSDE SP3A
setup.exe will not install a new instance without it.
joe.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:C8D287B9-BCD6-4423-AF48-21455901D596@.microsoft.com...
> We have packaged MSDE 2000 release A with our applicaiton. A user ran the
> install, but when MSDE goes to start it says, MSDE is either currport or
> has
> been tampered with, please uninstall and reinstall MSDE, invalid package
> ID.
> We go to unstill MSDE and it says MSDE is not installed... We tried
> reinstalling, since it thought it wasn't installed and when the installer
> starts, we get the same invaled package id error... Does anyone have any
> ideas, or thoughts on what is happening or what I can do to fix this?
> Thanks,
> Brian
>

Relay Error Using Database Mail

SQL 2005 SP1
Windows Server 2003 SP1
Exchange 2000
I am getting this error sending a Maintenance Plans report to an email
recipient. It only happens sending to an external email address.
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 2 (2007-02-06T08:01:25). Exception
Message: Cannot send mails to mail server. (Mailbox unavailable. The server
response was: 5.7.1 Unable to relay for myaddress@.domain.com).
)
This is an issue correct-able at your SMTP server; it has nothing to do with
database mail (unless you have a more open internal SMTP server available,
that you could use instead).
A
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:A35C82BD-00DA-414F-8071-2DD5F11EF010@.microsoft.com...
> SQL 2005 SP1
> Windows Server 2003 SP1
> Exchange 2000
> I am getting this error sending a Maintenance Plans report to an email
> recipient. It only happens sending to an external email address.
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 2 (2007-02-06T08:01:25). Exception
> Message: Cannot send mails to mail server. (Mailbox unavailable. The
> server
> response was: 5.7.1 Unable to relay for myaddress@.domain.com).
> )
|||Thanks for the reply. So what do I have to do on Exchange to correct this?
|||Well, you could open up external SMTP relay from the IP address of your SQL
Server(s). I am not an Exchange administrator. So for more specific
details, if you don't get a quick enough response here, you'll ghave better
luck in an Exchange newsgroup.
If you have a simple SMTP server running on an IIS box somewhere inside your
network, that you can use for this, I can help with specific instructions
there. Database Mail does not require Exchange, and depending on your
requirements, you may not want to mix the two.
A
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:D989979C-3692-4C0D-9BA9-87523863ED45@.microsoft.com...
> Thanks for the reply. So what do I have to do on Exchange to correct
> this?
sql

Relay Error Using Database Mail

SQL 2005 SP1
Windows Server 2003 SP1
Exchange 2000
I am getting this error sending a Maintenance Plans report to an email
recipient. It only happens sending to an external email address.
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 2 (2007-02-06T08:01:25). Exception
Message: Cannot send mails to mail server. (Mailbox unavailable. The server
response was: 5.7.1 Unable to relay for myaddress@.domain.com).
)This is an issue correct-able at your SMTP server; it has nothing to do with
database mail (unless you have a more open internal SMTP server available,
that you could use instead).
A
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:A35C82BD-00DA-414F-8071-2DD5F11EF010@.microsoft.com...
> SQL 2005 SP1
> Windows Server 2003 SP1
> Exchange 2000
> I am getting this error sending a Maintenance Plans report to an email
> recipient. It only happens sending to an external email address.
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 2 (2007-02-06T08:01:25). Exception
> Message: Cannot send mails to mail server. (Mailbox unavailable. The
> server
> response was: 5.7.1 Unable to relay for myaddress@.domain.com).
> )|||Thanks for the reply. So what do I have to do on Exchange to correct this?|||Well, you could open up external SMTP relay from the IP address of your SQL
Server(s). I am not an Exchange administrator. So for more specific
details, if you don't get a quick enough response here, you'll ghave better
luck in an Exchange newsgroup.
If you have a simple SMTP server running on an IIS box somewhere inside your
network, that you can use for this, I can help with specific instructions
there. Database Mail does not require Exchange, and depending on your
requirements, you may not want to mix the two.
A
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:D989979C-3692-4C0D-9BA9-87523863ED45@.microsoft.com...
> Thanks for the reply. So what do I have to do on Exchange to correct
> this?

Relay Error Using Database Mail

SQL 2005 SP1
Windows Server 2003 SP1
Exchange 2000
I am getting this error sending a Maintenance Plans report to an email
recipient. It only happens sending to an external email address.
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 2 (2007-02-06T08:01:25). Exception
Message: Cannot send mails to mail server. (Mailbox unavailable. The server
response was: 5.7.1 Unable to relay for myaddress@.domain.com).
)This is an issue correct-able at your SMTP server; it has nothing to do with
database mail (unless you have a more open internal SMTP server available,
that you could use instead).
A
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:A35C82BD-00DA-414F-8071-2DD5F11EF010@.microsoft.com...
> SQL 2005 SP1
> Windows Server 2003 SP1
> Exchange 2000
> I am getting this error sending a Maintenance Plans report to an email
> recipient. It only happens sending to an external email address.
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 2 (2007-02-06T08:01:25). Exception
> Message: Cannot send mails to mail server. (Mailbox unavailable. The
> server
> response was: 5.7.1 Unable to relay for myaddress@.domain.com).
> )|||Thanks for the reply. So what do I have to do on Exchange to correct this?|||Well, you could open up external SMTP relay from the IP address of your SQL
Server(s). I am not an Exchange administrator. So for more specific
details, if you don't get a quick enough response here, you'll ghave better
luck in an Exchange newsgroup.
If you have a simple SMTP server running on an IIS box somewhere inside your
network, that you can use for this, I can help with specific instructions
there. Database Mail does not require Exchange, and depending on your
requirements, you may not want to mix the two.
A
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:D989979C-3692-4C0D-9BA9-87523863ED45@.microsoft.com...
> Thanks for the reply. So what do I have to do on Exchange to correct
> this?

RelativeTime

Hi,

I have a traditional Time dimension, MatchDate (Year - Qtr - Month). I have created a dummy dimension RelativeTime in trying to copy the Cognos functionality. I have tried to created a calculated member:

SUM(PeriodsToDate([MatchDate].[Year]),([RelativeTime].[Relative Time].[Current]))

but it results in a errormess:

Formula error - cannot find dimension member

("[RelativeTime].[Relative Time].[Current]")) in a name-binding function.

Any idea anyone?

After modifying the MDX syntax its working fine:

SUM(PeriodsToDate([MatchDate].[Year]),([RelativeTime].[Relative Time].&[1]))

This is giving me the YTD total, how do I create a Previous YTD ? I will try using ParallelPeriod

Larra

RelativeTime

Hi,

SUM(PeriodsToDate([MatchDate].[Year]),([RelativeTime].[Relative Time].[Current]))

but it results in a errormess:

Formula error - cannot find dimension member

("[RelativeTime].[Relative Time].[Current]")) in a name-binding function.

Any idea anyone?

After modifying the MDX syntax its working fine:

SUM(PeriodsToDate([MatchDate].[Year]),([RelativeTime].[Relative Time].&[1]))

This is giving me the YTD total, how do I create a Previous YTD ? I will try using ParallelPeriod

Larra

Relatively new to SQL ...

How would I do something like this in a stored procedure?
Basically I want to declare ProductPrice as a sub routine type of thing..
Select * from products where {ProductPrice}
{ProductPrice}
(ProductID like "%Car%" or ProductID != NULL)
Help...
This is a simple example, the others I have require this kind of
functionality.
Thanks.
If I understand what you're trying to do correctly, you do this:
CREATE PROC ProductPrice
AS
SELECT * from products WHERE ProductID LIKE "%Car%" OR ProductID IS NOT
NULL)
GO
You'd normally want to pass a parameter though instead of hard-coding a
search term like "%car%". Also, the where clause you gave isn't correct...I
think you might have meant "= null" instead of not equals.
"AshVsAOD" wrote:

> How would I do something like this in a stored procedure?
> Basically I want to declare ProductPrice as a sub routine type of thing..
> Select * from products where {ProductPrice}
> {ProductPrice}
> (ProductID like "%Car%" or ProductID != NULL)
>
> Help...
> This is a simple example, the others I have require this kind of
> functionality.
> Thanks.
>
>
|||Yeah, I understand that. What I am after is. Can SQL use sub-procedures
like in my original piece of code?
[vbcol=seagreen]
Thanks for the help anyway.
"Mike Asher" <MikeAsher@.discussions.microsoft.com> wrote in message
news:FD3207AA-F1D0-460A-8979-E37E611A08B3@.microsoft.com...
> If I understand what you're trying to do correctly, you do this:
> CREATE PROC ProductPrice
> AS
> SELECT * from products WHERE ProductID LIKE "%Car%" OR ProductID IS NOT
> NULL)
> GO
> You'd normally want to pass a parameter though instead of hard-coding a
> search term like "%car%". Also, the where clause you gave isn't
correct...I[vbcol=seagreen]
> think you might have meant "= null" instead of not equals.
>
> "AshVsAOD" wrote:
thing..[vbcol=seagreen]
|||AshVsAOD wrote:
> How would I do something like this in a stored procedure?
> Basically I want to declare ProductPrice as a sub routine type of
> thing..
> Select * from products where {ProductPrice}
> {ProductPrice}
> (ProductID like "%Car%" or ProductID != NULL)
>
> Help...
> This is a simple example, the others I have require this kind of
> functionality.
> Thanks.
Firstly, you cannot compare to NULL in that method. Nothing is equal to
NULL, not even NULL. If you want to compare the value (or lack thereof)
of a column to NULL you have to use "Where ColName IS NULL" or "Where
ColName IS NOT NULL". As an exercise, run the following code and examine
the results:
create table #NullTest (col1 nvarchar(10))
go
Insert Into #NullTest Values (NULL)
Insert Into #NullTest Values (N'')
Insert Into #NullTest Values (N'ABC')
go
Select col1 from #NullTest Where col1 != NULL
Select col1 from #NullTest Where col1 = NULL
Select col1 from #NullTest Where col1 IS NOT NULL
Select col1 from #NullTest Where col1 IS NULL
Select col1 from #NullTest Where col1 = N'ABC'
Drop Table #NullTest
Secondly, your query if changed to IS NOT NULL, will return all rows
that do not have a NULL ProductID.
"ProductID like "%Car%" or ProductID IS NOT NULL"
This means give me all products that have "Car" somewhere in the name
_and_ all products where the product id is not null.
You should also avoid using SELECT * syntax for result sets.
I'm not sure exactly what your criteria for the procedure is. Do you
want to pass a ProductID to the procedure and have the procedure return
the rows that match? If so...
Create Proc dbo.GetProducts
@.ProductID nvarchar(100)
as
Begin
Select
Col1,
Col2,
Col3
From dbo.Products
Where ProductID LIKE @.ProductID
End
Go
If you only need to do equality comparisons, then you can change the
"LIKE" to an "=".
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Ok,
So my example was poorly constructed.
I know not to use select *, I also know my query was rubbish.
What I don't know is, can I use sub routine type code in SQL.
For example Select blah from products where {productprice}
{productprice}
whatever statement here...
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
> AshVsAOD wrote:
> Firstly, you cannot compare to NULL in that method. Nothing is equal to
> NULL, not even NULL. If you want to compare the value (or lack thereof)
> of a column to NULL you have to use "Where ColName IS NULL" or "Where
> ColName IS NOT NULL". As an exercise, run the following code and examine
> the results:
> create table #NullTest (col1 nvarchar(10))
> go
> Insert Into #NullTest Values (NULL)
> Insert Into #NullTest Values (N'')
> Insert Into #NullTest Values (N'ABC')
> go
> Select col1 from #NullTest Where col1 != NULL
> Select col1 from #NullTest Where col1 = NULL
> Select col1 from #NullTest Where col1 IS NOT NULL
> Select col1 from #NullTest Where col1 IS NULL
> Select col1 from #NullTest Where col1 = N'ABC'
> Drop Table #NullTest
>
> Secondly, your query if changed to IS NOT NULL, will return all rows
> that do not have a NULL ProductID.
> "ProductID like "%Car%" or ProductID IS NOT NULL"
> This means give me all products that have "Car" somewhere in the name
> _and_ all products where the product id is not null.
> You should also avoid using SELECT * syntax for result sets.
> I'm not sure exactly what your criteria for the procedure is. Do you
> want to pass a ProductID to the procedure and have the procedure return
> the rows that match? If so...
>
> Create Proc dbo.GetProducts
> @.ProductID nvarchar(100)
> as
> Begin
> Select
> Col1,
> Col2,
> Col3
> From dbo.Products
> Where ProductID LIKE @.ProductID
> End
> Go
> If you only need to do equality comparisons, then you can change the
> "LIKE" to an "=".
>
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||> Yeah, I understand that. What I am after is. Can SQL use sub-procedures
> like in my original piece of code?
Yes you can; one stored proc can call another, or itself recursively.
Return values other than result sets can be passed back to the caller via
output parameters. If you want to use in-line "function-type" syntax,
though, you'll need to code the procedure as a user-defined function.
|||As Mike Asher posted, you can call stored procs from other stored procs
but that's not what you're talking about here in your example (from what
I can tell). You're talking about nested sub-queries, which basically
are categorized into scalar subqueries (that return a single column,
single row) and correlated subqueries (which are dependent on one or
more values from the outer query). I'm guessing you want to do a
correlated subquery like:
select blah from products p
where exists (select * from orders o where o.productID = p.productID)
or
select blah from products p
where productID in (select productID from orders o where customer =
'ACME Corp')
Something like that. Of course this won't allow code reuse and there
are better ways to write these queries (for example quite often
correlated subqueries can be rewritten with joins instead of the
subquery and in many cases perform better with the joins) but in my
understanding that's basically what you were asking. You cannot call a
stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
statement but you can include subqueries against views and/or tables.
Additionally, the "tables" in the FROM clause can alternately be tables,
views or derived tables (basically a SELECT statement wrapped in
parentheses and given a table alias).
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AshVsAOD wrote:

>Ok,
>So my example was poorly constructed.
>I know not to use select *, I also know my query was rubbish.
>What I don't know is, can I use sub routine type code in SQL.
>For example Select blah from products where {productprice}
>{productprice}
>whatever statement here...
>
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
>
>
>
|||Thanks,
and sorry.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:OB6ktMvdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> As Mike Asher posted, you can call stored procs from other stored procs
> but that's not what you're talking about here in your example (from what
> I can tell). You're talking about nested sub-queries, which basically
> are categorized into scalar subqueries (that return a single column,
> single row) and correlated subqueries (which are dependent on one or
> more values from the outer query). I'm guessing you want to do a
> correlated subquery like:
> select blah from products p
> where exists (select * from orders o where o.productID = p.productID)
> or
> select blah from products p
> where productID in (select productID from orders o where customer =
> 'ACME Corp')
>
> Something like that. Of course this won't allow code reuse and there
> are better ways to write these queries (for example quite often
> correlated subqueries can be rewritten with joins instead of the
> subquery and in many cases perform better with the joins) but in my
> understanding that's basically what you were asking. You cannot call a
> stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
> statement but you can include subqueries against views and/or tables.
> Additionally, the "tables" in the FROM clause can alternately be tables,
> views or derived tables (basically a SELECT statement wrapped in
> parentheses and given a table alias).
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
http://www.mallesons.com
>
> AshVsAOD wrote:
>
|||Cheers!
"Mike Asher" <MikeAsher@.discussions.microsoft.com> wrote in message
news:316F437C-80F2-4127-846D-2A87C73960A2@.microsoft.com...[vbcol=seagreen]
sub-procedures
> Yes you can; one stored proc can call another, or itself recursively.
> Return values other than result sets can be passed back to the caller via
> output parameters. If you want to use in-line "function-type" syntax,
> though, you'll need to code the procedure as a user-defined function.
sql

Relatively new to SQL ...

How would I do something like this in a stored procedure'
Basically I want to declare ProductPrice as a sub routine type of thing..
Select * from products where {ProductPrice}
{ProductPrice}
(ProductID like "%Car%" or ProductID != NULL)
Help...
This is a simple example, the others I have require this kind of
functionality.
Thanks.If I understand what you're trying to do correctly, you do this:
CREATE PROC ProductPrice
AS
SELECT * from products WHERE ProductID LIKE "%Car%" OR ProductID IS NOT
NULL)
GO
You'd normally want to pass a parameter though instead of hard-coding a
search term like "%car%". Also, the where clause you gave isn't correct...I
think you might have meant "= null" instead of not equals.
"AshVsAOD" wrote:
> How would I do something like this in a stored procedure'
> Basically I want to declare ProductPrice as a sub routine type of thing..
> Select * from products where {ProductPrice}
> {ProductPrice}
> (ProductID like "%Car%" or ProductID != NULL)
>
> Help...
> This is a simple example, the others I have require this kind of
> functionality.
> Thanks.
>
>|||Yeah, I understand that. What I am after is. Can SQL use sub-procedures
like in my original piece of code?
> > Select * from products where {ProductPrice}
> >
> > {ProductPrice}
> > (ProductID like "%Car%" or ProductID != NULL)
Thanks for the help anyway.
"Mike Asher" <MikeAsher@.discussions.microsoft.com> wrote in message
news:FD3207AA-F1D0-460A-8979-E37E611A08B3@.microsoft.com...
> If I understand what you're trying to do correctly, you do this:
> CREATE PROC ProductPrice
> AS
> SELECT * from products WHERE ProductID LIKE "%Car%" OR ProductID IS NOT
> NULL)
> GO
> You'd normally want to pass a parameter though instead of hard-coding a
> search term like "%car%". Also, the where clause you gave isn't
correct...I
> think you might have meant "= null" instead of not equals.
>
> "AshVsAOD" wrote:
> > How would I do something like this in a stored procedure'
> >
> > Basically I want to declare ProductPrice as a sub routine type of
thing..
> >
> > Select * from products where {ProductPrice}
> >
> > {ProductPrice}
> > (ProductID like "%Car%" or ProductID != NULL)
> >
> >
> > Help...
> >
> > This is a simple example, the others I have require this kind of
> > functionality.
> >
> > Thanks.
> >
> >
> >|||AshVsAOD wrote:
> How would I do something like this in a stored procedure'
> Basically I want to declare ProductPrice as a sub routine type of
> thing..
> Select * from products where {ProductPrice}
> {ProductPrice}
> (ProductID like "%Car%" or ProductID != NULL)
>
> Help...
> This is a simple example, the others I have require this kind of
> functionality.
> Thanks.
Firstly, you cannot compare to NULL in that method. Nothing is equal to
NULL, not even NULL. If you want to compare the value (or lack thereof)
of a column to NULL you have to use "Where ColName IS NULL" or "Where
ColName IS NOT NULL". As an exercise, run the following code and examine
the results:
create table #NullTest (col1 nvarchar(10))
go
Insert Into #NullTest Values (NULL)
Insert Into #NullTest Values (N'')
Insert Into #NullTest Values (N'ABC')
go
Select col1 from #NullTest Where col1 != NULL
Select col1 from #NullTest Where col1 = NULL
Select col1 from #NullTest Where col1 IS NOT NULL
Select col1 from #NullTest Where col1 IS NULL
Select col1 from #NullTest Where col1 = N'ABC'
Drop Table #NullTest
Secondly, your query if changed to IS NOT NULL, will return all rows
that do not have a NULL ProductID.
"ProductID like "%Car%" or ProductID IS NOT NULL"
This means give me all products that have "Car" somewhere in the name
_and_ all products where the product id is not null.
You should also avoid using SELECT * syntax for result sets.
I'm not sure exactly what your criteria for the procedure is. Do you
want to pass a ProductID to the procedure and have the procedure return
the rows that match? If so...
Create Proc dbo.GetProducts
@.ProductID nvarchar(100)
as
Begin
Select
Col1,
Col2,
Col3
From dbo.Products
Where ProductID LIKE @.ProductID
End
Go
If you only need to do equality comparisons, then you can change the
"LIKE" to an "=".
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Ok,
So my example was poorly constructed.
I know not to use select *, I also know my query was rubbish.
What I don't know is, can I use sub routine type code in SQL.
For example Select blah from products where {productprice}
{productprice}
whatever statement here...
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
> AshVsAOD wrote:
> > How would I do something like this in a stored procedure'
> >
> > Basically I want to declare ProductPrice as a sub routine type of
> > thing..
> >
> > Select * from products where {ProductPrice}
> >
> > {ProductPrice}
> > (ProductID like "%Car%" or ProductID != NULL)
> >
> >
> > Help...
> >
> > This is a simple example, the others I have require this kind of
> > functionality.
> >
> > Thanks.
> Firstly, you cannot compare to NULL in that method. Nothing is equal to
> NULL, not even NULL. If you want to compare the value (or lack thereof)
> of a column to NULL you have to use "Where ColName IS NULL" or "Where
> ColName IS NOT NULL". As an exercise, run the following code and examine
> the results:
> create table #NullTest (col1 nvarchar(10))
> go
> Insert Into #NullTest Values (NULL)
> Insert Into #NullTest Values (N'')
> Insert Into #NullTest Values (N'ABC')
> go
> Select col1 from #NullTest Where col1 != NULL
> Select col1 from #NullTest Where col1 = NULL
> Select col1 from #NullTest Where col1 IS NOT NULL
> Select col1 from #NullTest Where col1 IS NULL
> Select col1 from #NullTest Where col1 = N'ABC'
> Drop Table #NullTest
>
> Secondly, your query if changed to IS NOT NULL, will return all rows
> that do not have a NULL ProductID.
> "ProductID like "%Car%" or ProductID IS NOT NULL"
> This means give me all products that have "Car" somewhere in the name
> _and_ all products where the product id is not null.
> You should also avoid using SELECT * syntax for result sets.
> I'm not sure exactly what your criteria for the procedure is. Do you
> want to pass a ProductID to the procedure and have the procedure return
> the rows that match? If so...
>
> Create Proc dbo.GetProducts
> @.ProductID nvarchar(100)
> as
> Begin
> Select
> Col1,
> Col2,
> Col3
> From dbo.Products
> Where ProductID LIKE @.ProductID
> End
> Go
> If you only need to do equality comparisons, then you can change the
> "LIKE" to an "=".
>
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||> Yeah, I understand that. What I am after is. Can SQL use sub-procedures
> like in my original piece of code?
Yes you can; one stored proc can call another, or itself recursively.
Return values other than result sets can be passed back to the caller via
output parameters. If you want to use in-line "function-type" syntax,
though, you'll need to code the procedure as a user-defined function.|||This is a multi-part message in MIME format.
--050701000406080309060203
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
As Mike Asher posted, you can call stored procs from other stored procs
but that's not what you're talking about here in your example (from what
I can tell). You're talking about nested sub-queries, which basically
are categorized into scalar subqueries (that return a single column,
single row) and correlated subqueries (which are dependent on one or
more values from the outer query). I'm guessing you want to do a
correlated subquery like:
select blah from products p
where exists (select * from orders o where o.productID = p.productID)
or
select blah from products p
where productID in (select productID from orders o where customer = 'ACME Corp')
Something like that. Of course this won't allow code reuse and there
are better ways to write these queries (for example quite often
correlated subqueries can be rewritten with joins instead of the
subquery and in many cases perform better with the joins) but in my
understanding that's basically what you were asking. You cannot call a
stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
statement but you can include subqueries against views and/or tables.
Additionally, the "tables" in the FROM clause can alternately be tables,
views or derived tables (basically a SELECT statement wrapped in
parentheses and given a table alias).
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AshVsAOD wrote:
>Ok,
>So my example was poorly constructed.
>I know not to use select *, I also know my query was rubbish.
>What I don't know is, can I use sub routine type code in SQL.
>For example Select blah from products where {productprice}
>{productprice}
>whatever statement here...
>
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
>
>>AshVsAOD wrote:
>>
>>How would I do something like this in a stored procedure'
>>Basically I want to declare ProductPrice as a sub routine type of
>>thing..
>>Select * from products where {ProductPrice}
>>{ProductPrice}
>>(ProductID like "%Car%" or ProductID != NULL)
>>
>>Help...
>>This is a simple example, the others I have require this kind of
>>functionality.
>>Thanks.
>>
>>Firstly, you cannot compare to NULL in that method. Nothing is equal to
>>NULL, not even NULL. If you want to compare the value (or lack thereof)
>>of a column to NULL you have to use "Where ColName IS NULL" or "Where
>>ColName IS NOT NULL". As an exercise, run the following code and examine
>>the results:
>>create table #NullTest (col1 nvarchar(10))
>>go
>>Insert Into #NullTest Values (NULL)
>>Insert Into #NullTest Values (N'')
>>Insert Into #NullTest Values (N'ABC')
>>go
>>Select col1 from #NullTest Where col1 != NULL
>>Select col1 from #NullTest Where col1 = NULL
>>Select col1 from #NullTest Where col1 IS NOT NULL
>>Select col1 from #NullTest Where col1 IS NULL
>>Select col1 from #NullTest Where col1 = N'ABC'
>>Drop Table #NullTest
>>
>>Secondly, your query if changed to IS NOT NULL, will return all rows
>>that do not have a NULL ProductID.
>>"ProductID like "%Car%" or ProductID IS NOT NULL"
>>This means give me all products that have "Car" somewhere in the name
>>_and_ all products where the product id is not null.
>>You should also avoid using SELECT * syntax for result sets.
>>I'm not sure exactly what your criteria for the procedure is. Do you
>>want to pass a ProductID to the procedure and have the procedure return
>>the rows that match? If so...
>>
>>Create Proc dbo.GetProducts
>>@.ProductID nvarchar(100)
>>as
>>Begin
>> Select
>> Col1,
>> Col2,
>> Col3
>> From dbo.Products
>> Where ProductID LIKE @.ProductID
>>End
>>Go
>>If you only need to do equality comparisons, then you can change the
>>"LIKE" to an "=".
>>
>>
>>--
>>David Gugick
>>Quest Software
>>www.imceda.com
>>www.quest.com
>>
>
>
--050701000406080309060203
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>As Mike Asher posted, you can call stored procs from other stored
procs but that's not what you're talking about here in your example
(from what I can tell). You're talking about nested sub-queries, which
basically are categorized into scalar subqueries (that return a single
column, single row) and correlated subqueries (which are dependent on
one or more values from the outer query). I'm guessing you want to do
a correlated subquery like: 
</tt>
<blockquote><tt>select blah from products p</tt> 
<tt>where exists (select * from orders o where o.productID =p.productID)</tt> 
</blockquote>
<tt>or 
</tt>
<blockquote><tt>select blah from products p</tt> 
<tt>where productID in (select productID from orders o where customer
= 'ACME Corp')</tt> 
</blockquote>
<tt> 
Something like that. Of course this won't allow code reuse and there
are better ways to write these queries (for example quite often
correlated subqueries can be rewritten with joins instead of the
subquery and in many cases perform better with the joins) but in my
understanding that's basically what you were asking. You cannot call a
stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
statement but you can include subqueries against views and/or tables.
Additionally, the "tables" in the FROM clause can alternately be
tables, views or derived tables (basically a SELECT statement wrapped
in parentheses and given a table alias). 
 
HTH 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
AshVsAOD wrote:
<blockquote cite="mide79syxudFHA.584@.TK2MSFTNGP15.phx.gbl" type="cite">
<pre wrap="">Ok,
So my example was poorly constructed.
I know not to use select *, I also know my query was rubbish.
What I don't know is, can I use sub routine type code in SQL.
For example Select blah from products where {productprice}
{productprice}
whatever statement here...
"David Gugick" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:david.gugick-nospam@.quest.com"><david.gugick-nospam@.quest.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl">news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">AshVsAOD wrote:
</pre>
<blockquote type="cite">
<pre wrap="">How would I do something like this in a stored procedure'
Basically I want to declare ProductPrice as a sub routine type of
thing..
Select * from products where {ProductPrice}
{ProductPrice}
(ProductID like "%Car%" or ProductID != NULL)
Help...
This is a simple example, the others I have require this kind of
functionality.
Thanks.
</pre>
</blockquote>
<pre wrap="">Firstly, you cannot compare to NULL in that method. Nothing is equal to
NULL, not even NULL. If you want to compare the value (or lack thereof)
of a column to NULL you have to use "Where ColName IS NULL" or "Where
ColName IS NOT NULL". As an exercise, run the following code and examine
the results:
create table #NullTest (col1 nvarchar(10))
go
Insert Into #NullTest Values (NULL)
Insert Into #NullTest Values (N'')
Insert Into #NullTest Values (N'ABC')
go
Select col1 from #NullTest Where col1 != NULL
Select col1 from #NullTest Where col1 = NULL
Select col1 from #NullTest Where col1 IS NOT NULL
Select col1 from #NullTest Where col1 IS NULL
Select col1 from #NullTest Where col1 = N'ABC'
Drop Table #NullTest
Secondly, your query if changed to IS NOT NULL, will return all rows
that do not have a NULL ProductID.
"ProductID like "%Car%" or ProductID IS NOT NULL"
This means give me all products that have "Car" somewhere in the name
_and_ all products where the product id is not null.
You should also avoid using SELECT * syntax for result sets.
I'm not sure exactly what your criteria for the procedure is. Do you
want to pass a ProductID to the procedure and have the procedure return
the rows that match? If so...
Create Proc dbo.GetProducts
@.ProductID nvarchar(100)
as
Begin
Select
Col1,
Col2,
Col3
From dbo.Products
Where ProductID LIKE @.ProductID
End
Go
If you only need to do equality comparisons, then you can change the
"LIKE" to an "=".
David Gugick
Quest Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.quest.com</a>">http://www.quest.com">www.quest.com</a>
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--050701000406080309060203--|||Thanks,
and sorry.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:OB6ktMvdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> As Mike Asher posted, you can call stored procs from other stored procs
> but that's not what you're talking about here in your example (from what
> I can tell). You're talking about nested sub-queries, which basically
> are categorized into scalar subqueries (that return a single column,
> single row) and correlated subqueries (which are dependent on one or
> more values from the outer query). I'm guessing you want to do a
> correlated subquery like:
> select blah from products p
> where exists (select * from orders o where o.productID = p.productID)
> or
> select blah from products p
> where productID in (select productID from orders o where customer => 'ACME Corp')
>
> Something like that. Of course this won't allow code reuse and there
> are better ways to write these queries (for example quite often
> correlated subqueries can be rewritten with joins instead of the
> subquery and in many cases perform better with the joins) but in my
> understanding that's basically what you were asking. You cannot call a
> stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
> statement but you can include subqueries against views and/or tables.
> Additionally, the "tables" in the FROM clause can alternately be tables,
> views or derived tables (basically a SELECT statement wrapped in
> parentheses and given a table alias).
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
http://www.mallesons.com
>
> AshVsAOD wrote:
> >Ok,
> >
> >So my example was poorly constructed.
> >
> >I know not to use select *, I also know my query was rubbish.
> >
> >What I don't know is, can I use sub routine type code in SQL.
> >
> >For example Select blah from products where {productprice}
> >
> >{productprice}
> >whatever statement here...
> >
> >
> >
> >"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> >news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
> >
> >
> >>AshVsAOD wrote:
> >>
> >>
> >>How would I do something like this in a stored procedure'
> >>
> >>Basically I want to declare ProductPrice as a sub routine type of
> >>thing..
> >>
> >>Select * from products where {ProductPrice}
> >>
> >>{ProductPrice}
> >>(ProductID like "%Car%" or ProductID != NULL)
> >>
> >>
> >>Help...
> >>
> >>This is a simple example, the others I have require this kind of
> >>functionality.
> >>
> >>Thanks.
> >>
> >>
> >>Firstly, you cannot compare to NULL in that method. Nothing is equal to
> >>NULL, not even NULL. If you want to compare the value (or lack thereof)
> >>of a column to NULL you have to use "Where ColName IS NULL" or "Where
> >>ColName IS NOT NULL". As an exercise, run the following code and examine
> >>the results:
> >>
> >>create table #NullTest (col1 nvarchar(10))
> >>go
> >>
> >>Insert Into #NullTest Values (NULL)
> >>Insert Into #NullTest Values (N'')
> >>Insert Into #NullTest Values (N'ABC')
> >>go
> >>
> >>Select col1 from #NullTest Where col1 != NULL
> >>Select col1 from #NullTest Where col1 = NULL
> >>Select col1 from #NullTest Where col1 IS NOT NULL
> >>Select col1 from #NullTest Where col1 IS NULL
> >>Select col1 from #NullTest Where col1 = N'ABC'
> >>
> >>Drop Table #NullTest
> >>
> >>
> >>Secondly, your query if changed to IS NOT NULL, will return all rows
> >>that do not have a NULL ProductID.
> >>
> >>"ProductID like "%Car%" or ProductID IS NOT NULL"
> >>
> >>This means give me all products that have "Car" somewhere in the name
> >>_and_ all products where the product id is not null.
> >>
> >>You should also avoid using SELECT * syntax for result sets.
> >>
> >>I'm not sure exactly what your criteria for the procedure is. Do you
> >>want to pass a ProductID to the procedure and have the procedure return
> >>the rows that match? If so...
> >>
> >>
> >>Create Proc dbo.GetProducts
> >>@.ProductID nvarchar(100)
> >>as
> >>Begin
> >> Select
> >> Col1,
> >> Col2,
> >> Col3
> >> From dbo.Products
> >> Where ProductID LIKE @.ProductID
> >>End
> >>Go
> >>
> >>If you only need to do equality comparisons, then you can change the
> >>"LIKE" to an "=".
> >>
> >>
> >>
> >>
> >>--
> >>David Gugick
> >>Quest Software
> >>www.imceda.com
> >>www.quest.com
> >>
> >>
> >>
> >
> >
> >
> >
>|||Cheers!
"Mike Asher" <MikeAsher@.discussions.microsoft.com> wrote in message
news:316F437C-80F2-4127-846D-2A87C73960A2@.microsoft.com...
> > Yeah, I understand that. What I am after is. Can SQL use
sub-procedures
> > like in my original piece of code?
> Yes you can; one stored proc can call another, or itself recursively.
> Return values other than result sets can be passed back to the caller via
> output parameters. If you want to use in-line "function-type" syntax,
> though, you'll need to code the procedure as a user-defined function.

Relatively new to SQL ...

Relatively easy SQL SELECT statement issues :/

Hi All,

I am trying to perform a relatively simple SELECT query. Firstly heres my 3 tables im working on:

CREATE TABLE business_contact
(
BusContactID INT NOT NULL AUTO_INCREMENT,
Title VARCHAR(5),
Surname VARCHAR(30),
FirstName VARCHAR(30),
PRIMARY KEY (BusContactID)
) TYPE = INNODB;

CREATE TABLE company
(
CompanyID INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Manager VARCHAR(25),
PRIMARY KEY (CompanyID)
) TYPE = INNODB;

CREATE TABLE works_for
(
CompanyID INT NOT NULL,
BusContactID INT NOT NULL,
Index (CompanyID),
FOREIGN KEY (CompanyID) REFERENCES company (CompanyID) ON UPDATE CASCADE ON DELETE CASCADE,
Index (BusContactID),
FOREIGN KEY (BusContactID) REFERENCES business_contact (BusContactID) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (CompanyID, BusContactID)
) TYPE = INNODB;

The 'company' table quite obviously stores details about a range of companies, the 'business_contact' about business contacts and the 'works_for' table uses the PK values from the previous tables to associate a contact with a particular employer.

What i want to do is to retrieve a list of all the contacts and (if applicable) the name of the company they work for. My knowledge of SQL is relatively limited and so far ive managed to retrieve the all the contact and the where applicable, the key value of the company a contact works for. So all i really need to do is replace the key value with the company name, but, i dont know how!! :confused:

Heres my current query:

SELECT business_contact.*, works_for.buscontactid
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid

Can anyone help me with fetching all the contacts in the table and if the contact works for a company then listing the name of the company?!?!?

Thanks in advance to anyone who can help

Damocles.SELECT business_contact.*, company.name
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid
LEFT JOIN company
ON works_for.companyid = company.companyid|||select business_contact.Title
, business_contact.Surname
, business_contact.FirstName
, company.Name
, company.Manager
from business_contact
left outer
join works_for
on business_contact.BusContactID
= works_for.BusContactID
left outer
join company
on works_for.CompanyID
= company.CompanyID|||Thanks to both for your replies, works great!

I could really do to read a decent SQL tutorial at some point!

Damocles.

Relative speed of physical and logical I/Os

How much faster is a database access that is serviced from cache compared to
retrieved from disk. Obviously this is dependent on the specifics of disk
configurations, processor speed, memory speed, etc. but in terms of order of
magnitude, is cache access 100, 1000, 10000 times quicker or more?
Hi
Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
In theory. 250x.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robin East" <robin_east@.hotmail.com> wrote in message
news:eg9vatBcFHA.228@.TK2MSFTNGP12.phx.gbl...
> How much faster is a database access that is serviced from cache compared
> to retrieved from disk. Obviously this is dependent on the specifics of
> disk configurations, processor speed, memory speed, etc. but in terms of
> order of magnitude, is cache access 100, 1000, 10000 times quicker or
> more?
>
|||Robin East wrote:
> How much faster is a database access that is serviced from cache
> compared to retrieved from disk. Obviously this is dependent on the
> specifics of disk configurations, processor speed, memory speed, etc.
> but in terms of order of magnitude, is cache access 100, 1000, 10000
> times quicker or more?
Memory access is measured in nanoseconds 10^-9. Disk access is measured
in milliseconds. 10^-3. This difference is extreme and measurable in
production systems.
The thing to remember when thinking of disk access in a RDBMS
environment is that the access does not occur in isolation. That is, a
single access to data on disk may take 3ms, which could return data in
an acceptable amount of time, but if 100 users all want data from disk,
that 3ms can look more like 30ms. And a 1,000 users can make it look
like 3 seconds. Contention in memory is much less noticeable because of
the increased speed.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
> In theory. 250x.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
I think it's worse than that. Isn't 15ms = 15,000,000ns
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:et9x2$BcFHA.3620@.TK2MSFTNGP09.phx.gbl...
> The thing to remember when thinking of disk access in a RDBMS
> environment is that the access does not occur in isolation. That is, a
Just to add to this, I'd like to point out for the OP that this can be
monitored to some degree by watching the PhysicalDisk:Avg. Disk Read Queue
Length counter in perfmon. You can learn a lot about why you're having
performance problems by watching that counter... Also check out the
SqlServer:CacheManager:Cache Hit Ratio counter at the same time. What you'd
like to see is the queue length drop as the cache hit ratio goes up (meaning
that data in cache is being hit instead of the disk.)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
|||As a rule of thumb: 100 to 1000 times faster.
To give you an idea: I ran a big query on one of my older servers (with
2 700Mhz CPU's and 4 10K RPM drives).
With a hot cache the results were: Scan count 2, logical reads 46536,
physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
= 842 ms.
With a cold cache, the results were: Scan count 2, logical reads 46541,
physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
time = 89005 ms.
As you can see, with a hot cache, the query took 0.8 seconds, with a
cold cache it took 89.0 seconds. With small reads, sequential read is
not possible (read-ahead count will usually be 0). Random reads are
slower than sequential reads, so what you are seeing in the example
above is really the best case for a cold cache.
HTH,
Gert-Jan
Robin East wrote:
> How much faster is a database access that is serviced from cache compared to
> retrieved from disk. Obviously this is dependent on the specifics of disk
> configurations, processor speed, memory speed, etc. but in terms of order of
> magnitude, is cache access 100, 1000, 10000 times quicker or more?
|||Thanks everyone who replied. I think Gerts answer was closest to what I was
looking for and expecting. I have greater experience with Oracle where, as a
general rule, the figure is closer to 100 than a 1000.
As a matter of interest, Gert, how come the hot cache results gave CPU
1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed time
before but usually only about 10-15ms.
Robin
>Re: Relative speed of physical and logical I/Os
>From: Gert-Jan Strik
>Date Posted: 6/13/2005 11:45:00 AM
>
[vbcol=seagreen]
>As a rule of thumb: 100 to 1000 times faster.
>To give you an idea: I ran a big query on one of my older servers (with
>2 700Mhz CPU's and 4 10K RPM drives).
>With a hot cache the results were: Scan count 2, logical reads 46536,
>physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
>= 842 ms.
>With a cold cache, the results were: Scan count 2, logical reads 46541,
>physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
>time = 89005 ms.
>As you can see, with a hot cache, the query took 0.8 seconds, with a
>cold cache it took 89.0 seconds. With small reads, sequential read is
>not possible (read-ahead count will usually be 0). Random reads are
>slower than sequential reads, so what you are seeing in the example
>above is really the best case for a cold cache.
>HTH,
>Gert-Jan
>
>Robin East wrote:
|||Robin East wrote:
> Thanks everyone who replied. I think Gerts answer was closest to what
> I was looking for and expecting. I have greater experience with
> Oracle where, as a general rule, the figure is closer to 100 than a
> 1000.
> As a matter of interest, Gert, how come the hot cache results gave CPU
> 1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed
> time before but usually only about 10-15ms.
>
That's because of a parallel plan on more than one processor. Parallel
plans are more expensive that their single-cpu plans, but with the
additional available CPUs, they can complete faster.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Of course, obvious really
regards
Robin
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:exPusPPcFHA.612@.TK2MSFTNGP12.phx.gbl...
> Robin East wrote:
> That's because of a parallel plan on more than one processor. Parallel
> plans are more expensive that their single-cpu plans, but with the
> additional available CPUs, they can complete faster.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Relative speed of physical and logical I/Os

How much faster is a database access that is serviced from cache compared to
retrieved from disk. Obviously this is dependent on the specifics of disk
configurations, processor speed, memory speed, etc. but in terms of order of
magnitude, is cache access 100, 1000, 10000 times quicker or more?Hi
Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
In theory. 250x.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robin East" <robin_east@.hotmail.com> wrote in message
news:eg9vatBcFHA.228@.TK2MSFTNGP12.phx.gbl...
> How much faster is a database access that is serviced from cache compared
> to retrieved from disk. Obviously this is dependent on the specifics of
> disk configurations, processor speed, memory speed, etc. but in terms of
> order of magnitude, is cache access 100, 1000, 10000 times quicker or
> more?
>|||Robin East wrote:
> How much faster is a database access that is serviced from cache
> compared to retrieved from disk. Obviously this is dependent on the
> specifics of disk configurations, processor speed, memory speed, etc.
> but in terms of order of magnitude, is cache access 100, 1000, 10000
> times quicker or more?
Memory access is measured in nanoseconds 10^-9. Disk access is measured
in milliseconds. 10^-3. This difference is extreme and measurable in
production systems.
The thing to remember when thinking of disk access in a RDBMS
environment is that the access does not occur in isolation. That is, a
single access to data on disk may take 3ms, which could return data in
an acceptable amount of time, but if 100 users all want data from disk,
that 3ms can look more like 30ms. And a 1,000 users can make it look
like 3 seconds. Contention in memory is much less noticeable because of
the increased speed.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
> In theory. 250x.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
I think it's worse than that. Isn't 15ms = 15,000,000ns
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:et9x2$BcFHA.3620@.TK2MSFTNGP09.phx.gbl...
> The thing to remember when thinking of disk access in a RDBMS
> environment is that the access does not occur in isolation. That is, a
Just to add to this, I'd like to point out for the OP that this can be
monitored to some degree by watching the PhysicalDisk:Avg. Disk Read Queue
Length counter in perfmon. You can learn a lot about why you're having
performance problems by watching that counter... Also check out the
SqlServer:CacheManager:Cache Hit Ratio counter at the same time. What you'd
like to see is the queue length drop as the cache hit ratio goes up (meaning
that data in cache is being hit instead of the disk.)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||As a rule of thumb: 100 to 1000 times faster.
To give you an idea: I ran a big query on one of my older servers (with
2 700Mhz CPU's and 4 10K RPM drives).
With a hot cache the results were: Scan count 2, logical reads 46536,
physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
= 842 ms.
With a cold cache, the results were: Scan count 2, logical reads 46541,
physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
time = 89005 ms.
As you can see, with a hot cache, the query took 0.8 seconds, with a
cold cache it took 89.0 seconds. With small reads, sequential read is
not possible (read-ahead count will usually be 0). Random reads are
slower than sequential reads, so what you are seeing in the example
above is really the best case for a cold cache.
HTH,
Gert-Jan
Robin East wrote:
> How much faster is a database access that is serviced from cache compared
to
> retrieved from disk. Obviously this is dependent on the specifics of disk
> configurations, processor speed, memory speed, etc. but in terms of order
of
> magnitude, is cache access 100, 1000, 10000 times quicker or more?|||Thanks everyone who replied. I think Gerts answer was closest to what I was
looking for and expecting. I have greater experience with Oracle where, as a
general rule, the figure is closer to 100 than a 1000.
As a matter of interest, Gert, how come the hot cache results gave CPU
1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed time
before but usually only about 10-15ms.
Robin
>Re: Relative speed of physical and logical I/Os
>From: Gert-Jan Strik
>Date Posted: 6/13/2005 11:45:00 AM
>
[vbcol=seagreen]
>As a rule of thumb: 100 to 1000 times faster.
>To give you an idea: I ran a big query on one of my older servers (with
>2 700Mhz CPU's and 4 10K RPM drives).
>With a hot cache the results were: Scan count 2, logical reads 46536,
>physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
>= 842 ms.
>With a cold cache, the results were: Scan count 2, logical reads 46541,
>physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
>time = 89005 ms.
>As you can see, with a hot cache, the query took 0.8 seconds, with a
>cold cache it took 89.0 seconds. With small reads, sequential read is
>not possible (read-ahead count will usually be 0). Random reads are
>slower than sequential reads, so what you are seeing in the example
>above is really the best case for a cold cache.
>HTH,
>Gert-Jan
>
>Robin East wrote:|||Robin East wrote:
> Thanks everyone who replied. I think Gerts answer was closest to what
> I was looking for and expecting. I have greater experience with
> Oracle where, as a general rule, the figure is closer to 100 than a
> 1000.
> As a matter of interest, Gert, how come the hot cache results gave CPU
> 1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed
> time before but usually only about 10-15ms.
>
That's because of a parallel plan on more than one processor. Parallel
plans are more expensive that their single-cpu plans, but with the
additional available CPUs, they can complete faster.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Of course, obvious really
regards
Robin
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:exPusPPcFHA.612@.TK2MSFTNGP12.phx.gbl...
> Robin East wrote:
> That's because of a parallel plan on more than one processor. Parallel
> plans are more expensive that their single-cpu plans, but with the
> additional available CPUs, they can complete faster.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Relative speed of physical and logical I/Os

Thanks everyone who replied. I think Gerts answer was closest to what I was
looking for and expecting. I have greater experience with Oracle where, as a
general rule, the figure is closer to 100 than a 1000.
As a matter of interest, Gert, how come the hot cache results gave CPU
1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed time
before but usually only about 10-15ms.
Robin
>Re: Relative speed of physical and logical I/Os
>From: Gert-Jan Strik
>Date Posted: 6/13/2005 11:45:00 AM
>
>As a rule of thumb: 100 to 1000 times faster.
>
>To give you an idea: I ran a big query on one of my older servers (with
>2 700Mhz CPU's and 4 10K RPM drives).
>
>With a hot cache the results were: Scan count 2, logical reads 46536,
>physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
>= 842 ms.
>
>With a cold cache, the results were: Scan count 2, logical reads 46541,
>physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
>time = 89005 ms.
>
>As you can see, with a hot cache, the query took 0.8 seconds, with a
>cold cache it took 89.0 seconds. With small reads, sequential read is
>not possible (read-ahead count will usually be 0). Random reads are
>slower than sequential reads, so what you are seeing in the example
>above is really the best case for a cold cache.
>
>HTH,
>Gert-Jan
>
>
>Robin East wrote:
>>
>> How much faster is a database access that is serviced from cache compared
>> to
>> retrieved from disk. Obviously this is dependent on the specifics of disk
>> configurations, processor speed, memory speed, etc. but in terms of order
>> of
>> magnitude, is cache access 100, 1000, 10000 times quicker or more?Robin East wrote:
> Thanks everyone who replied. I think Gerts answer was closest to what
> I was looking for and expecting. I have greater experience with
> Oracle where, as a general rule, the figure is closer to 100 than a
> 1000.
> As a matter of interest, Gert, how come the hot cache results gave CPU
> 1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed
> time before but usually only about 10-15ms.
>
That's because of a parallel plan on more than one processor. Parallel
plans are more expensive that their single-cpu plans, but with the
additional available CPUs, they can complete faster.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Of course, obvious really
regards
Robin
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:exPusPPcFHA.612@.TK2MSFTNGP12.phx.gbl...
> Robin East wrote:
>> Thanks everyone who replied. I think Gerts answer was closest to what
>> I was looking for and expecting. I have greater experience with
>> Oracle where, as a general rule, the figure is closer to 100 than a
>> 1000.
>> As a matter of interest, Gert, how come the hot cache results gave CPU
>> 1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed
>> time before but usually only about 10-15ms.
> That's because of a parallel plan on more than one processor. Parallel
> plans are more expensive that their single-cpu plans, but with the
> additional available CPUs, they can complete faster.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Relative Performance: Native SQL vs User Functions

Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and where
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and where
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.

Friday, March 30, 2012

release database unused space

release database unused space

Release Database Transaction Log Used Space After Database Backup

Release Database Transaction Log Used Space After Database Backup

Release database lock

Release A Install Problems

Relay Error Using Database Mail

Relay Error Using Database Mail

Relay Error Using Database Mail

RelativeTime

RelativeTime

Relatively new to SQL ...

Relatively new to SQL ...

Relatively new to SQL ...

Relatively easy SQL SELECT statement issues :/

Relative speed of physical and logical I/Os

Relative speed of physical and logical I/Os

Relative speed of physical and logical I/Os

Relative speed of physical and logical I/Os

Relative Performance: Native SQL vs User Functions

Registering Error

Blog Archive

About Me