define Hierarchy: 2012

Friday, March 30, 2012

How to disabel the Microsoft Search Service

Hi everybody,

I work with SQL Server 2000 SP2 in an Active/active Cluster.
Two days ago, one the sql server fulltext resource of one node just failed suddenly.
Now I am realizing that Search Engine Utility has not been used by any of my databases, so I would like do disable this service...
Does anybody know how can I get rid of the Microsoft Search Service.
I don't need this service active!
In Service Manager the Microsoft Search is in an unkown state.
In cluster Administrator the sql server full text resource is in a failed state.
Do I need to delete the full text resource or is there any other way to disable the Search Service?
Any help or advice will be very important to me...
Thanks,Search service is a shared service by all SQL Server instances.

Can you take off-line on SQL Server FullText resources from all SQL Server instances from cluster administrator?

You shouldn't remove the search service even though you currently do not use it. Try take it off-line.

Becuase your search service is in unknow status, I would move all cluster groups to the good node and restart the machine that search service is not function properly.|||Hi Allen_Cui

Thank you for your reply...
I have already tried to move all the groups to the good node and the result was:
Microsoft Search is in "stopped" state in Service Manager... -> that's ok!
SQL Server Full Text resource was in "failed" state in cluster Administrator... -> that's not ok!
But before I try the manual failover I selected the option "Do not restart" in the properties of the failed Full Text Resource...
My problem now is: I want to convert the state of the failed Full Text Resource to an offline state the is more pleasant to see...
Do you have any idea?
I will be waiting for your help...|||I would check the machine application/system logs to see any errors related FullText resource and search service.

How to direct T-SQL executoin output to a file

Is there a SQL Server 2000 equivalent of the Oracle SPOOL command like SPOOL 'C:\TEMP\MyFile.txt' which can be placed at the top of a series of SQL commands to direct the execution messages and results to the nominated file ? I want to use this in a Stored Procedure executed from within Access and capture all system and error messages at the server level.

hi,

open sql query analyzer

click on "query"

click on "results to file"

run a query.

or better yet

run BCPor the bulk copy program

regards,

joey

|||

This will work if xp_cmdshell is enabled

DECLARE @.isqlString varchar(255)

SELECT @.isqlString = 'isql -Q "SELECT top 10 FROM TABLE" -E -o C:\Results.txt'

EXEC master..xp_cmdshell @.isqlString

|||

Hi,

I found Steve's tip very helpful, however I also found out that you have to refer to the database name that the table belongs to even if the database you are refering to is the currently selected database within query analyser. This is because the command shell cannot see what the default database within query analyser is.

Please find the following example; -

declare @.str varchar(255)
select @.str = 'isql -Q"select * from spintlslive.dbo.T_Payments WHERE Paydate = ''07/13/2007''" -E -w255 -oD:\UPLOADS\payments.txt'
exec master..xp_cmdshell @.str

How to direct T-SQL executoin output to a file

hi,
open sql query analyzer
click on "query"
click on "results to file"
run a query.
or better yet
run BCPor the bulk copy program
regards,
joey

|||

This will work if xp_cmdshell is enabled

DECLARE @.isqlString varchar(255)

SELECT @.isqlString = 'isql -Q "SELECT top 10 FROM TABLE" -E -o C:\Results.txt'

EXEC master..xp_cmdshell @.isqlString

|||

Hi,

Please find the following example; -

declare @.str varchar(255)
select @.str = 'isql -Q"select * from spintlslive.dbo.T_Payments WHERE Paydate = ''07/13/2007''" -E -w255 -oD:\UPLOADS\payments.txt'
exec master..xp_cmdshell @.str

How to direct temp files for DTS packages

I have several DTS packages that import FoxPro data into SQL 2000. These
jobs create a temp file about the same size of the data that I am importing.
I call the DTS packages as a SQL job. If the job runs successfully then the
temp files are removed when it finishes, however if the job fails it leaves
the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
directory and I start to run low on disk space until I manually delete the
files.
Is there a way to direct where these files go? I would like to change this
to the D:\ where I have a lot more space.
Thanks
MikeNevermind. I found it. It is a windows configuration setting.
"Mike" <Mike@.Comcast.net> wrote in message
news:u7yCs%235MEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I have several DTS packages that import FoxPro data into SQL 2000. These
> jobs create a temp file about the same size of the data that I am
importing.
> I call the DTS packages as a SQL job. If the job runs successfully then
the
> temp files are removed when it finishes, however if the job fails it
leaves
> the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
> directory and I start to run low on disk space until I manually delete the
> files.
> Is there a way to direct where these files go? I would like to change this
> to the D:\ where I have a lot more space.
> Thanks
> Mike
>

How to direct temp files for DTS packages

I have several DTS packages that import FoxPro data into SQL 2000. These
jobs create a temp file about the same size of the data that I am importing.
I call the DTS packages as a SQL job. If the job runs successfully then the
temp files are removed when it finishes, however if the job fails it leaves
the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
directory and I start to run low on disk space until I manually delete the
files.
Is there a way to direct where these files go? I would like to change this
to the D:\ where I have a lot more space.
Thanks
Mike
Nevermind. I found it. It is a windows configuration setting.
"Mike" <Mike@.Comcast.net> wrote in message
news:u7yCs%235MEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I have several DTS packages that import FoxPro data into SQL 2000. These
> jobs create a temp file about the same size of the data that I am
importing.
> I call the DTS packages as a SQL job. If the job runs successfully then
the
> temp files are removed when it finishes, however if the job fails it
leaves
> the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
> directory and I start to run low on disk space until I manually delete the
> files.
> Is there a way to direct where these files go? I would like to change this
> to the D:\ where I have a lot more space.
> Thanks
> Mike
>

How to diplay a dataset in a table

Hi all..
I have a dataset named "Primas" that returns 3 records with 3 fields each.
I want to display a list of those records with a header and a summarize row.
To do that, I placed a Table control into the layout. Assigned "Primas" as
the dataset and I placed a textbox control into it with the value of
=Fields!CODIGOPRIMA.value (CODIGOPRIMA belongs to Primas dataset)
When I build the report, I get the error:
"Expression value of object CODIGOPRIMA reference field CODIGOPRIMA. Report
element expressions can only reference a field in actual dataset scope or, if
they are inside an aggregate, the specified dataset scope"
(I translated the messaege from Spanish, so I'm not sure if it is accurate,
but that's the idea).
The question.. why I get that message although I have the dataset specified
for that table? When I go to a field property inside the table, under
expressions, system shows me only the fields from a dataset that is the
parent of the table (a List)
Any help will be greately appreciated,
Thanks
JaimeYou might check that the Dataset is aware of the field you are trying to
use. You can do this by clicking on the Refresh button on the Data tab with
that dataset selected. I get that same message if I've added or changed a
field in the underlying database and forget to refresh the dataset.
Jared
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:8C285D68-701D-451F-A2A8-363B80874025@.microsoft.com...
> Hi all..
> I have a dataset named "Primas" that returns 3 records with 3 fields each.
> I want to display a list of those records with a header and a summarize
> row.
> To do that, I placed a Table control into the layout. Assigned "Primas" as
> the dataset and I placed a textbox control into it with the value of
> =Fields!CODIGOPRIMA.value (CODIGOPRIMA belongs to Primas dataset)
> When I build the report, I get the error:
> "Expression value of object CODIGOPRIMA reference field CODIGOPRIMA.
> Report
> element expressions can only reference a field in actual dataset scope or,
> if
> they are inside an aggregate, the specified dataset scope"
> (I translated the messaege from Spanish, so I'm not sure if it is
> accurate,
> but that's the idea).
> The question.. why I get that message although I have the dataset
> specified
> for that table? When I go to a field property inside the table, under
> expressions, system shows me only the fields from a dataset that is the
> parent of the table (a List)
> Any help will be greately appreciated,
> Thanks
> Jaime|||Hi Tom...
I have done so but the same problem happens. And when I go to the field
value combobox, only dataset fields associated with the List are shown, not
table daaset fields.
Jaime
"Tom Rocco" wrote:
> You might check that the Dataset is aware of the field you are trying to
> use. You can do this by clicking on the Refresh button on the Data tab with
> that dataset selected. I get that same message if I've added or changed a
> field in the underlying database and forget to refresh the dataset.
> Jared
> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
> news:8C285D68-701D-451F-A2A8-363B80874025@.microsoft.com...
> > Hi all..
> >
> > I have a dataset named "Primas" that returns 3 records with 3 fields each.
> >
> > I want to display a list of those records with a header and a summarize
> > row.
> > To do that, I placed a Table control into the layout. Assigned "Primas" as
> > the dataset and I placed a textbox control into it with the value of
> > =Fields!CODIGOPRIMA.value (CODIGOPRIMA belongs to Primas dataset)
> >
> > When I build the report, I get the error:
> > "Expression value of object CODIGOPRIMA reference field CODIGOPRIMA.
> > Report
> > element expressions can only reference a field in actual dataset scope or,
> > if
> > they are inside an aggregate, the specified dataset scope"
> > (I translated the messaege from Spanish, so I'm not sure if it is
> > accurate,
> > but that's the idea).
> >
> > The question.. why I get that message although I have the dataset
> > specified
> > for that table? When I go to a field property inside the table, under
> > expressions, system shows me only the fields from a dataset that is the
> > parent of the table (a List)
> >
> > Any help will be greately appreciated,
> > Thanks
> > Jaime
>
>

How to differentiate SQL 2000 and MSDE installs

Hello!
I am trying to determine how to differentiate a SQL 2000 and MSDE install
without running a query on the database.
In mmc's post and Andrea's answer on 1/18/05, a clear solution using a query
is noted.
I would like to be able to read a registy value to make the determination if
possible.
Thanks very much for the assistance.
Carl
Hi
Gave you installed SQL Server on one machine, MSDE on another one and looked
at the differences?
--
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/
"Carl Langren" <test@.test> wrote in message
news:eNlceBWBFHA.3588@.TK2MSFTNGP11.phx.gbl...
> Hello!
> I am trying to determine how to differentiate a SQL 2000 and MSDE install
> without running a query on the database.
> In mmc's post and Andrea's answer on 1/18/05, a clear solution using a
query
> is noted.
> I would like to be able to read a registy value to make the determination
if
> possible.
> Thanks very much for the assistance.
> Carl
>
|||Hi Mike,
Yes, three servers and six workstations. Mixed MSDE, Developer and Full SQL.
I was really looking more for an answer rather than a question......
I found this post:
http://www.masterado.net/home/ng/for...t14419113.aspx
=================================================
11/16/2004 4:27:03 PM SQL Server 2000 registry values by edition?
Is it possible to determine through the registry which edition of SQL Server
2000 has been installed - Enterprise, Standard, Personal, Developer,
Evalulation, Windows CE, Desktop (MSDE)?
I know it's possible using 'SELECT SERVERPROPERTY('edition')' in
QueryAnalyzer but what about the registry?
Thanks!
==================================================
11/28/2004 4:20:00 AM RE: SQL Server 2000 registry values by edition?
No, but you can use registry key below to differentiate between MSDE and
other sql editions:
For a named instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance
Name>\Setup
For a default instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Setup
If one of these keys exist but there is no ProductCode, the instance is one
of the SQL Server 2000 Editions (such as Enterprise, Enterprise Evaluation,
Standard, Developer, or Personal).
If a ProductCode entry is present, the instance is MSDE.
You can also parse the sql errorlog to determine what edition, see KB: INF:
How to Determine the Download Files You Need for Upgrading to SQL Server
2000 Service Pack 3 - http://support.microsoft.com/?id=814618
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||hi Carl,
Carl Langren wrote:
> Hello!
> I am trying to determine how to differentiate a SQL 2000 and MSDE
> install without running a query on the database.
> In mmc's post and Andrea's answer on 1/18/05, a clear solution using
> a query is noted.
> I would like to be able to read a registy value to make the
> determination if possible.
> Thanks very much for the assistance.
> Carl
AFAIK, apart from ProductCode key in the Setup hive you already mentioned,
unfortunately there are no registry difference among SQL Server editions,
and MSDE is one of them, and only directly querying the engine determins a
real answer to this question...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to differentiate process instances in perfmon

In a multi-instance cluster, how can you match a named sql server instance
with an instance of sqlservr.exe? When setting up counter collection in
perfmon, I choose the "process" performance object, then the "% processor
time" counter". In list of instances, I can see sqlservr#1, sqlservr#2, etc.
How do I know which named instance each of those represents?
TIA.
Hello
One way (I dont know if is the best way) is to match the ProcessID on
Process Counter from Performance Monitor to the ProcessID that is on the
ErrorLog from the Instance.
Something like:
PerfMon -> Process -> ProcessID -> Select SQLServer# on the instances.
Now go to SQL Server Enterprise Manager for each instance and look on the
ErrorLog for the line:
Server Process ID is 3636.
That should be easy if you need to do just on time.

Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.org
"Denise" wrote:

> In a multi-instance cluster, how can you match a named sql server instance
> with an instance of sqlservr.exe? When setting up counter collection in
> perfmon, I choose the "process" performance object, then the "% processor
> time" counter". In list of instances, I can see sqlservr#1, sqlservr#2, etc.
> How do I know which named instance each of those represents?
> TIA.
|||That's a good idea, thanks for the suggestion.
I don't have access to the error log on all the servers, but I can use
SELECT SERVERPROPERTY ('processid')
"Carlos Eduardo Selonke de Souza" wrote:
[vbcol=seagreen]
> Hello
> One way (I dont know if is the best way) is to match the ProcessID on
> Process Counter from Performance Monitor to the ProcessID that is on the
> ErrorLog from the Instance.
> Something like:
> PerfMon -> Process -> ProcessID -> Select SQLServer# on the instances.
> Now go to SQL Server Enterprise Manager for each instance and look on the
> ErrorLog for the line:
> Server Process ID is 3636.
> That should be easy if you need to do just on time.

> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
>
> "Denise" wrote:

How to differentiate between an SQL 2000 installation and an MSDE

Hi
Could any one tell me how to diffentiate between a sql instance and an msde
instance
thanks
mmc
hi,
mmc wrote:
> Hi
> Could any one tell me how to diffentiate between a sql instance and
> an msde instance
> thanks
> mmc
SET NOCOUNT ON
SELECT SERVERPROPERTY ( 'Edition' )
http://msdn.microsoft.com/library/de...a-ses_3mi1.asp
it returns "Desktop Engine" for MSDE
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

how to Diff vs Sum in Group By query?

Hello,
if area 'A' contains 2 numbers in 2 rows then
Select area, Sum(number) from tbl1 where area = 'A'
Group By area
gives me the sum of these 2 numbers in area 'A'
But how can I retrieve the difference of these 2 numbers
using T-Sql?
Thanks,
RonRon wrote:
> Hello,
> if area 'A' contains 2 numbers in 2 rows then
> Select area, Sum(number) from tbl1 where area = 'A'
> Group By area
> gives me the sum of these 2 numbers in area 'A'
> But how can I retrieve the difference of these 2 numbers
> using T-Sql?
> Thanks,
> Ron
Max(number) - Min(number)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks. That is pretty

. I forgot to include a
twist in here. I also have a datefld. So num1 may be min
or num may be max. I have to show +num or -num.
select area, (First(num) - Last(num)) as num1 from tbl1
where datefld between '1/1/2005' and 1/2/2005' Group By
area having area = 'A'
I was able to use your trick to get my positive or
negative result using First and Last functions. Any
suggestions appreciated if this is incorrect usage.
Thanks again,
Ron

>--Original Message--
>Ron wrote:
>Max(number) - Min(number)
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed
in my From
>header is my spam trap, so I don't check it very often.
You will get a
>quicker response by posting to the newsgroup.
>
>.
>|||Ron wrote:
> Thanks. That is pretty

. I forgot to include a
>twist in here. I also have a datefld. So num1 may be min
>or num may be max. I have to show +num or -num.
>select area, (First(num) - Last(num)) as num1 from tbl1
>where datefld between '1/1/2005' and 1/2/2005' Group By
>area having area = 'A'
>I was able to use your trick to get my positive or
>negative result using First and Last functions. Any
>suggestions appreciated if this is incorrect usage.
Hi Ron,
Try if this helps:
SELECT G.Area, F.num - L.num AS num1
FROM (SELECT area, MIN(datefld) AS FDate, MAX(datefld) AS LDate
FROM tbl1
WHERE datefld BETWEEN '20050101' AND '20050201'
GROUP BY area) AS G
INNER JOIN tbl1 AS F
ON F.area = G.area
AND F.datefld = G.FDate
INNER JOIN tbl1 AS L
ON L.area = G.area
AND L.datefld = G.LDate
WHERE G.area = 'A'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||SELECT A.area, A.num-B.num
FROM Tbl1 AS A
JOIN Tbl1 AS B
ON A.datefld < B.datefld
AND A.area = 'A'
AND B.area = 'A'
David Portas
SQL Server MVP
--|||Yes, I figured that out. I was writing the sql in Access
and transferring in to Query Analyzer. Sorry bout that.
I did end up creating a udf for first and last.

>--Original Message--
>Ron wrote:
min
>First? Last? You must be using Access ... This is a SQL
Server group
>Do you need a SQL Server (Transact-SQL) solution? Those
fnctions do not
>exist in T-SQL.
>
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed
in my From
>header is my spam trap, so I don't check it very often.
You will get a
>quicker response by posting to the newsgroup.
>
>.
>|||Thanks. I will give that a try.

>--Original Message--
>On Fri, 25 Feb 2005 11:19:41 -0800, Ron wrote:
>
min
>Hi Ron,
>Try if this helps:
>SELECT G.Area, F.num - L.num AS num1
>FROM (SELECT area, MIN(datefld) AS FDate, MAX
(datefld) AS LDate
> FROM tbl1
> WHERE datefld BETWEEN '20050101'
AND '20050201'
> GROUP BY area) AS G
>INNER JOIN tbl1 AS F
> ON F.area = G.area
> AND F.datefld = G.FDate
>INNER JOIN tbl1 AS L
> ON L.area = G.area
> AND L.datefld = G.LDate
>WHERE G.area = 'A'
>
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||Thanks very much for your reply. I will give this a try.

>--Original Message--
>SELECT A.area, A.num-B.num
> FROM Tbl1 AS A
> JOIN Tbl1 AS B
> ON A.datefld < B.datefld
> AND A.area = 'A'
> AND B.area = 'A'
>--
>David Portas
>SQL Server MVP
>--
>.
>

how to diff the data of same table of two sql servers

as subject.
any freeware or done at sql level?
thanks!What?
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||say, i have a table Order at server A and B,
i want to diff the sql data at two servers.
"ChrisR" <chris@.noemail.com> wrote in message
news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>|||It's not free but it's real cheap. www.red-gate.com has a product called
data compare that will do what you ask.
Andrew J. Kelly SQL MVP
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:%23tl6F6FzEHA.1596@.TK2MSFTNGP10.phx.gbl...
> say, i have a table Order at server A and B,
> i want to diff the sql data at two servers.
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
>|||I'm with Andrew, I vote Red Gate
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
What about using a FULL OUTER JOIN and then pulling the values with NULL.
Those would be the difference records. The matches would be the matching
records.
Rick Sawtell
MCT, MCSD, MCDBA|||Have a look at www.dbghost.com - why bother with a product that doesn't
always work?
DB Ghost? provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghost? will bu
ild
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, fo
r
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"ChrisR" wrote:

> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>
>

how to diff the data of same table of two sql servers

as subject.
any freeware or done at sql level?
thanks!What?
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||say, i have a table Order at server A and B,
i want to diff the sql data at two servers.
"ChrisR" <chris@.noemail.com> wrote in message
news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > as subject.
> >
> > any freeware or done at sql level?
> >
> > thanks!
> >
> >
>|||It's not free but it's real cheap. www.red-gate.com has a product called
data compare that will do what you ask.
--
Andrew J. Kelly SQL MVP
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:%23tl6F6FzEHA.1596@.TK2MSFTNGP10.phx.gbl...
> say, i have a table Order at server A and B,
> i want to diff the sql data at two servers.
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
>> What?
>>
>> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
>> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>> > as subject.
>> >
>> > any freeware or done at sql level?
>> >
>> > thanks!
>> >
>> >
>>
>|||I'm with Andrew, I vote Red Gate
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
What about using a FULL OUTER JOIN and then pulling the values with NULL.
Those would be the difference records. The matches would be the matching
records.
Rick Sawtell
MCT, MCSD, MCDBA|||Have a look at www.dbghost.com - why bother with a product that doesn't
always work?
DB Ghostâ?¢ provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghostâ?¢ will build
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, for
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"ChrisR" wrote:
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > as subject.
> >
> > any freeware or done at sql level?
> >
> > thanks!
> >
> >
>
>

how to diff the data of same table of two sql servers

as subject.
any freeware or done at sql level?
thanks!
What?
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
|||say, i have a table Order at server A and B,
i want to diff the sql data at two servers.
"ChrisR" <chris@.noemail.com> wrote in message
news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>
|||It's not free but it's real cheap. www.red-gate.com has a product called
data compare that will do what you ask.
Andrew J. Kelly SQL MVP
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:%23tl6F6FzEHA.1596@.TK2MSFTNGP10.phx.gbl...
> say, i have a table Order at server A and B,
> i want to diff the sql data at two servers.
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
>
|||I'm with Andrew, I vote Red Gate
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
|||"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
What about using a FULL OUTER JOIN and then pulling the values with NULL.
Those would be the difference records. The matches would be the matching
records.
Rick Sawtell
MCT, MCSD, MCDBA
|||Have a look at www.dbghost.com - why bother with a product that doesn't
always work?
DB Ghost? provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghost? will build
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, for
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"ChrisR" wrote:

> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>
>

how to diaplay date in MM/dd/yyyy format

how to display date in mm/dd/yy using select query ( i have dates which are in mm/dd/yyyyy format but when i run a query it displaying yyyy-mm-dd format).i want to display date in mm/dd/yyyy format so how to write select query for that

This 'should' work:

SELECT convert( varchar(10), MyColumn, 101 )

For example, using today's date:

SELECT convert( varchar(10), getdate(), 101 )

-
05/25/2007

|||what is this 101 , 102 in code|||

It the style number which indicates how the system should transform your data.

Here is more detail.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

|||thanks a lot MVPsql

how to diagnostic performance issue?

Hi,
I have a server with performance issue and I want to know why or know how to
identify the cause.
its for a datawarehouse SQL database.
I'm using SQL 2005.
during loading process or during query against the largest table (40
millions of rows), the server provide a bad response time.
when I load this table the performance could be 150 000rows/sec or 10
000rows/sec!
so I try to find why sometimes the performance is average, and sometimes its
really bad. (for the same process & queries)
I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA at
2Gbps
if I track the performance counters...
Sometimes I see a high pages/sec value (4000-6000)
sometimes its the Avg disk queue length value
the CPU is 25% of usage
sometimes the server become unresponsive or is responseive but no rows are
loaded!!!
doing the same process of a smaller table and on a smaller dev server
provide a far better throughput (200 000 rows/sec; near no stops in the
process; perf. counter are constant values etc...)
when a execute a query which is a simple group by (without joins) on the big
table, the server takes 3 to 4 minutes to answer.
using joins the same group by query takes 10 minutes and there is a lot of
CXPacket waitstate.
loading these 40 millions of rows can takes 20 minutes or 1hour...
If I take a look at the memory usage, the server starts to use memory (up to
8Gb) and sudenly release the memory (go down to 200Mb of physical memory,
but virtual memory is 9Gb)
what can I do to diagnose the problem?
Thanks
Jerome.Jeje wrote:
> Hi,
> I have a server with performance issue and I want to know why or know how to
> identify the cause.
> its for a datawarehouse SQL database.
> I'm using SQL 2005.
> during loading process or during query against the largest table (40
> millions of rows), the server provide a bad response time.
> when I load this table the performance could be 150 000rows/sec or 10
> 000rows/sec!
> so I try to find why sometimes the performance is average, and sometimes its
> really bad. (for the same process & queries)
> I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA at
> 2Gbps
> if I track the performance counters...
> Sometimes I see a high pages/sec value (4000-6000)
> sometimes its the Avg disk queue length value
> the CPU is 25% of usage
> sometimes the server become unresponsive or is responseive but no rows are
> loaded!!!
> doing the same process of a smaller table and on a smaller dev server
> provide a far better throughput (200 000 rows/sec; near no stops in the
> process; perf. counter are constant values etc...)
> when a execute a query which is a simple group by (without joins) on the big
> table, the server takes 3 to 4 minutes to answer.
> using joins the same group by query takes 10 minutes and there is a lot of
> CXPacket waitstate.
> loading these 40 millions of rows can takes 20 minutes or 1hour...
> If I take a look at the memory usage, the server starts to use memory (up to
> 8Gb) and sudenly release the memory (go down to 200Mb of physical memory,
> but virtual memory is 9Gb)
> what can I do to diagnose the problem?
> Thanks
> Jerome.
>
Do you have the proper indexes in place to support the queries you're
running? You might also look into using MAXDOP, the wait type of
CXPACKET indicates parallel processes that are waiting on siblings to
catch up.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||the maxdop option reduce the cxpacket size numbers, but the query is a
little slower.
I have 1 index which cover all the columns used in the joins + 1 index on
each linked table.
all the joins except 1 are simple = on 1 column only.
there is no clustered indexes on these columns.
and I can't create or use them.
also... I see a high % disk time (up to 800%)
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:eh$Y2aesGHA.3556@.TK2MSFTNGP03.phx.gbl...
> Jeje wrote:
>> Hi,
>> I have a server with performance issue and I want to know why or know how
>> to identify the cause.
>> its for a datawarehouse SQL database.
>> I'm using SQL 2005.
>> during loading process or during query against the largest table (40
>> millions of rows), the server provide a bad response time.
>> when I load this table the performance could be 150 000rows/sec or 10
>> 000rows/sec!
>> so I try to find why sometimes the performance is average, and sometimes
>> its really bad. (for the same process & queries)
>> I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA
>> at 2Gbps
>> if I track the performance counters...
>> Sometimes I see a high pages/sec value (4000-6000)
>> sometimes its the Avg disk queue length value
>> the CPU is 25% of usage
>> sometimes the server become unresponsive or is responseive but no rows
>> are loaded!!!
>> doing the same process of a smaller table and on a smaller dev server
>> provide a far better throughput (200 000 rows/sec; near no stops in the
>> process; perf. counter are constant values etc...)
>> when a execute a query which is a simple group by (without joins) on the
>> big table, the server takes 3 to 4 minutes to answer.
>> using joins the same group by query takes 10 minutes and there is a lot
>> of CXPacket waitstate.
>> loading these 40 millions of rows can takes 20 minutes or 1hour...
>> If I take a look at the memory usage, the server starts to use memory (up
>> to 8Gb) and sudenly release the memory (go down to 200Mb of physical
>> memory, but virtual memory is 9Gb)
>> what can I do to diagnose the problem?
>> Thanks
>> Jerome.
>>
> Do you have the proper indexes in place to support the queries you're
> running? You might also look into using MAXDOP, the wait type of CXPACKET
> indicates parallel processes that are waiting on siblings to catch up.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hi
Have you done AWE /PAE to configure the memory?
http://support.microsoft.com/kb/274750/
"Jeje" <willgart@.hotmail.com> wrote in message
news:%23rk%238mdsGHA.1888@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a server with performance issue and I want to know why or know how
> to identify the cause.
> its for a datawarehouse SQL database.
> I'm using SQL 2005.
> during loading process or during query against the largest table (40
> millions of rows), the server provide a bad response time.
> when I load this table the performance could be 150 000rows/sec or 10
> 000rows/sec!
> so I try to find why sometimes the performance is average, and sometimes
> its really bad. (for the same process & queries)
> I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA
> at 2Gbps
> if I track the performance counters...
> Sometimes I see a high pages/sec value (4000-6000)
> sometimes its the Avg disk queue length value
> the CPU is 25% of usage
> sometimes the server become unresponsive or is responseive but no rows are
> loaded!!!
> doing the same process of a smaller table and on a smaller dev server
> provide a far better throughput (200 000 rows/sec; near no stops in the
> process; perf. counter are constant values etc...)
> when a execute a query which is a simple group by (without joins) on the
> big table, the server takes 3 to 4 minutes to answer.
> using joins the same group by query takes 10 minutes and there is a lot of
> CXPacket waitstate.
> loading these 40 millions of rows can takes 20 minutes or 1hour...
> If I take a look at the memory usage, the server starts to use memory (up
> to 8Gb) and sudenly release the memory (go down to 200Mb of physical
> memory, but virtual memory is 9Gb)
> what can I do to diagnose the problem?
> Thanks
> Jerome.
>|||no, not required
its x64bits server (win 2003 ent x64 and SQL Server 2005x64 enterprise)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23bygttfsGHA.148@.TK2MSFTNGP05.phx.gbl...
> Hi
> Have you done AWE /PAE to configure the memory?
> http://support.microsoft.com/kb/274750/
>
>
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:%23rk%238mdsGHA.1888@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a server with performance issue and I want to know why or know how
>> to identify the cause.
>> its for a datawarehouse SQL database.
>> I'm using SQL 2005.
>> during loading process or during query against the largest table (40
>> millions of rows), the server provide a bad response time.
>> when I load this table the performance could be 150 000rows/sec or 10
>> 000rows/sec!
>> so I try to find why sometimes the performance is average, and sometimes
>> its really bad. (for the same process & queries)
>> I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA
>> at 2Gbps
>> if I track the performance counters...
>> Sometimes I see a high pages/sec value (4000-6000)
>> sometimes its the Avg disk queue length value
>> the CPU is 25% of usage
>> sometimes the server become unresponsive or is responseive but no rows
>> are loaded!!!
>> doing the same process of a smaller table and on a smaller dev server
>> provide a far better throughput (200 000 rows/sec; near no stops in the
>> process; perf. counter are constant values etc...)
>> when a execute a query which is a simple group by (without joins) on the
>> big table, the server takes 3 to 4 minutes to answer.
>> using joins the same group by query takes 10 minutes and there is a lot
>> of CXPacket waitstate.
>> loading these 40 millions of rows can takes 20 minutes or 1hour...
>> If I take a look at the memory usage, the server starts to use memory (up
>> to 8Gb) and sudenly release the memory (go down to 200Mb of physical
>> memory, but virtual memory is 9Gb)
>> what can I do to diagnose the problem?
>> Thanks
>> Jerome.
>>
>

how to diagnostic performance issue?

How to diagnose memory bloating?

Good morning/afternoon,
We have two instances of sql-server 2k running. They start out fine
running about 50-100 meg of storage, then gradually increase to over 750mg
each, then one of the other stops responding and needs a restart.
Though I'm an old hand at database systems, I'm new to SQL-server and would
like to find a way to resolve this. I've run a couple of traces, but not
know what parameters to set, haven't really found a problem. (Though I did
add a couple of indexes which sped the system up.)
Where do we go from here?eg1,
By storage, do you mean memoy as in your subject line? Check the SQL Server
logand Windows event log to see if there are any messages that might offer a
hint. Check to see if your paging file is filling up (use Performance
Monitor or Task Manager).
The two instances might be fighting each other for memory resources. If all
else fails, try limiting the memory for each instance to 700 MB (or maybe a
little less). SQL Server is an aggressive user of system memory.
-- Bill
"eg1" <Bor@.kurzikstan.com> wrote in message
news:Xns98BA9DAEAED0BBorkurzikstancom@.20
7.46.248.16...
> Good morning/afternoon,
> We have two instances of sql-server 2k running. They start out fine
> running about 50-100 meg of storage, then gradually increase to over 750mg
> each, then one of the other stops responding and needs a restart.
> Though I'm an old hand at database systems, I'm new to SQL-server and
> would
> like to find a way to resolve this. I've run a couple of traces, but not
> know what parameters to set, haven't really found a problem. (Though I
> did
> add a couple of indexes which sped the system up.)
> Where do we go from here?

How to diagnose memory bloating?

Good morning/afternoon,
We have two instances of sql-server 2k running. They start out fine
running about 50-100 meg of storage, then gradually increase to over 750mg
each, then one of the other stops responding and needs a restart.
Though I'm an old hand at database systems, I'm new to SQL-server and would
like to find a way to resolve this. I've run a couple of traces, but not
know what parameters to set, haven't really found a problem. (Though I did
add a couple of indexes which sped the system up.)
Where do we go from here?eg1,
By storage, do you mean memoy as in your subject line? Check the SQL Server
logand Windows event log to see if there are any messages that might offer a
hint. Check to see if your paging file is filling up (use Performance
Monitor or Task Manager).
The two instances might be fighting each other for memory resources. If all
else fails, try limiting the memory for each instance to 700 MB (or maybe a
little less). SQL Server is an aggressive user of system memory.
-- Bill
"eg1" <Bor@.kurzikstan.com> wrote in message
news:Xns98BA9DAEAED0BBorkurzikstancom@.207.46.248.16...
> Good morning/afternoon,
> We have two instances of sql-server 2k running. They start out fine
> running about 50-100 meg of storage, then gradually increase to over 750mg
> each, then one of the other stops responding and needs a restart.
> Though I'm an old hand at database systems, I'm new to SQL-server and
> would
> like to find a way to resolve this. I've run a couple of traces, but not
> know what parameters to set, haven't really found a problem. (Though I
> did
> add a couple of indexes which sped the system up.)
> Where do we go from here?

How to diagnose memory bloating?

Good morning/afternoon,
We have two instances of sql-server 2k running. They start out fine
running about 50-100 meg of storage, then gradually increase to over 750mg
each, then one of the other stops responding and needs a restart.
Though I'm an old hand at database systems, I'm new to SQL-server and would
like to find a way to resolve this. I've run a couple of traces, but not
know what parameters to set, haven't really found a problem. (Though I did
add a couple of indexes which sped the system up.)
Where do we go from here?
eg1,
By storage, do you mean memoy as in your subject line? Check the SQL Server
logand Windows event log to see if there are any messages that might offer a
hint. Check to see if your paging file is filling up (use Performance
Monitor or Task Manager).
The two instances might be fighting each other for memory resources. If all
else fails, try limiting the memory for each instance to 700 MB (or maybe a
little less). SQL Server is an aggressive user of system memory.
-- Bill
"eg1" <Bor@.kurzikstan.com> wrote in message
news:Xns98BA9DAEAED0BBorkurzikstancom@.207.46.248.1 6...
> Good morning/afternoon,
> We have two instances of sql-server 2k running. They start out fine
> running about 50-100 meg of storage, then gradually increase to over 750mg
> each, then one of the other stops responding and needs a restart.
> Though I'm an old hand at database systems, I'm new to SQL-server and
> would
> like to find a way to resolve this. I've run a couple of traces, but not
> know what parameters to set, haven't really found a problem. (Though I
> did
> add a couple of indexes which sped the system up.)
> Where do we go from here?
sql

How to diagnose IO Problems

Recently our production database began experiencing very poor I/O
performance. How do I begin to narrow down the queries or tables that are
affecting this?It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:
> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?

How to diagnose IO Problems

Recently our production database began experiencing very poor I/O
performance. How do I begin to narrow down the queries or tables that are
affecting this?It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:

> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?

How to diagnose IO Problems

Recently our production database began experiencing very poor I/O
performance. How do I begin to narrow down the queries or tables that are
affecting this?
It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:

> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?

How to Diagnose IO Performance On A SAN

I'm runing SQL2000 with SP4 on Win 2003 Ent Srvr and use a SAN for the
database data and log files.
The SAN drives are configured as RAID5.
My question is when diagnosing IO performance issues, what perfmon counters
are recommended for use when the databases are on a SAN? I used to rely on
current disk queue length but have been told that counter is skewed when the
storage is SAN. Also if other counters are recommended, what would be the
thresholds for judging poor performance? Any utilites recommended?
thksTom,
See if this helps.
http://blogs.msdn.com/sqlcat/archive/2005/11/17/493944.aspx
AMB
"Tom Frost" wrote:
> I'm runing SQL2000 with SP4 on Win 2003 Ent Srvr and use a SAN for the
> database data and log files.
> The SAN drives are configured as RAID5.
> My question is when diagnosing IO performance issues, what perfmon counters
> are recommended for use when the databases are on a SAN? I used to rely on
> current disk queue length but have been told that counter is skewed when the
> storage is SAN. Also if other counters are recommended, what would be the
> thresholds for judging poor performance? Any utilites recommended?
> thks|||Hi Tom
"Tom Frost" wrote:
> I'm runing SQL2000 with SP4 on Win 2003 Ent Srvr and use a SAN for the
> database data and log files.
> The SAN drives are configured as RAID5.
> My question is when diagnosing IO performance issues, what perfmon counters
> are recommended for use when the databases are on a SAN? I used to rely on
> current disk queue length but have been told that counter is skewed when the
> storage is SAN. Also if other counters are recommended, what would be the
> thresholds for judging poor performance? Any utilites recommended?
> thks
Check out http://support.microsoft.com/kb/224587/
http://www.sql-server-performance.com/ew_san.asp
http://www.sql-server-performance.com/qdpma/inst_3_pmlogs.as
http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
You SAN vendor should also have tools which you can monitor the performance,
this will be necessary to rule out interface issues i.e. the SAN is fine, but
the OS thinks it's slow!
John|||Hi,
I understand that you would like to know what performance counters should
be used to troubleshoot your SQL Server 2000 performance issue.
If I have misunderstood, please let me know.
I recommend that you refer to the following articles for monitoring your
SQL Server Performance:
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/kb/838622
Microsoft SQL Server 2000 RDBMS Performance Tuning Guide for Data
Warehousing
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
SQL Server 2000 I/O Basics (SQLIOStress.exe for stress test)
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.m
spx
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi,
Just check with you to see if the suggestions were helpful. Please let us
know if you would like further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

How to diagnose a deadly embrace

I have an application which is running on 40 active workstations. I am
encountering lock outs many times a day. When I run the stored procedure
"sp_who2" I can see the spids that are blocked and by which other spid.
If I "kill xx" where xx is the head of the blocking chain the system frees
up. When doing the "sp_who2" I see a command column which only shows the
start of a command like "insert", 'update", "select" or "AWAITING COMMAND".
Is there a way to see the whole command to isolate which table(s) are
causing the problem?
Are there other tools to help me out?
Desparately in need of help...
Mark
Mark Butler wrote:
> I have an application which is running on 40 active workstations. I
> am encountering lock outs many times a day. When I run the stored
> procedure "sp_who2" I can see the spids that are blocked and by which
> other spid.
> If I "kill xx" where xx is the head of the blocking chain the system
> frees up. When doing the "sp_who2" I see a command column which only
> shows the start of a command like "insert", 'update", "select" or
> "AWAITING COMMAND". Is there a way to see the whole command to
> isolate which table(s) are causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
You can use Profiler to see what command are starting and not completing
or committing as the case may be. If you look at the starting and
completed events, you can see if something did not complete (missing
completed event). That might mean that data was not rolled back. It
sounds like you might have open transactions. Make sure no one is using
SQL Enterprise Manager (or other tools that do not fetch all data at
once) to edit data in tables as they will leave open locks on data.
If the same user is responsibl, you can filter the Profiler data. The
output may generate a lot of information. I would start looking at
SQL:BatchStarting/Completed and RPC:Starting/Completed. If yo uneed more
detail, you can add SQL:StmtStarting/Completed and
SP:StmtStarting/Completed.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||You could also use DBCC INPUTBUFFER or fn_get_sql on specific spids to find
out what they are doing at a given point in time. See SQL Server Books
Online for more information. You may find my code useful in this scenario:
http://vyaskn.tripod.com/fn_get_sql.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:eYpyqXLfFHA.1284@.TK2MSFTNGP14.phx.gbl...
> I have an application which is running on 40 active workstations. I am
> encountering lock outs many times a day. When I run the stored procedure
> "sp_who2" I can see the spids that are blocked and by which other spid.
> If I "kill xx" where xx is the head of the blocking chain the system frees
> up. When doing the "sp_who2" I see a command column which only shows the
> start of a command like "insert", 'update", "select" or "AWAITING
COMMAND".
> Is there a way to see the whole command to isolate which table(s) are
> causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
>
|||You can start SQL Server Profiler to trace which table is causing the lock.
John King
http://www.agileinfollc.com
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:eYpyqXLfFHA.1284@.TK2MSFTNGP14.phx.gbl...
>I have an application which is running on 40 active workstations. I am
>encountering lock outs many times a day. When I run the stored procedure
>"sp_who2" I can see the spids that are blocked and by which other spid.
> If I "kill xx" where xx is the head of the blocking chain the system frees
> up. When doing the "sp_who2" I see a command column which only shows the
> start of a command like "insert", 'update", "select" or "AWAITING
> COMMAND". Is there a way to see the whole command to isolate which
> table(s) are causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
>

How to develope a notification service application

hi all,

i am a novice in notification service.i am trying to develope an application on notification service but i am not able to do a sample application.i am not able to understand what is instance and rest of the things in notification service.

can anybody provide any guideline or links so that i am able to understand what notification service is and how to create a sample application.

please help

thanks a lot in advance.

Hi -

I typically recommend that people start with the walkthrough included in BOL; it'll give you a pretty good overview of the technology, terminology, etc. Many people, after going through the example, are left with more questions that when they began. That's where some of the other sample instances can help out to some extent.

At the risk of sounding like I'm doing little more than promoting my own book, I will mention that I do have a book out there that's designed to get people up to speed with SSNS in a very short amount of time. It's less than 200 pages and I've heard good things about it in the community.

Shyam Pather, also has a book out there on the subject. At 600+ pages, it's definitely a great resource to have and it covers much more than my book - custom content formatters, etc.

I also blog about the topic here - http://www.sqlns.com.

And of course you can content to visit this forum and we'll be glad to provide whatever help we can.

HTH...

Joe|||

Indeed, Joe Webb is right:

his and S.Pather's books are the best 2 books in the market now.

There are quite a few others which either have a chapter on NS or are completely dedicated to NS, but those 2 are way better.

Just get either one of those 2 books (or, better yet, both) and follow the samples.

It won't take you too long to get your own prototype up and running, and then you will change and extend it the way you need.

That's how I did my NS app.

Undoubtedly, MSDN is the best source of technical info when you need to quickly find an answer to a particular question...

...but to get you up and running, you need a good book.

How to develope a notification service application

hi all,

can anybody provide any guideline or links so that i am able to understand what notification service is and how to create a sample application.

please help

thanks a lot in advance.

Hi -

Shyam Pather, also has a book out there on the subject. At 600+ pages, it's definitely a great resource to have and it covers much more than my book - custom content formatters, etc.

I also blog about the topic here - http://www.sqlns.com.

And of course you can content to visit this forum and we'll be glad to provide whatever help we can.

HTH...

Joe|||

Indeed, Joe Webb is right:

his and S.Pather's books are the best 2 books in the market now.

There are quite a few others which either have a chapter on NS or are completely dedicated to NS, but those 2 are way better.

Just get either one of those 2 books (or, better yet, both) and follow the samples.

It won't take you too long to get your own prototype up and running, and then you will change and extend it the way you need.

That's how I did my NS app.

Undoubtedly, MSDN is the best source of technical info when you need to quickly find an answer to a particular question...

...but to get you up and running, you need a good book.

How to Develop Today Screen Plug in For WM5 Pocket PC

Hi, anybody help me how to develop Today Screen plug in for WM5 pocket pc.

Maybe this can get you started: http://msdn2.microsoft.com/en-us/windowsmobile/bb264330.aspxsql

How to develop database locally and post to web host?

I have MSSQL 2005 Express installed locally. I've developed a database and
would like to copy it to my web host to be accessed using some ASP code.
Everything appears to be OK, except for one issue that I can't figure out.
HOW do I take my local database and upload it to my host?
I do have the MS SQL Server 2005 Express Manager installed and can connect
to both my local SQL 2005 server and my hosts SQL 2003 server.
How are databases normally developed for web applications?Hi,
"SQL 2003 server." There is no SQL 2003 Server. If your hoster doesn=B4t
offer you to restore backups you ade on your test system. You can
either use scripts changing your database which can be applied on the
server, or you can transfer objects to your hosters db. I am alqys
doing a whole *backup* (with transfering the database objects to my
local machine) doing changes and reapply them on the *productional*
server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||You can backup local database and restore on the host database.
If you developed database file using Visual Studio, you can attach database
on the host.
And can modifiy connection string.
"Noozer"?? ??? ??:

> I have MSSQL 2005 Express installed locally. I've developed a database and
> would like to copy it to my web host to be accessed using some ASP code.
> Everything appears to be OK, except for one issue that I can't figure out.
> HOW do I take my local database and upload it to my host?
> I do have the MS SQL Server 2005 Express Manager installed and can connect
> to both my local SQL 2005 server and my hosts SQL 2003 server.
> How are databases normally developed for web applications?
>
>

How to develop database locally and post to web host?

I have MSSQL 2005 Express installed locally. I've developed a database and
would like to copy it to my web host to be accessed using some ASP code.
Everything appears to be OK, except for one issue that I can't figure out.
HOW do I take my local database and upload it to my host?
I do have the MS SQL Server 2005 Express Manager installed and can connect
to both my local SQL 2005 server and my hosts SQL 2003 server.
How are databases normally developed for web applications?
Hi,
"SQL 2003 server." There is no SQL 2003 Server. If your hoster doesn=B4t
offer you to restore backups you ade on your test system. You can
either use scripts changing your database which can be applied on the
server, or you can transfer objects to your hosters db. I am alqys
doing a whole *backup* (with transfering the database objects to my
local machine) doing changes and reapply them on the *productional*
server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||You can backup local database and restore on the host database.
If you developed database file using Visual Studio, you can attach database
on the host.
And can modifiy connection string.
"Noozer"?? ??? ??:

> I have MSSQL 2005 Express installed locally. I've developed a database and
> would like to copy it to my web host to be accessed using some ASP code.
> Everything appears to be OK, except for one issue that I can't figure out.
> HOW do I take my local database and upload it to my host?
> I do have the MS SQL Server 2005 Express Manager installed and can connect
> to both my local SQL 2005 server and my hosts SQL 2003 server.
> How are databases normally developed for web applications?
>
>

How to develop database locally and post to web host?

Hongju - can you be a little more specific on how to back up and restore the
databases. I have a very similar problem. I created a project that has a
database in APP_DATA. I'm hosting on GoDaddy and I need to take the APP_DAT
A
database (.mdf) and move it to one of their MySQL databases. I have no clue
on how to do that. Any help would be greatly appreciated.
Thanks
"hongju" wrote:
> You can backup local database and restore on the host database.
> If you developed database file using Visual Studio, you can attach databas
e
> on the host.
> And can modifiy connection string.
> "Noozer"?? ??? ??:
>Moving it to a MySQL database might take a little more work. Use the
"Generate scripts..." function in Management Studio and run those script
in the mysql database.
You will most likely need to modify them some to get them to work.
It's probably a good idea not to generate scripts for everything at
once, but to start with the tables first, then the views, then the SP's
etc etc...
Michelle wrote:
> Hongju - can you be a little more specific on how to back up and restore t
he
> databases. I have a very similar problem. I created a project that has a
> database in APP_DATA. I'm hosting on GoDaddy and I need to take the APP_D
ATA
> database (.mdf) and move it to one of their MySQL databases. I have no cl
ue
> on how to do that. Any help would be greatly appreciated.
> Thanks
> "hongju" wrote:
>

How to develop a report to use a global datasource?

Hi all..
By using report manager, I can configure a datasource that can be global for
all reports (stored in "/" path).
When I develop the report in VS.NET, I can set the report to use a shared
datasource or a private datasource. When I use a shared datasource, a new
datasource is created under the report path when I deploy it. That way I
always have to manually change connection information of the report using
report manager.
Is there a way to point to the global datasource at design time?
thanks
JaimeHi,
use <DataSourceReference>DSN path</DataSourceReference>
inside the <DataSource> tag for each report, to use a gloal datasource.
Thanks
Bava
"Jaime Stuardo" wrote:
> Hi all..
> By using report manager, I can configure a datasource that can be global for
> all reports (stored in "/" path).
> When I develop the report in VS.NET, I can set the report to use a shared
> datasource or a private datasource. When I use a shared datasource, a new
> datasource is created under the report path when I deploy it. That way I
> always have to manually change connection information of the report using
> report manager.
> Is there a way to point to the global datasource at design time?
> thanks
> Jaime

How to develop a program which can monitor the change of a table content?

I need this exe program to monitor the insert and update action of a
table in SQL Server 2000 initiatively
How to delevop this program?
Who can provide me some advice or some information?
Thanks a lot.This is a multi-part message in MIME format.
--=_NextPart_000_0051_01C3CAB1.5A9963D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
See my reply in .programming.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Simon Peng" <pengxq@.hotmail.com> wrote in message
news:tpcluvo1vuac96vtrplv36sndhoiheg9ck@.4ax.com...
I need this exe program to monitor the insert and update action of a
table in SQL Server 2000 initiatively
How to delevop this program?
Who can provide me some advice or some information?
Thanks a lot.
--=_NextPart_000_0051_01C3CAB1.5A9963D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

See my reply in =.programming.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Simon Peng" wrote in =message news:tpcluvo1vua=c96vtrplv36sndhoiheg9ck@.4ax.com...I need this exe program to monitor the insert and update action of =atable in SQL Server 2000 initiativelyHow to delevop this program?Who can =provide me some advice or some information?Thanks a lot.

--=_NextPart_000_0051_01C3CAB1.5A9963D0--|||Why dont you use triggers to audit the table|||sysindexes table records all update, insert, delete activity for all tables. this may work for you. check it out.sql

How to determine, inside a function, if a linked-server-query returned results

Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@.Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:

SET @.SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @.Code + ''')'
EXEC sp_executesql @.SQL

(CAT_ASA is the linked-server's name)

Then, i would use @.@.ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!I never worked with an ASA6 db but how about using four-part naming instead of OpenQuery? In a normal query, you can use variables in your where clauses. So, if the column type of CODE is not something out of the ordinary and recognized by SQL Server, everything should run fine. There could be interface problems but usually with a query as simple as yours, it should work.

This is a simple solution that doesn't really answer your question. Consider it as a possible workaround.

Good luck,

Skip.|||Thanks for your answer, Skip. I also tried using a four part name, but SQL server gave me a message saying that the ODBC Interface doesnt support four-part names. I tried with a 3 part name (linkedservername.database.table), but it still doesnt works. The error was diferent (so, I supose that the names with this ODBC interface must have three parts). I read in another thread that the only way to make a query to a linked server was using OPENQUERY or OPENROWSET. Im not really sure about that, but i tried many ways using 3 or 4 part names and it never worked.|||In addition i tried something like this:
SELECT * FROM OPENQUERY(CAT_ASA,'SELECT code FROM COUNTRIES') WHERE code=@.Code

Here i dont have to use an EXEC, so it works in a function, and i can filter the results with a condition. The problem is (sorry for not saying it before) that i wrote a very simple example, but the real query has 4 nested joins, and (because of performance) i should make it in only 1 query.
Thats why I cannot make something like this:

SELECT * FROM OPENQUERY(CAT_ASA,'SELECT * FROM Table1')
INNER JOIN (OPENQUERY(CAT_ASA,'SELECT * FROM Table2') ON ... )

Because i would make 4 OPENQUERY, which results in a very poor performance (10/14 secs per query!!!).

Another solution would be making the join inside the OPENQUERY, and filtering the results in SQL Server, like this:
SELECT * FROM OPENQUERY(CAT_ASA,'SELECT * FROM Table1 inner join (Table2 inner join (Table3 inner join Table 4 on...) on...)....
WHERE ...

Obviously this is worse than using 4 openquerys, because four joins without conditions (except on PKs) would return a very big quantity of records (in the order of 6.000.000.000!!!!!) and, after the conditions, that number would be reduced to 0 or 1 record (remember, i must check only the EXISTENCE of a record). That would be very inefficient.

So, I think in two ways for solving this:
1) Using the right part names (3 or 4), and making a normal query.
2) Find another method to execute a string query (or, more precisely, to determine if a string query has results), that can be used inside a function.
Thanks

How to Determine, how much recordsets returns Query?

Hi all!

Is there any chanse to determine in Transact-SQL, how much recordsets/rows already returned by currently executing query? I don't need count of rows affected by last statement (that @.@.ROWCOUNT returns), but ones, really returned to SQL-Client.

To understand, what I need it for, please see: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1707794&SiteID=1#1715230

Solution found!

See: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1716062&SiteID=1&mode=1#1716062

how to determine who has dba privileges?

for sql servers how can i determine who are the dbas?Hi,
Execute the below command:-
sp_helpsrvrolemember 'SYSADMIN'
Who ever comes under this result set will be able to do all the activities
inside that sql server instance.
For Database level DBA previlages, execute
sp_helprolemember 'db_owner'
Thanks
Hari
SQL Server MVP
"nlehrer" <nlehrer@.discussions.microsoft.com> wrote in message
news:CECA1001-EC8C-4EEC-866F-ECEB6B3C5C44@.microsoft.com...
> for sql servers how can i determine who are the dbas?|||thanks. where do i enter the command?
"Hari Prasad" wrote:

> Hi,
> Execute the below command:-
> sp_helpsrvrolemember 'SYSADMIN'
> Who ever comes under this result set will be able to do all the activities
> inside that sql server instance.
> For Database level DBA previlages, execute
> sp_helprolemember 'db_owner'
> Thanks
> Hari
> SQL Server MVP
>
>
> "nlehrer" <nlehrer@.discussions.microsoft.com> wrote in message
> news:CECA1001-EC8C-4EEC-866F-ECEB6B3C5C44@.microsoft.com...
>
>|||Hi,
Login to SQL Server using Query analyzer and enter your commands.
If it is MSDE then execute the commands from command prompt
OSQL -E
This will allow you to go to a SQL prompt, there u could type those
commands.
Thanks
Hari
SQL Server Mvp
"nlehrer" <nlehrer@.discussions.microsoft.com> wrote in message
news:9B6BA8B8-CFA5-4435-B6C1-9906B7DBD8D4@.microsoft.com...[vbcol=seagreen]
> thanks. where do i enter the command?
> "Hari Prasad" wrote:
>|||thank you.
"Hari Prasad" wrote:

> Hi,
> Login to SQL Server using Query analyzer and enter your commands.
>
> If it is MSDE then execute the commands from command prompt
> OSQL -E
> This will allow you to go to a SQL prompt, there u could type those
> commands.
> Thanks
> Hari
> SQL Server Mvp
> "nlehrer" <nlehrer@.discussions.microsoft.com> wrote in message
> news:9B6BA8B8-CFA5-4435-B6C1-9906B7DBD8D4@.microsoft.com...
>
>

Friday, March 30, 2012

How to disabel the Microsoft Search Service

How to direct T-SQL executoin output to a file

How to direct T-SQL executoin output to a file

How to direct temp files for DTS packages

How to direct temp files for DTS packages

How to direct temp files for DTS packages

How to diplay a dataset in a table

How to differentiate SQL 2000 and MSDE installs

How to differentiate process instances in perfmon

How to differentiate between an SQL 2000 installation and an MSDE

how to Diff vs Sum in Group By query?

how to diff the data of same table of two sql servers

how to diff the data of same table of two sql servers

how to diff the data of same table of two sql servers

how to diaplay date in MM/dd/yyyy format

how to diagnostic performance issue?

how to diagnostic performance issue?

How to diagnose memory bloating?

How to diagnose memory bloating?

How to diagnose memory bloating?

How to diagnose IO Problems

How to diagnose IO Problems

How to diagnose IO Problems

How to Diagnose IO Performance On A SAN

How to diagnose a deadly embrace

How to develope a notification service application

How to develope a notification service application

How to Develop Today Screen Plug in For WM5 Pocket PC

How to develop database locally and post to web host?

How to develop database locally and post to web host?

How to develop database locally and post to web host?

How to develop a report to use a global datasource?

How to develop a program which can monitor the change of a table content?

How to determine, inside a function, if a linked-server-query returned results

How to Determine, how much recordsets returns Query?

how to determine who has dba privileges?

define Hierarchy

Blog Archive

About Me