Data files in Flash Drive

Hi friends,
i want to know something abt sql server installation and database properties as under.
1) how much disk space reqd by multiuser sqlserver 2000 db to install ?
2) is it possible to install sql 2K on Flash Drive ( gen connect at USB) ?
3) When v create a database user Enterprise Manager , which files created by database ?
4) If i Know datafiles name of a database e.g. pubs , and if v copied on cd and paste on other system which has sql 2K install , does those database works ?
5) is it possible to create database on flash drive ?

thanx in advance.Come on...are these trick questions?|||I suppose it could be done, but you would have to be very sure that you properly detach the database before someone goes and unplugs the USB drive. I think you would set yourself up for a heck of a lot more user errors than SQL Server errors this way.

data base name

I have to migrate one data base from sqlserver 7 to
another sql server 7. the data base name was 'Item CS'
there is a blank in the name (between Item and CS).
I used the restore command to restore from backup copy
but it got a syntax error.
RESTORE DATABASE Item CS FROM c:\backup\Item CS.BAK
This command gave an error because a 'blank' between Item
CS
How can we get around to use the restore command for a
data base with this kind of name ?
Thanks
VanTry:
RESTORE DATABASE [Item CS]
FROM DISK='c:\backup\Item CS.BAK'
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"VanHo" <vanho@.ispwest.com> wrote in message
news:08f301c378eb$e5b70390$a301280a@.phx.gbl...
> I have to migrate one data base from sqlserver 7 to
> another sql server 7. the data base name was 'Item CS'
> there is a blank in the name (between Item and CS).
> I used the restore command to restore from backup copy
> but it got a syntax error.
> RESTORE DATABASE Item CS FROM c:\backup\Item CS.BAK
> This command gave an error because a 'blank' between Item
> CS
>
> How can we get around to use the restore command for a
> data base with this kind of name ?
> Thanks
> Van

Data Acess aplication block

Hi
I have Microsoft Enterprise Library 2005 installed on my local system.
I m also using ASp.net 1.1 And C3 as coding language , I have MS Sql
Server 2000.
I am developing a web application in which i am using data Access
Application Block for data access.
I have a remote web n data server with asp.net 1.1 support
the application is working fine here on the local server(web & data).
when i change the data server path to the remote server it also working
fine but
When I uploaded the application on the remote server n tested there it
gives me the following err
ex.message was
[vbcol=seagreen]
ex.Source was
.........Microsoft.Practices.EnterpriseLibrary.Data
ex.trace was
at
Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnectionFai
ledEvent.Fire(String
connectionString) at
Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataInstrumentati
onFacade.ConnectionFailed(String
connectionString) at
Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DBCommandW
rapper
command) at property.m4.Button2_Click(Object sender, EventArgs e)
Plz help me why this is happening.........
Mukesh Kumar agarwal
Delhi
IndiaMukesh,
Are you sure that the user that ASP.NET is running under has access to
the database you are trying to reach? It seems as if this is an error on
connecting.
Hope this helps.
- Nicholas Paldino [.NET/C# MVP]
- mvp@.spam.guard.caspershouse.com
"Mukesh" <efextra@.newsgroups.nospam> wrote in message
news:u6JBICB1GHA.4264@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have Microsoft Enterprise Library 2005 installed on my local system.
> I m also using ASp.net 1.1 And C3 as coding language , I have MS Sql
> Server 2000.
> I am developing a web application in which i am using data Access
> Application Block for data access.
> I have a remote web n data server with asp.net 1.1 support
> the application is working fine here on the local server(web & data).
> when i change the data server path to the remote server it also working
> fine but
> When I uploaded the application on the remote server n tested there it
> gives me the following err
>
> ex.message was
>
> ex.Source was
> .........Microsoft.Practices.EnterpriseLibrary.Data
> ex.trace was
> at
> Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnectionF
ailedEvent.Fire(String
> connectionString) at
> Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataInstrumenta
tionFacade.ConnectionFailed(String
> connectionString) at
> Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at
> Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DBComman
dWrapper
> command) at property.m4.Button2_Click(Object sender, EventArgs e)
>
> Plz help me why this is happening.........
> Mukesh Kumar agarwal
> Delhi
> India
>
>
>|||The error you are getting is because the enterprise library wants to write
events to the event log and setting this up requires registry permissions.
You need to installutil the dlls for the enterprise library (this is covered
in the docs somewhere). The reason it is writing to the event log is because
the connection failed. If you setup the event log access correctly then you
will have an eventlog entry telling you why the connection failed.
HTH
Ciaran O'Donnell
"Mukesh" wrote:

> Hi
> I have Microsoft Enterprise Library 2005 installed on my local system.
> I m also using ASp.net 1.1 And C3 as coding language , I have MS Sql
> Server 2000.
> I am developing a web application in which i am using data Access
> Application Block for data access.
> I have a remote web n data server with asp.net 1.1 support
> the application is working fine here on the local server(web & data).
> when i change the data server path to the remote server it also working
> fine but
> When I uploaded the application on the remote server n tested there it
> gives me the following err
>
> ex.message was
>
> ex.Source was
> ..........Microsoft.Practices.EnterpriseLibrary.Data
> ex.trace was
> at
> Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnectionF
ailedEvent.Fire(String
> connectionString) at
> Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataInstrumenta
tionFacade.ConnectionFailed(String
> connectionString) at
> Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at
> Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DBComman
dWrapper
> command) at property.m4.Button2_Click(Object sender, EventArgs e)
>
> Plz help me why this is happening.........
> Mukesh Kumar agarwal
> Delhi
> India
>
>
>

Data access From sqlserver 2000

Hello

i have one prob . i m using sql server 2000 . and i have write a store procedure to fetch data from multiple table using cursor . this query exculate in sql server . but i can't fetch this data from in our page . how to fetch data from using multiple table .plz help

thnx

Hi shruram.gore,

You don't need to use cursor for this unless you need to do some operations (processess) before displaying the data.
Cursor will not help you in displying the data, to display the data use SELECT and if you need to fetch the data from more than one table, use JOIN.

Example:

SELECT a.col1, a.col2, b.col2FROM table1 aINNERJOIN table2 bon (a.id = b.id)

Good luck.

|||

thnx , for suggestion

But dont have fix table name . in my prob my table name is variable plz check the query . this query we will use to bind treeView to show parent child releation . i have execute data from this query but we cant fetch data in dataset

thanx in advance

CREATE PROCEDURE [dbo].GetRootChild
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
declare @.parentId int
declare @.TableName varchar (50)
declare @.RelIDName char(10),@.s int,@.sql varchar(4000)

declare comments_list CURSOR FOR
select distinct [PcRelation].[ChID],[ObjectType].[ObjName],[ObjectType].[RelIdName] from PcRelation,ObjectType where [PcRelation].[ChObjectID]=[ObjectType].[ID] and [PcRelation].[PID]='0' and [PcRelation].[PObjectID]='0'
OPEN comments_list
FETCH NEXT FROM comments_list INTO @.parentId,@.TableName,@.RelIDName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE product_cursor CURSOR FOR
select @.TableName as TableName
--select * from Company
select @.sql ='select * from [dbo].['+ @.TableName +'] where ['+ @.TableName +'].['+@.RelIDName+']= '+convert(char(10),@.parentId)+''
OPEN product_cursor
FETCH NEXT FROM product_cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
--select @.TableName as TableName
exec(@.sql)
FETCH NEXT FROM product_cursor
END
CLOSE product_cursor
DEALLOCATE product_cursor
FETCH NEXT FROM comments_list INTO @.parentId,@.TableName,@.RelIDName
END
CLOSE comments_list
DEALLOCATE comments_list
GO

and fetch data from sql

if cant image refer this url

http://shriramgore.ifastnet.com/?q=node/41

thnx

|||

Hi,

Based on my understing, since you didn't do any row-based operations on the table(after you have fetched each row, seems you didn't perform any operations), there is no need for you to use cursors. If what you want is just to fetch data from multi tables, a simpleJoin will work in your case. Use dynamic query if you cannot determine your table name until run-time. Like:

declare @.str nvarchar(100) ,@.str1 varchar(100)set @.str='table_1'set @.str1='table_2'exec ('select * from '+@.str+' as a inner join '+ @.str1+' as b on a.id=b.id')

Hope my suggestion helps|||

thnx for replyBo Chen

but i have following like ......... please check example ...

I have data from multiple table, those table's Names are store as Variable in a table . in this Case i have written 1 store procedure using Cursor . this query Execute in server like (Please check this Url -http://shriramgore.ifastnet.com/?q=node/41&size=_original ) . after executing storeprocedure i get data in different tabels this data i can't fetch in single dataset .if i will fetch all tables(results of storeprocedure) then it eaiser to me for next part .

e.g
DataSet ds = new DataSet();
ds = proc.GetDataSet();

for (int i= 0; i< ds.Tables.Count; i++)
{
for (int y= 0; y< ds.Tables [i].Rows.Count; y++)
{
String str=ds.tabels[i].rows[y]["ColumnName"].toString();
}
}

in this example i enable to get other tabels (dsParent1.Tables[int] ).

please help

thnx in advance

|||

Hi shriram.gore,

I can see your problem. In my opinion, i think you can try to revise your stored procedure, like this: pass three or four parameters(as you need) into your stored procedure and make them as output paramters. In your stored procedure, you can assign them the table-name values during sql clause execution. For example:

create procedure sp_get_tbl_name (@.str1 varchar(100) output,
@.str2 varchar(100) output, @.str3 varchar(100) output...)
as
select @.str1=tablename
from yourtablename
where .....
go
select @.str2=tablename
from yoursecondtablename
where...
go
..

Hope my suggestion helps

|||

Hello ,

thnx for Reply

Yes uropinion is correct . That query iwould like to use in for loop to fetch data from multiple table . m i rt . in my case i can't use for loop in my page bcoz we have 200 record's from 5 table (Varible table - those declare in other table).if i had use for loop then those query fire 200 time . m i rt .

excuse i m not using sqlserver 2005

thnx

shriram

|||

Yes uropinion is correct . That query iwould like to use in for loop to fetch data from multiple table . m i rt . in my case i can't use for loop in my page bcoz we have 200 record's from 5 table (Varible table - those declare in other table).if i had use for loop then those query fire 200 time . m i rt .

If i understand you correct, that's not a problem.

Since now you have got those table names, you can user your sldataadpater to perform a sql data retrieval against each data table name--after that you will get server datasets, say, ds1, ds2,ds3,ds4..etc. Generally speaking each dataset you get contains only one table(it depends on your select command)

Then you can create a new dataset named ds, run : ds.tables.add(ds1.table[0]); ds.tables.add(ds2.table[0]),ds.tables.add(ds3.table[0])...etc..

Now your dataset ds contains all the tables you need. You can perform your loop on it.

BTW, what does "m i rt. " stand for??

thanks

|||

HelloBo Chen ,

thank u very much . my prob is solved .

thnx

shriram

DAO, Transactions, SQLServer

Hello,
I have a problem with DAO, Transactions and SQLServer. I want to do a very
simple thing (in VB)!
BeginTrans
Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
'mycounter'", dbSQLPassThrough)
Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
value = Rec(0)
CommitTrans
Note how simple this is. I just want to get a new value for a counter,
UPDATing first in order to make sure each value is returned only once, even
in concurrent environment. This is what you learn in school.
Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not in
SQLServer using DAO/ODBC.
In SQLServer using DAO/ODBC the code just hangs the entire application at
the SELECT line.
I believe the problem comes from the ODBC SQL Server driver
(2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
are opened (by the driver I believe) and I suspect that the driver sends the
UPDATE statement to connection 1 for instance and the SELECT query to ...
connection 2! Of course this will cause a deadlock.
I saw microsoft comments
http://support.microsoft.com/default...b;EN-US;170548 , but I am
not really using JET, since all my database calls use the dbSQLPassThrough
option. I also tried to disable the ODBC connection pool but this is
useless. I believe it really is the Drivers fault since I can get these
statements to work in Sybase and Oracle.
This is the kind of stuff that puzzles me the most. The whole microsoft
architecture tries to be smarter than you are and takes control of
everything, but fails to do the simplest things AND it really seems you
cannot disable it.
Any help would be very much appreciated.
SerGioGio
Hi
What error are you getting?
Have you considered using ADO insterad of DAO?
DAO is a very old technology so I am trying to remember how the stuff worked
10 years ago.
Since you are doing a read, use dbForwardOnly (I think that is what is it)
for the rs.
rs.BeginTran and rs.CommitTran are required.
Regards
Mike
"SerGioGio" wrote:

> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once, even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
>
|||Hello Mike,
Thanks for your quick answer.
I am only getting a "Timeout Expired" Error after 1 minute or so.
I tried your suggestion but with no luck
The whole app is written in DAO so I cannot switch to ADO unfortunately. In
addition I am not even sure ADO will fix that. We always need the most
advanced technology to support the most basic stuff. On the other hand,
client-side cursors, distributed transactions, access database links,
connection pool are available in DAO since early stages...
SerGioGio
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:D390368A-C890-4B3E-8C62-725D48FC5348@.microsoft.com...
> Hi
> What error are you getting?
> Have you considered using ADO insterad of DAO?
> DAO is a very old technology so I am trying to remember how the stuff
worked[vbcol=seagreen]
> 10 years ago.
> Since you are doing a read, use dbForwardOnly (I think that is what is it)
> for the rs.
> rs.BeginTran and rs.CommitTran are required.
> Regards
> Mike
> "SerGioGio" wrote:
very[vbcol=seagreen]
even[vbcol=seagreen]
not in[vbcol=seagreen]
at[vbcol=seagreen]
connections[vbcol=seagreen]
the[vbcol=seagreen]
...[vbcol=seagreen]
am[vbcol=seagreen]
dbSQLPassThrough[vbcol=seagreen]
|||Hi
Then you need to pass both statements at once (you may have to navigate
through the dataset collection to get to the 2nd executions output) or create
a stored procedure that has the update and select in it. Input parameter of
'mycounter' and output parameter of count.
Regards
Mike
"SerGioGio" wrote:

> Hello Mike,
> Thanks for your quick answer.
> I am only getting a "Timeout Expired" Error after 1 minute or so.
> I tried your suggestion but with no luck
> The whole app is written in DAO so I cannot switch to ADO unfortunately. In
> addition I am not even sure ADO will fix that. We always need the most
> advanced technology to support the most basic stuff. On the other hand,
> client-side cursors, distributed transactions, access database links,
> connection pool are available in DAO since early stages...
> SerGioGio
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a écrit dans le message de
> news:D390368A-C890-4B3E-8C62-725D48FC5348@.microsoft.com...
> worked
> very
> even
> not in
> at
> connections
> the
> ...
> am
> dbSQLPassThrough
>
>
|||Well it is a bit sad to rewrite all the sql just because of one dumb driver
(or whatever it is that fails here).
Using stored proc means writing one version for Oracle, one for Sybase, one
for SQL Server...
But I guess I will have to.
Sometimes I admire MS for their tool/concepts, sometimes I just find they
purposely push us to bloated solutions.
SerGioGio
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:76FCBB7F-C446-4938-8122-D5B536D06417@.microsoft.com...
> Hi
> Then you need to pass both statements at once (you may have to navigate
> through the dataset collection to get to the 2nd executions output) or
create
> a stored procedure that has the update and select in it. Input parameter
of[vbcol=seagreen]
> 'mycounter' and output parameter of count.
> Regards
> Mike
>
> "SerGioGio" wrote:
In[vbcol=seagreen]
it)[vbcol=seagreen]
a[vbcol=seagreen]
counter,[vbcol=seagreen]
once,[vbcol=seagreen]
but[vbcol=seagreen]
application[vbcol=seagreen]
sends[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
these[vbcol=seagreen]
microsoft[vbcol=seagreen]
you[vbcol=seagreen]
|||Hi SerGioGio,
Close all opened recordset before to begin a transaction.
Vctor Koch.
"SerGioGio" <sergiogio@.yahoo.fr> escribi en el mensaje
news:OyCDm4SpEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once,
even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not
in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends
the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
|||Hello,
OK I eventually found a solution to this issue, hopefully this may help some
people.
I now believe that I was wrong, the responsible for multiple connections is
not ODBC, it is Jet! I was under the assumption that since I was using
dbSQLPassThrough in all my queries, I was getting rid of Jet, but not
completely actually as it turned out.
To completely get rid of Jet one must use VB's ODBCDirect, it's just a
matter of adding the flag dbUseODBC in the Workspace object creation option.
With this option I no longer get outstanding connections, and I have a great
control over the connections.
Hope this will help people not to struggle for a whole week like I did.
SerGioGio
"SerGioGio" <sergiogio@.yahoo.fr> a crit dans le message de
news:OyCDm4SpEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once,
even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not
in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends
the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>

CXPACKET & LATCH_EX

Dear all,
For some unknown reason, I am keep getting these 2 wait type on my SQL
server and it is considerably slowing down the whole server.
Is there a way to to get rid of it or any reference resources to
troubleshoot with these 2 wait type? Can it be possibily got anything to do
with my Application it self or the SQL server?
Thanx in advanced
Leethis is not doc'd very well. Tom Davidson from MS has a great white paper on
MSDN. I'm not sure if it was published yet? Search MSDN for his name...
also... we write a summary of it for SQL Server Magazine.
CX_PACKET deals with parallel query synchronization while LATCH_EX deals
with a a type of internal latching mechanism. Tom's paper talks about them
nicely...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
news:OEtsdNvCEHA.1236@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> For some unknown reason, I am keep getting these 2 wait type on my SQL
> server and it is considerably slowing down the whole server.
> Is there a way to to get rid of it or any reference resources to
> troubleshoot with these 2 wait type? Can it be possibily got anything to
do
> with my Application it self or the SQL server?
> Thanx in advanced
> Lee
>
>|||Hi Brian,
Is it the below document?
http://sqldev.net/misc/WaitTypes.htm
Regards
Thirumal
>--Original Message--
>this is not doc'd very well. Tom Davidson from MS has a
great white paper on
>MSDN. I'm not sure if it was published yet? Search MSDN
for his name...
>also... we write a summary of it for SQL Server Magazine.
>CX_PACKET deals with parallel query synchronization while
LATCH_EX deals
>with a a type of internal latching mechanism. Tom's paper
talks about them
>nicely...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
>news:OEtsdNvCEHA.1236@.TK2MSFTNGP11.phx.gbl...
wait type on my SQL
server.
resources to
possibily got anything to
>do
>
>.
>|||"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
news:OEtsdNvCEHA.1236@.TK2MSFTNGP11.phx.gbl...
> For some unknown reason, I am keep getting these 2 wait type on my SQL
> server and it is considerably slowing down the whole server.
We had a problem with lots of LATCH_EX locks on our SQL server and the
problem was caused by badly written ASP code.
The ASP code was opening up multiple connections which one would have done.
The problem wasn't to do with the number of connections but the frequency
that it was opening and closing them.
Instead of opening one connection and using that throughout the ASP code, it
would open a connection, run one query and close it and that was then run
within a loop which compounded the issue.
Once the ASP code had been modified to only use one connection, all the
LATCH_EX locks dissapeared|||As Brian says CX_PACKET wait is used as a synchronization mechanism for PQ.
What seems to happen is that one EC (execution context) waits 'behind' this
wait, while 2 other EC's (on my 2 way server) are doing (or should do, see
below) the work.
By extensive tracing on my test server(with tools available on my website) I
found that LATCH_EX is waited for by one EC while the other EC is doing(and
waiting for I/O). This looks like a bug, since the whole idea of PQ is of
course to read and process in parallel..
Btw I told a widely respected MS Server engineer about this a week ago.
Instead of taking this seriously, he questioned my tools..
regards,
Mario
http://www.sqlinternals.com
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:uXnBeivCEHA.2256@.TK2MSFTNGP12.phx.gbl...
> this is not doc'd very well. Tom Davidson from MS has a great white paper
on
> MSDN. I'm not sure if it was published yet? Search MSDN for his name...
> also... we write a summary of it for SQL Server Magazine.
> CX_PACKET deals with parallel query synchronization while LATCH_EX deals
> with a a type of internal latching mechanism. Tom's paper talks about them
> nicely...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Learn Yee" <learnyee@.freightmark.com.my> wrote in message
> news:OEtsdNvCEHA.1236@.TK2MSFTNGP11.phx.gbl...
SQL
> do
>