How would I add:
WHERE Year(tblDetails.DateAdded)=#2003#
... to the SQL statement below?
"SELECT tblDetails.ProductID, tblProducts.ShortDesc, Count(tblDetails.ProductID) AS ProductCount FROM (tblDetails INNER JOIN tblProducts ON tblDetails.ProductID = tblProducts.ProductID) GROUP BY tblDetails.ProductID, tblProducts.ShortDesc ORDER BY Count(tblDetails.ProductID) DESC"
Cheers,
Davidi think the order is
sql
select...
from...
where...
group by ...
having...
order by...
ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;
I realize that all connections expect the query window need to be
closed and that is the case I think, or at least we are resetting the
web server and still see the issue. The only way I have been able to
fix it is to completely stop and restart the database server, then
issue the command and it returns immediately.
This is a pain though and has to be done after hours. Is there a way to
issue the command while the system is in use, possibly taking just that
database offline (and not all other Dbs on the server) for a short time
and then returning it back to use using just scripting?pb648174 (google@.webpaul.net) writes:
> When issuing the below command on any of our databases, it just hangs
> forever.
> ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;
> I realize that all connections expect the query window need to be
> closed and that is the case I think, or at least we are resetting the
> web server and still see the issue. The only way I have been able to
> fix it is to completely stop and restart the database server, then
> issue the command and it returns immediately.
Did you use sp_who to see what other connections to the database that
were active?
You can use
ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
as a guick way to get everyone out. Don't forget to set it back to
multi user when you are done.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The problems happen intermittently, so I can't really execute the
sp_who statement when there is an error. Theoretically, how could it
ever block? Shouldn't it just get the version of the row before the
transaction started for the read operation?|||Whoops, wrong message, nevermind|||In case anyone is interested, here is the final solution we came up
with. It assumes the current DB is the one you want to set for snapshot
mania and will execute only on SQL 2005 without throwing errors on SQL
2000.
if(charindex('Microsoft SQL Server 2005',@.@.version) > 0)
begin
declare @.sql varchar(8000)
select @.sql = '
ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK
IMMEDIATE ;
ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;
'
Exec(@.sql)
end
go|||pb648174 (google@.webpaul.net) writes:
> In case anyone is interested, here is the final solution we came up
> with. It assumes the current DB is the one you want to set for snapshot
> mania and will execute only on SQL 2005 without throwing errors on SQL
> 2000.
> if(charindex('Microsoft SQL Server 2005',@.@.version) > 0)
A somewhat simpler test:
if serverproperty('ProductVersion') not like '[1-8].%'
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||if CAST(serverproperty('ProductVersion') as varchar) not like '[1-8].%'
Erland Sommarskog wrote:
> pb648174 (google@.webpaul.net) writes:
> > In case anyone is interested, here is the final solution we came up
> > with. It assumes the current DB is the one you want to set for snapshot
> > mania and will execute only on SQL 2005 without throwing errors on SQL
> > 2000.
> > if(charindex('Microsoft SQL Server 2005',@.@.version) > 0)
> A somewhat simpler test:
> if serverproperty('ProductVersion') not like '[1-8].%'
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi *,
Is there any simpler way to set Isolation level means not
using the PL/SQL block.
~Vivek
--
sharma_vivek_us
----------------------
sharma_vivek_us's Profile: http://www.dbtalk.net/m441
View this thread: http://www.dbtalk.net/t309038|||sharma_vivek_us (sharma_vivek_us.29osdz@.no-mx.forums.yourdomain.com.au)
writes:
> Is there any simpler way to set Isolation level means not
> using the PL/SQL block.
Could you clarify what you want to achieve?
"PL/SQL block" is not something you need to use with SQL Server - or even
can. PL/SQL is Oracle or DB2.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What's you hardware conf.? You may want to check the controller's
behavior...The versionning behind READ_COMMITTED_SNAPSHOT imposes super
heavy IO overhead on the controller...Set up a trace on Physical Disk:
Average sec per/write
Physical Disk: Average sec per/read and monitor until next
problem...Intermittent behaviors often result of a extreme disphase
between physical resources and logical need...
pb648174 wrote:
> When issuing the below command on any of our databases, it just hangs
> forever.
> ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;
> I realize that all connections expect the query window need to be
> closed and that is the case I think, or at least we are resetting the
> web server and still see the issue. The only way I have been able to
> fix it is to completely stop and restart the database server, then
> issue the command and it returns immediately.
> This is a pain though and has to be done after hours. Is there a way to
> issue the command while the system is in use, possibly taking just that
> database offline (and not all other Dbs on the server) for a short time
> and then returning it back to use using just scripting?|||FYI
READ_COMMITED_SNAPSHOT is the early MS attempts at implementing ORACLE
Read Consistency versionning...
sharma_vivek_us wrote:
> Hi *,
> Is there any simpler way to set Isolation level means not
> using the PL/SQL block.
>
> ~Vivek
>
> --
> sharma_vivek_us
> ----------------------
> sharma_vivek_us's Profile: http://www.dbtalk.net/m441
> View this thread: http://www.dbtalk.net/t309038
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public GetFilePath As String = ""
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
GetFilePath = "C:\Rapagator\Downloaded" + Row.FileName
End Sub
Public Overrides Sub PostExecute()
Variables.FilePath = GetFilePath
MyBase.PostExecute()
End Sub
End Class
How do you know the variable value is only the filename part you state? You cannot debug inside the Script Component, so perhaps add a MessageBox or log an event to write out the values you have just to be sure. Perhaps set a breakpoint as well and add the variable to the watch window. You do know that the Variables window only shows design-time values during runtime, even when completed or stopped on a breakpoint. Use the watch window to get real values. This does seem rather strange, are you only expecting one row to flow through this component; otherwise you will only store the last row's value in the variable. Is that what you want?
Does the filename start with a "\" ? If not the path looks invalid - C:\Rapagator\DownloadedMYFILE. Top tip, use System.IO.Path.Combine(x, y) as that will sort out path qualifiers for you.
Handling # table is giving problem.
Does anybody have solution/suggestion for the below given problem.
Create a #TMPTAB table
--------
SELECT GETDATE() MYDATE INTO #TMPTAB
Select #TMPTAB Table
------
SELECT NAME FROM TEMPDB..sysobjects where name ='#TMPTAB'
OR
SELECT NAME FROM sysobjects where name ='#TMPTAB'
--------
It does not return the record.
(Because SQL Server create # table with an system generated unique ID
in Name like (#TMPTAB________________________________000000011F 8F))
-----
But if I select it with Like clause:
SELECT NAME FROM TEMPDB..sysobjects where name ='#TMPTAB'
It return the name:
#TMPTAB________________________________000000011F8 F
When I drop the table it canbe drop with
DROP TABLE #TMPTAB
(EVEN IF I'M USING SOME OTHER DATA BASE 'XDATABASE')
NOW THE PROBLEM IS:
====================
IF I CHECK TABLE WITH LIKE CLAUSE IT WILL RETURN ME ALL # TABLE, WHICH
HAS BEEN CREATED IN TEMPDB.. DATABASE BY DIFFERENT USER/CLIENT).
IN A SCENERIO WHERE A PARTICULAR CONNECTION DOES NOT HAVE MADE ANY #
TABLE, BUT IT HAS MADE FROM SOME ANOTHER INSTANCE.
IF I SEARCH TABLE WITH LIKE CLAUSE (SELECT NAME FROM
TEMPDB..sysobjects where name ='#TMPTAB'), IT WILL RETURN TRUE AND
THEN AT THE TIME OF DROPPING TABLE IT WILL RETURN ERROR.
WHY THIS HAPPEN?
DOES ANYBODY HAVE SOME SOLUTION/SUGGETION ON IT.
THANKS IN ADV.
T.S.NEGI
tilak.negi@.mind-infotech.comUse OBJECT_ID to test for the existence of a temp table:
IF OBJECT_ID('tempdb..#tmptab') IS NOT NULL
...
--
David Portas
SQL Server MVP
--
