kill running query: February 2012

Friday, February 24, 2012

How to know what Version is Sql 2000

Hi Folks:
How does one determine what version and Service Pak level is SQL Server. We
have SBS 2003 and I am not sure what Service Paks have been applied.
Many thanks for your help.
JM
http://www.aspfaq.com/show.asp?id=2160
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JBM" <JBM@.nowhere.com> wrote in message news:e$llvXv0FHA.1264@.tk2msftngp13.phx.gbl...
> Hi Folks:
> How does one determine what version and Service Pak level is SQL Server. We
> have SBS 2003 and I am not sure what Service Paks have been applied.
> Many thanks for your help.
> JM
>
|||Open EM, right click on the servername select properties and look for
"Product version"
..194 is RTM
..760 is SP3
The rest of the specific build numbers can be seen in the link provided by
Tibor
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"JBM" <JBM@.nowhere.com> wrote in message
news:e$llvXv0FHA.1264@.tk2msftngp13.phx.gbl...
> Hi Folks:
> How does one determine what version and Service Pak level is SQL Server.
> We have SBS 2003 and I am not sure what Service Paks have been applied.
> Many thanks for your help.
> JM
>

How to know what Version is Sql 2000

Hi Folks:
How does one determine what version and Service Pak level is SQL Server. We
have SBS 2003 and I am not sure what Service Paks have been applied.
Many thanks for your help.
JMhttp://www.aspfaq.com/show.asp?id=2160
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JBM" <JBM@.nowhere.com> wrote in message news:e$llvXv0FHA.1264@.tk2msftngp13.phx.gbl...
> Hi Folks:
> How does one determine what version and Service Pak level is SQL Server. We
> have SBS 2003 and I am not sure what Service Paks have been applied.
> Many thanks for your help.
> JM
>|||Open EM, right click on the servername select properties and look for
"Product version"
.194 is RTM
.760 is SP3
The rest of the specific build numbers can be seen in the link provided by
Tibor
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"JBM" <JBM@.nowhere.com> wrote in message
news:e$llvXv0FHA.1264@.tk2msftngp13.phx.gbl...
> Hi Folks:
> How does one determine what version and Service Pak level is SQL Server.
> We have SBS 2003 and I am not sure what Service Paks have been applied.
> Many thanks for your help.
> JM
>

How to know what Version is Sql 2000

Hi Folks:
How does one determine what version and Service Pak level is SQL Server. We
have SBS 2003 and I am not sure what Service Paks have been applied.
Many thanks for your help.
JMhttp://www.aspfaq.com/show.asp?id=2160
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JBM" <JBM@.nowhere.com> wrote in message news:e$llvXv0FHA.1264@.tk2msftngp13.phx.gbl...[vbcol
=seagreen]
> Hi Folks:
> How does one determine what version and Service Pak level is SQL Server. W
e
> have SBS 2003 and I am not sure what Service Paks have been applied.
> Many thanks for your help.
> JM
>[/vbcol]|||Open EM, right click on the servername select properties and look for
"Product version"
.194 is RTM
.760 is SP3
The rest of the specific build numbers can be seen in the link provided by
Tibor
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"JBM" <JBM@.nowhere.com> wrote in message
news:e$llvXv0FHA.1264@.tk2msftngp13.phx.gbl...
> Hi Folks:
> How does one determine what version and Service Pak level is SQL Server.
> We have SBS 2003 and I am not sure what Service Paks have been applied.
> Many thanks for your help.
> JM
>

How to know what sql version is installed.

Hi, we are verifying the licenses of sql server that we have installed
on al the company. But we dont know how to know with type of
installation a machine has.
For example, if i execute the select @.@.version statement in QA it
returns all the information But we need a way to know this information
at a file level.
Do you know how can i get this information without using sql sentences.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Maria,
I just this week wrote this kixTart script that checks for the product
version of a local SQL client tools installation. We will be running it
through Scriptlogic which is the script running tool that we use
(recommended). Scriptlogic is a kixtart environment, but the script could
easily be made into vbscript.
The @.@.version command checks to see what is installed on the server that
hosts the database regardless of where you run it from.
$LogFile = "\\Acsb\Common\Administration\Shared\DB\inv\Cver.t xt"
$SQLFile = "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe"
$AnaLiveFile = "C:\Anasazi\Live\AS\APP\ATRAIN.ICO"
$AnaExist = "No Value"
If Exist ("$AnaLiveFile")
$AnaExist = "Y"
Else
$AnaExist = "N"
EndIf
If Exist ("$SQLFile")
$Cver = GetFileVersion("$SQLFile", "ProductVersion")
Else
$Cver = "No SQl File"
EndIf
$ReturnCode = RedirectOutput("$LogFile","0")
If $ReturnCode = 0
? @.WKSTA + "," + $Cver + "," + $AnaExist + "," + @.USERID + "," +
@.PRODUCTTYPE + "," + @.FULLNAME + "," + @.IPADDRESS0 + "," + @.DATE + "," + @.CSD
EndIf
The product version of osql.exe will be 800.760 if Service pack 3 is
installed, 800.194 if not. I hope that helps.
"MariaGuzman" wrote:

> Hi, we are verifying the licenses of sql server that we have installed
> on al the company. But we don4t know how to know with type of
> installation a machine has.
> For example, if i execute the select @.@.version statement in QA it
> returns all the information But we need a way to know this information
> at a file level.
> Do you know how can i get this information without using sql sentences.
> Thanks a lot for your help.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

How to know what sql version is installed.

Hi, we are verifying the licenses of sql server that we have installed
on al the company. But we dont know how to know with type of
installation a machine has.
For example, if i execute the select @.@.version statement in QA it
returns all the information But we need a way to know this information
at a file level.
Do you know how can i get this information without using sql sentences.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Maria,
I just this week wrote this kixTart script that checks for the product
version of a local SQL client tools installation. We will be running it
through Scriptlogic which is the script running tool that we use
(recommended). Scriptlogic is a kixtart environment, but the script could
easily be made into vbscript.
The @.@.version command checks to see what is installed on the server that
hosts the database regardless of where you run it from.
$LogFile = " \\Acsb\Common\Administration\Shared\DB\i
nv\Cver.txt"
$SQLFile = "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe"
$AnaLiveFile = "C:\Anasazi\Live\AS\APP\ATRAIN.ICO"
$AnaExist = "No Value"
If Exist ("$AnaLiveFile")
$AnaExist = "Y"
Else
$AnaExist = "N"
EndIf
If Exist ("$SQLFile")
$Cver = GetFileVersion("$SQLFile", "ProductVersion")
Else
$Cver = "No SQl File"
EndIf
$ReturnCode = RedirectOutput("$LogFile","0")
If $ReturnCode = 0
? @.WKSTA + "," + $Cver + "," + $AnaExist + "," + @.USERID + "," +
@.PRODUCTTYPE + "," + @.FULLNAME + "," + @.IPADDRESS0 + "," + @.DATE + "," + @.CS
D
EndIf
The product version of osql.exe will be 800.760 if Service pack 3 is
installed, 800.194 if not. I hope that helps.
"MariaGuzman" wrote:

> Hi, we are verifying the licenses of sql server that we have installed
> on al the company. But we don4t know how to know with type of
> installation a machine has.
> For example, if i execute the select @.@.version statement in QA it
> returns all the information But we need a way to know this information
> at a file level.
> Do you know how can i get this information without using sql sentences.
> Thanks a lot for your help.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

How to know what sql version is installed.

Hi, we are verifying the licenses of sql server that we have installed
on al the company. But we don´t know how to know with type of
installation a machine has.
For example, if i execute the select @.@.version statement in QA it
returns all the information But we need a way to know this information
at a file level.
Do you know how can i get this information without using sql sentences.
Thanks a lot for your help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Maria,
I just this week wrote this kixTart script that checks for the product
version of a local SQL client tools installation. We will be running it
through Scriptlogic which is the script running tool that we use
(recommended). Scriptlogic is a kixtart environment, but the script could
easily be made into vbscript.
The @.@.version command checks to see what is installed on the server that
hosts the database regardless of where you run it from.
$LogFile = "\\Acsb\Common\Administration\Shared\DB\inv\Cver.txt"
$SQLFile = "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe"
$AnaLiveFile = "C:\Anasazi\Live\AS\APP\ATRAIN.ICO"
$AnaExist = "No Value"
If Exist ("$AnaLiveFile")
$AnaExist = "Y"
Else
$AnaExist = "N"
EndIf
If Exist ("$SQLFile")
$Cver = GetFileVersion("$SQLFile", "ProductVersion")
Else
$Cver = "No SQl File"
EndIf
$ReturnCode = RedirectOutput("$LogFile","0")
If $ReturnCode = 0
? @.WKSTA + "," + $Cver + "," + $AnaExist + "," + @.USERID + "," +
@.PRODUCTTYPE + "," + @.FULLNAME + "," + @.IPADDRESS0 + "," + @.DATE + "," + @.CSD
EndIf
The product version of osql.exe will be 800.760 if Service pack 3 is
installed, 800.194 if not. I hope that helps.
"MariaGuzman" wrote:
> Hi, we are verifying the licenses of sql server that we have installed
> on al the company. But we don4t know how to know with type of
> installation a machine has.
> For example, if i execute the select @.@.version statement in QA it
> returns all the information But we need a way to know this information
> at a file level.
> Do you know how can i get this information without using sql sentences.
> Thanks a lot for your help.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

How to know what schema was modified

Hello,

Is there a way in MSSQL server to find all the objects in the database
based on the modified date rather than the created date.

Thanks in advance
Kumuunfortunately, sqlserver does not store such info. so, you can't.

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"kumu" <forlist2001@.yahoo.com> wrote in message
news:a44abd39.0311191320.6e962c15@.posting.google.c om...
> Hello,
> Is there a way in MSSQL server to find all the objects in the database
> based on the modified date rather than the created date.
> Thanks in advance
> Kumu

How to know What Query Statement that consume the Server resourcesmost ?

Hi all,
We have problem with SQL Server 2000 : there something in the
application that cause the server out of memory. I suspect this is
because an inefficient and heavy query.
In Oracle we have TOP SQL from Enterprise Manager to see what query
that consume the server resources most, How can I get that information
in SQL Server 2000 ?
Thank you for your help,
Krist
Hi
It is possible that long running queries and /or queries that performs lots
READs are consumed resources. SQL Server Profiler tool is your friend
<krislioe@.gmail.com> wrote in message
news:157f6947-088e-4bf7-bcad-753ceff7be3c@.s37g2000prg.googlegroups.com...
> Hi all,
> We have problem with SQL Server 2000 : there something in the
> application that cause the server out of memory. I suspect this is
> because an inefficient and heavy query.
> In Oracle we have TOP SQL from Enterprise Manager to see what query
> that consume the server resources most, How can I get that information
> in SQL Server 2000 ?
> Thank you for your help,
> Krist
>
|||To really get a look at the internals of SQL executions and resource usage
you need to be on SQL 2005+. Not much info available in 2000.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<krislioe@.gmail.com> wrote in message
news:157f6947-088e-4bf7-bcad-753ceff7be3c@.s37g2000prg.googlegroups.com...
> Hi all,
> We have problem with SQL Server 2000 : there something in the
> application that cause the server out of memory. I suspect this is
> because an inefficient and heavy query.
> In Oracle we have TOP SQL from Enterprise Manager to see what query
> that consume the server resources most, How can I get that information
> in SQL Server 2000 ?
> Thank you for your help,
> Krist
>

How to know What Query Statement that consume the Server resources

Hi all,
We have problem with SQL Server 2000 : there something in the
application that cause the server out of memory. I suspect this is
because an inefficient and heavy query.
In Oracle we have TOP SQL from Enterprise Manager to see what query
that consume the server resources most, How can I get that information
in SQL Server 2000 ?
Thank you for your help,
KristHi
It is possible that long running queries and /or queries that performs lots
READs are consumed resources. SQL Server Profiler tool is your friend
<krislioe@.gmail.com> wrote in message
news:157f6947-088e-4bf7-bcad-753ceff7be3c@.s37g2000prg.googlegroups.com...
> Hi all,
> We have problem with SQL Server 2000 : there something in the
> application that cause the server out of memory. I suspect this is
> because an inefficient and heavy query.
> In Oracle we have TOP SQL from Enterprise Manager to see what query
> that consume the server resources most, How can I get that information
> in SQL Server 2000 ?
> Thank you for your help,
> Krist
>|||To really get a look at the internals of SQL executions and resource usage
you need to be on SQL 2005+. Not much info available in 2000.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<krislioe@.gmail.com> wrote in message
news:157f6947-088e-4bf7-bcad-753ceff7be3c@.s37g2000prg.googlegroups.com...
> Hi all,
> We have problem with SQL Server 2000 : there something in the
> application that cause the server out of memory. I suspect this is
> because an inefficient and heavy query.
> In Oracle we have TOP SQL from Enterprise Manager to see what query
> that consume the server resources most, How can I get that information
> in SQL Server 2000 ?
> Thank you for your help,
> Krist
>

How to know what is the default sort order of a database

Hi all,

The server is sql server 2000, it has a database with collation SQL_Latin_General_CP1_CI_AS. How to know what is the default sort order of it? By the way, is it possible to use a query using query analyser to find the sort order of the db? Thanks in advance.

Look at the code page definition

CI = Case Insenstive
AS = Accent Sensitive

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

As far as a query you can find the sort order/sollation for individual databases with

SELECT name, collation_name
FROM master.sys.databases

For the entire server instance, you can find the installed collation with

SELECT SERVERPROPERTY ('collation')

How to know what is filling UpTempdb

Hi all,
I have a question regarding my TempDB,The initial size of
my tempdb was 10MB and it has grown to 250mb in a have day
and then i wanted to test my vb application a simple
stored proc couldn't return a resultset and errored out
saying Query Timed Out ,Then i tried to increse the size
of Tempdb to 1000MB ,now the query runs fine but i want to
know what is filling the tempdb space and how to clean up
without restarting the sql server.
Thanks
Sudhahttp://www.aspfaq.com/2446
http://www.aspfaq.com/
(Reverse address to reply.)
"Sudha Reddy" <anonymous@.discussions.microsoft.com> wrote in message
news:17fe301c4499d$54e7b160$a001280a@.phx
.gbl...
> Hi all,
> I have a question regarding my TempDB,The initial size of
> my tempdb was 10MB and it has grown to 250mb in a have day
> and then i wanted to test my vb application a simple
> stored proc couldn't return a resultset and errored out
> saying Query Timed Out ,Then i tried to increse the size
> of Tempdb to 1000MB ,now the query runs fine but i want to
> know what is filling the tempdb space and how to clean up
> without restarting the sql server.
> Thanks
> Sudha|||Thanks for the link it helps
>--Original Message--
>http://www.aspfaq.com/2446
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Sudha Reddy" <anonymous@.discussions.microsoft.com> wrote
in message
> news:17fe301c4499d$54e7b160$a001280a@.phx
.gbl...
of[vbcol=seagreen]
day[vbcol=seagreen]
to[vbcol=seagreen]
up[vbcol=seagreen]
>
>.
>

How to know what is filling UpTempdb

Hi all,
I have a question regarding my TempDB,The initial size of
my tempdb was 10MB and it has grown to 250mb in a have day
and then i wanted to test my vb application a simple
stored proc couldn't return a resultset and errored out
saying Query Timed Out ,Then i tried to increse the size
of Tempdb to 1000MB ,now the query runs fine but i want to
know what is filling the tempdb space and how to clean up
without restarting the sql server.
Thanks
Sudhahttp://www.aspfaq.com/2446
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sudha Reddy" <anonymous@.discussions.microsoft.com> wrote in message
news:17fe301c4499d$54e7b160$a001280a@.phx.gbl...
> Hi all,
> I have a question regarding my TempDB,The initial size of
> my tempdb was 10MB and it has grown to 250mb in a have day
> and then i wanted to test my vb application a simple
> stored proc couldn't return a resultset and errored out
> saying Query Timed Out ,Then i tried to increse the size
> of Tempdb to 1000MB ,now the query runs fine but i want to
> know what is filling the tempdb space and how to clean up
> without restarting the sql server.
> Thanks
> Sudha|||Thanks for the link it helps
>--Original Message--
>http://www.aspfaq.com/2446
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Sudha Reddy" <anonymous@.discussions.microsoft.com> wrote
in message
>news:17fe301c4499d$54e7b160$a001280a@.phx.gbl...
>> Hi all,
>> I have a question regarding my TempDB,The initial size
of
>> my tempdb was 10MB and it has grown to 250mb in a have
day
>> and then i wanted to test my vb application a simple
>> stored proc couldn't return a resultset and errored out
>> saying Query Timed Out ,Then i tried to increse the size
>> of Tempdb to 1000MB ,now the query runs fine but i want
to
>> know what is filling the tempdb space and how to clean
up
>> without restarting the sql server.
>> Thanks
>> Sudha
>
>.
>

How to know what is filling UpTempdb

Hi all,
I have a question regarding my TempDB,The initial size of
my tempdb was 10MB and it has grown to 250mb in a have day
and then i wanted to test my vb application a simple
stored proc couldn't return a resultset and errored out
saying Query Timed Out ,Then i tried to increse the size
of Tempdb to 1000MB ,now the query runs fine but i want to
know what is filling the tempdb space and how to clean up
without restarting the sql server.
Thanks
Sudha
http://www.aspfaq.com/2446
http://www.aspfaq.com/
(Reverse address to reply.)
"Sudha Reddy" <anonymous@.discussions.microsoft.com> wrote in message
news:17fe301c4499d$54e7b160$a001280a@.phx.gbl...
> Hi all,
> I have a question regarding my TempDB,The initial size of
> my tempdb was 10MB and it has grown to 250mb in a have day
> and then i wanted to test my vb application a simple
> stored proc couldn't return a resultset and errored out
> saying Query Timed Out ,Then i tried to increse the size
> of Tempdb to 1000MB ,now the query runs fine but i want to
> know what is filling the tempdb space and how to clean up
> without restarting the sql server.
> Thanks
> Sudha
|||Thanks for the link it helps
>--Original Message--
>http://www.aspfaq.com/2446
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Sudha Reddy" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:17fe301c4499d$54e7b160$a001280a@.phx.gbl...
of[vbcol=seagreen]
day[vbcol=seagreen]
to[vbcol=seagreen]
up
>
>.
>

how to know time of insertion of a row

Hi
Is there a way to know time of insertion of a row without using,
timestamp,currentdate function etc. Isthere any DBCC COMMAND which gives me
the date of insertion of a row.
--
Thanks in Advance
Regards
SuparichituduNo, there isn't
However , you are be able to create a column with GEDATE() as a DEFAULT
CONSTRAINT.
CREATE TABLE Test (col1 INT,col2 DATETIME DEFAULT GETDATE())
GO
INSERT INTO Test (col1) VALUES (100)
GO
SELECT * FROM Test
"Suparichithudu" <Suparichithudu@.discussions.microsoft.com> wrote in message
news:42420237-454F-49EE-B756-21326EAADE0C@.microsoft.com...
> Hi
> Is there a way to know time of insertion of a row without using,
> timestamp,currentdate function etc. Isthere any DBCC COMMAND which gives
> me
> the date of insertion of a row.
> --
> Thanks in Advance
> Regards
> Suparichitudu
>|||if you're in the unenviable situation where you need to retrospectively
find this out, one thing that might be able to help you is if you can
get hold of the transaction logs over the period that you know it was
inserted. However that would require you to not be using a simple
recovery model.
There are some tools you can get free trials of which will query a
logfile, but I'm not sure of their reliability.

How to know the User who disabled a Job?

Hi all,

Is there any way I can know who Disabled a Job??

bye.Hi

I don't think there is anywhere that stores this.

John

"sasi" <vardhansasi@.gmail.com> wrote in message
news:1135868860.566217.105180@.o13g2000cwo.googlegr oups.com...
> Hi all,
> Is there any way I can know who Disabled a Job??
> bye.|||Make use of Profiler

Madhivanan|||How can I use Profiler to find the user who disabled the job?

Can u b more descriptive or lead to some reference.|||Hi

Check out the code from the following post and create a stored procedure to
do this http://tinyurl.com/bu2f9. You may want to add your own filter. Also
lookup sp_procoption in Books Online to make the procedure run at startup.

John

"sasi" <vardhansasi@.gmail.com> wrote in message
news:1136450853.470529.143800@.g49g2000cwa.googlegr oups.com...
> How can I use Profiler to find the user who disabled the job?
> Can u b more descriptive or lead to some reference.

How to know the table size in sql database and how to know which table has updated

Hi,
May i check with you, how to know the size of the table in the database. If
someone has updated one data into one table, how to know, which table has
updated at the database?
Thanks.
regards,
florenceHi,
To get the space used:-
SP_spaceused <table_name>
Enable the profiler to identify the updates happened.
Thanks
Hari
SQL Server MVP
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:OYkrcy$vEHA.612@.TK2MSFTNGP15.phx.gbl...
> Hi,
> May i check with you, how to know the size of the table in the database.
> If
> someone has updated one data into one table, how to know, which table has
> updated at the database?
> Thanks.
> regards,
> florence
>|||Hi,
Thanks for your reply..another question, what if i want to know which
table has the biggest size?
Thanks.
regards,
florence
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#IDW43$vEHA.2192@.TK2MSFTNGP14.phx.gbl...
> Hi,
> To get the space used:-
> SP_spaceused <table_name>
> Enable the profiler to identify the updates happened.
>
> --
> Thanks
> Hari
> SQL Server MVP
>
> "Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
> news:OYkrcy$vEHA.612@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > May i check with you, how to know the size of the table in the database.
> > If
> > someone has updated one data into one table, how to know, which table
has
> > updated at the database?
> >
> > Thanks.
> >
> > regards,
> > florence
> >
> >
>|||Hi
Probably a best method will be to run with cursor thru each table and
perform select count(*) from table and then compare which one has a big
output
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:OufvGNAwEHA.3320@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Thanks for your reply..another question, what if i want to know which
> table has the biggest size?
> Thanks.
> regards,
> florence
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:#IDW43$vEHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > To get the space used:-
> >
> > SP_spaceused <table_name>
> >
> > Enable the profiler to identify the updates happened.
> >
> >
> > --
> > Thanks
> > Hari
> > SQL Server MVP
> >
> >
> > "Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
> > news:OYkrcy$vEHA.612@.TK2MSFTNGP15.phx.gbl...
> > > Hi,
> > >
> > > May i check with you, how to know the size of the table in the
database.
> > > If
> > > someone has updated one data into one table, how to know, which table
> has
> > > updated at the database?
> > >
> > > Thanks.
> > >
> > > regards,
> > > florence
> > >
> > >
> >
> >
>|||YOu may also want to write a query directly agains sysindexes... If I
remember correctly, dpages, ipages, and reserved..You might want to take a
look at sp_spaceused and steal som code from there to come up with your
own... or search the web ( maybe www.sqlservercentral.com) probably has a
script you can use..
--
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
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBkRydAwEHA.1976@.TK2MSFTNGP09.phx.gbl...
> Hi
> Probably a best method will be to run with cursor thru each table and
> perform select count(*) from table and then compare which one has a big
> output
>
>
> "Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
> news:OufvGNAwEHA.3320@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> > Thanks for your reply..another question, what if i want to know
which
> > table has the biggest size?
> >
> > Thanks.
> > regards,
> >
> > florence
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:#IDW43$vEHA.2192@.TK2MSFTNGP14.phx.gbl...
> > > Hi,
> > >
> > > To get the space used:-
> > >
> > > SP_spaceused <table_name>
> > >
> > > Enable the profiler to identify the updates happened.
> > >
> > >
> > > --
> > > Thanks
> > > Hari
> > > SQL Server MVP
> > >
> > >
> > > "Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
> > > news:OYkrcy$vEHA.612@.TK2MSFTNGP15.phx.gbl...
> > > > Hi,
> > > >
> > > > May i check with you, how to know the size of the table in the
> database.
> > > > If
> > > > someone has updated one data into one table, how to know, which
table
> > has
> > > > updated at the database?
> > > >
> > > > Thanks.
> > > >
> > > > regards,
> > > > florence
> > > >
> > > >
> > >
> > >
> >
> >
>|||this will do the trick:
sp_MSforeachtable @.command1="print '?' Exec sp_mstablespace '?'"
What is happening here is that '?' is like a variable containing the name of
each table during the iteration that happens within the stored procedure.
Therefore, you will have the table name as output and the size of it.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Wayne Snyder" wrote:
> YOu may also want to write a query directly agains sysindexes... If I
> remember correctly, dpages, ipages, and reserved..You might want to take a
> look at sp_spaceused and steal som code from there to come up with your
> own... or search the web ( maybe www.sqlservercentral.com) probably has a
> script you can use..
> --
> 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
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uBkRydAwEHA.1976@.TK2MSFTNGP09.phx.gbl...
> > Hi
> > Probably a best method will be to run with cursor thru each table and
> > perform select count(*) from table and then compare which one has a big
> > output
> >
> >
> >
> >
> >
> > "Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
> > news:OufvGNAwEHA.3320@.TK2MSFTNGP14.phx.gbl...
> > > Hi,
> > > Thanks for your reply..another question, what if i want to know
> which
> > > table has the biggest size?
> > >
> > > Thanks.
> > > regards,
> > >
> > > florence
> > >
> > > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > > news:#IDW43$vEHA.2192@.TK2MSFTNGP14.phx.gbl...
> > > > Hi,
> > > >
> > > > To get the space used:-
> > > >
> > > > SP_spaceused <table_name>
> > > >
> > > > Enable the profiler to identify the updates happened.
> > > >
> > > >
> > > > --
> > > > Thanks
> > > > Hari
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
> > > > news:OYkrcy$vEHA.612@.TK2MSFTNGP15.phx.gbl...
> > > > > Hi,
> > > > >
> > > > > May i check with you, how to know the size of the table in the
> > database.
> > > > > If
> > > > > someone has updated one data into one table, how to know, which
> table
> > > has
> > > > > updated at the database?
> > > > >
> > > > > Thanks.
> > > > >
> > > > > regards,
> > > > > florence
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

How to know the table size in sql database and how to know which table has updated

How to know the sp_start_job job has failed?

How to know the sp_start_job job has failed?
No matter the job run successfully or not, we can only know that it started
successfully.
Job 'JobName' started successfully.
Is there any way we can know the job run result?
Thanks.You can get the information for the job (including the result of the last
run) with sp_help_job.
Jacco Schalkwijk
SQL Server MVP
"Tee" <thy@.streamyx.com> wrote in message
news:eUm5Jrr1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> How to know the sp_start_job job has failed?
> No matter the job run successfully or not, we can only know that it
> started
> successfully.
> Job 'JobName' started successfully.
> Is there any way we can know the job run result?
>
> Thanks.
>

How to know the sp_start_job job has failed?

How to know the sp_start_job job has failed?
No matter the job run successfully or not, we can only know that it started
successfully.
Job 'JobName' started successfully.
Is there any way we can know the job run result?
Thanks.If you check the sp_help_job command you can configure an
NT event to see whether the job has succeded or failed.
Peter
"Status quo, you know, that is Latin for "the mess we're
in."
Ronald Reagan
>--Original Message--
>How to know the sp_start_job job has failed?
>No matter the job run successfully or not, we can only
know that it started
>successfully.
>Job 'JobName' started successfully.
>Is there any way we can know the job run result?
>
>Thanks.
>
>.
>|||You can get the information for the job (including the result of the last
run) with sp_help_job.
--
Jacco Schalkwijk
SQL Server MVP
"Tee" <thy@.streamyx.com> wrote in message
news:eUm5Jrr1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> How to know the sp_start_job job has failed?
> No matter the job run successfully or not, we can only know that it
> started
> successfully.
> Job 'JobName' started successfully.
> Is there any way we can know the job run result?
>
> Thanks.
>

How to know the sp_start_job job has failed?

How to know the sp_start_job job has failed?
No matter the job run successfully or not, we can only know that it started
successfully.
Job 'JobName' started successfully.
Is there any way we can know the job run result?
Thanks.
You can get the information for the job (including the result of the last
run) with sp_help_job.
Jacco Schalkwijk
SQL Server MVP
"Tee" <thy@.streamyx.com> wrote in message
news:eUm5Jrr1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> How to know the sp_start_job job has failed?
> No matter the job run successfully or not, we can only know that it
> started
> successfully.
> Job 'JobName' started successfully.
> Is there any way we can know the job run result?
>
> Thanks.
>

How to know the rows count of a Field

I use a matrix which datasource is come from a olap
How can I know the rows count of a Field?Hi,
you can use the CountRows function. You can find more information about this
function at BOL.
Bye!
"ad" wrote:
> I use a matrix which datasource is come from a olap
> How can I know the rows count of a Field?
>
>|||Thanks,
I have tried, but I found that CountRows can only count f group members
which contain itself,
But if I want to know the count of group members of another(Say I want to
know the rows count of a column group in a row group),
how can I do that?
"Javier Catala" <JavierCatala@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:74E94833-7F46-4D5C-882D-02754D9715FA@.microsoft.com...
> Hi,
> you can use the CountRows function. You can find more information about
this
> function at BOL.
> Bye!
> "ad" wrote:
> > I use a matrix which datasource is come from a olap
> > How can I know the rows count of a Field?
> >
> >
> >

How to know the Reporting services version at runtime

HI, can anybody help me?
I have a problem. With the sqlserver 2005 sp2 some behavieur are differt from previous versions.
For example with service pack 1 there isn't the "select all" option for multi value parameters, so i add MY select all, but with service pack 2 there is the "select all" option for multivalue paramteres, so when my reports are running under a server with service pack 2 in the list of multivalue parameter I'm going to find "Select all" and "MY select all"
It is possible to know at runtime under which version my repors will be running? so I can use or not MY select all. (i cannot force client with a particular version)

Thank in advance

If you can call any webservice method (e.g. ListSecureMethods), you will get back a serverinfoheader, where you can look into reportserverversion property to determine the server version.

How to know the Primary Key of a Table?

Hi,
I need a quey that gives me the Field(s) that form the Primary Key of the
Table. I can't find anything about it on google, does anybody knwo how to
achieve this?
Thanks a lot in advance,
PieterThis exact question was posted here or in .programming yesterday.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need a quey that gives me the Field(s) that form the Primary Key of the
> Table. I can't find anything about it on google, does anybody knwo how to
> achieve this?
> Thanks a lot in advance,
> Pieter
>|||Thanks, I found it!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
> This exact question was posted here or in .programming yesterday.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I need a quey that gives me the Field(s) that form the Primary Key of
the
> > Table. I can't find anything about it on google, does anybody knwo how
to
> > achieve this?
> >
> > Thanks a lot in advance,
> >
> > Pieter
> >
> >
>|||Justo nel ittle remark: When i entered a new row directly in the Enterprise
Manager: I got this as 'query': "sp_executesql;1"
is this normal? does it only occur in that situation?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
> This exact question was posted here or in .programming yesterday.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I need a quey that gives me the Field(s) that form the Primary Key of
the
> > Table. I can't find anything about it on google, does anybody knwo how
to
> > achieve this?
> >
> > Thanks a lot in advance,
> >
> > Pieter
> >
> >
>|||I never use EM do modify data in the tables (Aaron has a FAQ on this at www.aspfaq.com), but it is
possible that EM uses prepared SQL or similar to do modifications...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.phx.gbl...
> Justo nel ittle remark: When i entered a new row directly in the Enterprise
> Manager: I got this as 'query': "sp_executesql;1"
> is this normal? does it only occur in that situation?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
>> This exact question was posted here or in .programming yesterday.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
>> > Hi,
>> >
>> > I need a quey that gives me the Field(s) that form the Primary Key of
> the
>> > Table. I can't find anything about it on google, does anybody knwo how
> to
>> > achieve this?
>> >
>> > Thanks a lot in advance,
>> >
>> > Pieter
>> >
>> >
>>
>|||Hi Tibor,
i'm testing your really promising solution, unfortunately, when I'm doing
updates or isnert with the SqlDataAdapter of .NET, I alsogot the
"sp_executesql;1" as the used query isntead of the sql string.
You don't have any idea how to get the original Sql-suery from that
sp_executesql?
Thanks a lot in advance,
Pieter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%232tNWf0RFHA.2448@.TK2MSFTNGP10.phx.gbl...
> I never use EM do modify data in the tables (Aaron has a FAQ on this at
www.aspfaq.com), but it is
> possible that EM uses prepared SQL or similar to do modifications...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.phx.gbl...
> > Justo nel ittle remark: When i entered a new row directly in the
Enterprise
> > Manager: I got this as 'query': "sp_executesql;1"
> > is this normal? does it only occur in that situation?
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
> >> This exact question was posted here or in .programming yesterday.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> >> > Hi,
> >> >
> >> > I need a quey that gives me the Field(s) that form the Primary Key of
> > the
> >> > Table. I can't find anything about it on google, does anybody knwo
how
> > to
> >> > achieve this?
> >> >
> >> > Thanks a lot in advance,
> >> >
> >> > Pieter
> >> >
> >> >
> >>
> >>
> >
> >
>|||Hmm, the command has to be send at one point in time. Did you trace the whole session? I'm afraid
that I can't be of any more help, I'm more of an engine person and not a client tool person...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:uswywDYSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> Hi Tibor,
> i'm testing your really promising solution, unfortunately, when I'm doing
> updates or isnert with the SqlDataAdapter of .NET, I alsogot the
> "sp_executesql;1" as the used query isntead of the sql string.
> You don't have any idea how to get the original Sql-suery from that
> sp_executesql?
> Thanks a lot in advance,
> Pieter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%232tNWf0RFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> I never use EM do modify data in the tables (Aaron has a FAQ on this at
> www.aspfaq.com), but it is
>> possible that EM uses prepared SQL or similar to do modifications...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.phx.gbl...
>> > Justo nel ittle remark: When i entered a new row directly in the
> Enterprise
>> > Manager: I got this as 'query': "sp_executesql;1"
>> > is this normal? does it only occur in that situation?
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> > message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
>> >> This exact question was posted here or in .programming yesterday.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> >> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
>> >> > Hi,
>> >> >
>> >> > I need a quey that gives me the Field(s) that form the Primary Key of
>> > the
>> >> > Table. I can't find anything about it on google, does anybody knwo
> how
>> > to
>> >> > achieve this?
>> >> >
>> >> > Thanks a lot in advance,
>> >> >
>> >> > Pieter
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||And what about the fn_get_sql function? I found soem info about it, and I
guess it must be possible to do like the samething? but maybe this will give
me the exact SQL-string? If i put it in a trigger now, I get the code of the
Trigger, not the code of the SQL statement that fired thetrigger :-(
But apparently I should somehow be able to do it with the @.stmt_end and
@.stmt_start variables? Or am I wrong?
Pieter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O6mxNLbSFHA.3424@.TK2MSFTNGP10.phx.gbl...
> Hmm, the command has to be send at one point in time. Did you trace the
whole session? I'm afraid
> that I can't be of any more help, I'm more of an engine person and not a
client tool person...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:uswywDYSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> > Hi Tibor,
> >
> > i'm testing your really promising solution, unfortunately, when I'm
doing
> > updates or isnert with the SqlDataAdapter of .NET, I alsogot the
> > "sp_executesql;1" as the used query isntead of the sql string.
> >
> > You don't have any idea how to get the original Sql-suery from that
> > sp_executesql?
> >
> > Thanks a lot in advance,
> >
> > Pieter
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:%232tNWf0RFHA.2448@.TK2MSFTNGP10.phx.gbl...
> >> I never use EM do modify data in the tables (Aaron has a FAQ on this at
> > www.aspfaq.com), but it is
> >> possible that EM uses prepared SQL or similar to do modifications...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> > news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.phx.gbl...
> >> > Justo nel ittle remark: When i entered a new row directly in the
> > Enterprise
> >> > Manager: I got this as 'query': "sp_executesql;1"
> >> > is this normal? does it only occur in that situation?
> >> >
> >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> >> > message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
> >> >> This exact question was posted here or in .programming yesterday.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> >> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> >> >> > Hi,
> >> >> >
> >> >> > I need a quey that gives me the Field(s) that form the Primary Key
of
> >> > the
> >> >> > Table. I can't find anything about it on google, does anybody knwo
> > how
> >> > to
> >> >> > achieve this?
> >> >> >
> >> >> > Thanks a lot in advance,
> >> >> >
> >> >> > Pieter
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

How to know the order of entries in a table

For example some data has entered into a table in a random manner i.e the pk filed value is not in a serial fashion.Is there any table or index that holds the entries of rows into a particular table as entered .

i.e
'some_table' has data like this

3,entry3
2,entry2
4,entry4
1,entry1

I want some DB table or Index that holds data like this about above 'some_table'

row_id ... ... ...
1
2
3
4

here 1 refers to entry of the first column in 'some_table' i.e 3,entry3
and so on...Hi

Please reread my first post in here:
http://www.dbforums.com/showthread.php?t=1620041

There is no order. If you need to know the order that data was inserted then you need to set something up yourself - use an identity column or a timestamp with a GETDATE() default value. Note that the second option will result in "ties" if you insert sets of data.|||Thanks for your help...

How to know the next ID ?I

Hi all,

is it possible to know the next record's identifier of a table without inserting it? I tried with SELECT MAX(ArticleID) FROM Articles but imagine that there is no record in the table (but there was of it), the SELECT MAX statement return a null value...

Thanks in advance,

Gilles
Why do you need the next id ? What datatype is the column ? Does it have the identity property ? Normally there is no need to get the next id prior to a transaction.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||If you just want to know the next value, then try the DBCC CHECKIDENT statement. Here's an example:

DBCC CHECKIDENT ('dbo.TableName', NORESEED )

Have a look here for details: http://msdn2.microsoft.com/en-US/library/ms176057(SQL.90).aspx

If you need this value programatically, then I might suggest that you may be doing something incorrectly, as this value shouldn't be required... (this functionality is meant to be hidden by the system - it's a key value only, and shouldn't be manipulated).|||

Hi,

You are right both... But, in fact, it's just to display an information. 'cause my application can add an "estimate" (I don't know the correct word in english) or an invoice and I'd like to display the next identifier so that it serf of reference... But it's a detail...

Thank you for your contribution

Gilles

|||If you really need to do that, then I'd suggest using something like this:

SELECT (ISNULL(MAX(IdntityColumnName), 0) + 1) AS NextId FROM [TableName]

... of course keeping in mind that if two people are using your app at the same time, they'll both get the same 'estimated' ID. This example is also assuming that your identitiy records are incremented by 1 (the default, but not the necessity).|||You have to keep in mind that this estimated id is only "safe" in a oneuser enviroment, due to the already mentioned reasons by Christoph. It is NOT preferable to use that in these days, because normally more than one user will connect to a database to manipulate the data (if not now, perhaps later, themost application are brought to a wider usergroup over the time and therefore ported from a one user enviroment, and its the heck to correct all this when your application is established and stable)

Better use Transaction, whereas you get a transaction scope the next ID, while locking the table for other users to prevent the concurrency conflicts.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Yes, that's right... but i think about what you write and, initialy, it will be a single user application... The problem is I don't have your level about programming SQL. I can work with ADO.Net but I don't know enough to do advanced stored procedure...

Thank you very much for your support. Now I know that such things exist and I'll be able to be interested in it later...

Thanks and have a good nigth...

PS : Can you just tell me where I can watch to study the things that you talked about... Thanks

Gilles

|||For most Microsoft development products, simply do a Google search for "Quickstart Tutorial" - there's usually something there. Tutorials are an easy way to learn.

For ADO.NET / SQL, have a look at:
http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/ADOPlusOverview.aspx|||

Hi Gilles,

Try this instead: "SELECT IDENT_CURRENT('tableName') AS currentIdentity"

That must work.

How to know the next ID ?

Hi all,

is it possible to know the next record's identifier of a

table without inserting it?

I tried with SELECT MAX(ArticleID) FROM Articles but imagine that there is no record in the table (but there was of it), the SELECT MAX statement return a null value...

Thanks in advance,

Gilles

Why do you need the next id ? What datatype is the column ? Does it have the identity property ? Normally there is no need to get the next id prior to a transaction.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||If you just want to know the next value, then try the DBCC CHECKIDENT statement. Here's an example:

DBCC CHECKIDENT ('dbo.TableName', NORESEED )

Have a look here for details: http://msdn2.microsoft.com/en-US/library/ms176057(SQL.90).aspx

Hi,

Thank you for your contribution

Gilles

|||If you really need to do that, then I'd suggest using something like this:

SELECT (ISNULL(MAX(IdntityColumnName), 0) + 1) AS NextId FROM [TableName]

Better use Transaction, whereas you get a transaction scope the next ID, while locking the table for other users to prevent the concurrency conflicts.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thank you very much for your support. Now I know that such things exist and I'll be able to be interested in it later...

Thanks and have a good nigth...

PS : Can you just tell me where I can watch to study the things that you talked about... Thanks

Gilles

|||For most Microsoft development products, simply do a Google search for "Quickstart Tutorial" - there's usually something there. Tutorials are an easy way to learn.

For ADO.NET / SQL, have a look at:
http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/ADOPlusOverview.aspx|||

Hi Gilles,

Try this instead: "SELECT IDENT_CURRENT('tableName') AS currentIdentity"

That must work.

How to know the next ID ?

Hi all,

Thanks in advance,

Gilles
Why do you need the next id ? What datatype is the column ? Does it have the identity property ? Normally there is no need to get the next id prior to a transaction.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||If you just want to know the next value, then try the DBCC CHECKIDENT statement. Here's an example:

DBCC CHECKIDENT ('dbo.TableName', NORESEED )

Have a look here for details: http://msdn2.microsoft.com/en-US/library/ms176057(SQL.90).aspx

Hi,

Thank you for your contribution

Gilles

|||If you really need to do that, then I'd suggest using something like this:

SELECT (ISNULL(MAX(IdntityColumnName), 0) + 1) AS NextId FROM [TableName]

Better use Transaction, whereas you get a transaction scope the next ID, while locking the table for other users to prevent the concurrency conflicts.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thank you very much for your support. Now I know that such things exist and I'll be able to be interested in it later...

Thanks and have a good nigth...

PS : Can you just tell me where I can watch to study the things that you talked about... Thanks

Gilles

|||For most Microsoft development products, simply do a Google search for "Quickstart Tutorial" - there's usually something there. Tutorials are an easy way to learn.

For ADO.NET / SQL, have a look at:
http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/ADOPlusOverview.aspx|||

Hi Gilles,

Try this instead: "SELECT IDENT_CURRENT('tableName') AS currentIdentity"

That must work.

How to know the DB name?

I'm working on SQL Server 2000 and I would to know the open DB name
into a stored procedure.
Any suggestion?
Fluido
You can get the current database name with DB_NAME(). For example:
SELECT DB_NAME()
Hope this helps.
Dan Guzman
SQL Server MVP
"Fluido" <simone.79@.tiscali.it> wrote in message
news:8484a1df.0407150515.68e8cd70@.posting.google.c om...
> I'm working on SQL Server 2000 and I would to know the open DB name
> into a stored procedure.
> Any suggestion?
> Fluido

How to know the DB name?

I'm working on SQL Server 2000 and I would to know the open DB name
into a stored procedure.
Any suggestion?
FluidoYou can get the current database name with DB_NAME(). For example:
SELECT DB_NAME()
Hope this helps.
Dan Guzman
SQL Server MVP
"Fluido" <simone.79@.tiscali.it> wrote in message
news:8484a1df.0407150515.68e8cd70@.posting.google.com...
> I'm working on SQL Server 2000 and I would to know the open DB name
> into a stored procedure.
> Any suggestion?
> Fluido

How to know the DB name?

I'm working on SQL Server 2000 and I would to know the open DB name
into a stored procedure.
Any suggestion?
FluidoYou can get the current database name with DB_NAME(). For example:
SELECT DB_NAME()
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Fluido" <simone.79@.tiscali.it> wrote in message
news:8484a1df.0407150515.68e8cd70@.posting.google.com...
> I'm working on SQL Server 2000 and I would to know the open DB name
> into a stored procedure.
> Any suggestion?
> Fluido

How to know the code in Query Analyser

My Program wants to know what is being edited in Query Analyser currently.
How can I do that?

Thanks.

I would not consider it nice for an application to snoop inside of another unless there is a system to addin that functionality.

Why do you want to know what is beeing edited?

One approach if you want to get the edited text is to use the Process class to launch query analyzer editing a temporary file. You will then add a FileSystemWatcher class to check whenever the user is saving the changes to the temporaryfile. You can then pick up any changes and in that way get what is in query analyzer. But only when the user chooses to save.

When the process closes you can remove the watching of the file and the file.

|||

Thank you 4 your answer,

I just want My program to prompt the SQL,tables and so on in MS Query Analyser when the user press the Space Key or Dot Key(".") in the SQL editor.

What is your opinion?

|||

You will have to do it in your own query tool. You can have a look at Query Commander and its source code, it is a query tool with intellisense. It is written in C#.

|||

Thank you 4 your suggestion,

There is an interesting tool called promptsql, http://www.promptsql.com/

how does it work? do you have any idea?

|||

I would not consider it nice for an application to snoop inside of another unless there is a system to addin that functionality.

For query analyzer I am not familiar with any addin functionality so I would suspect that promptsql installs hooks and finds out if the active window belongs to certain application like query analyzer when certain keyboard sequences has been entered.

How to know that which table i have updated?

Hi,
I have one sql server and many users access the sql
server in the same time. How i can know which table has
been recently updated? How i can know someone has update
the some data into which table?
Can i keep track what tables or data have been
updated to sql server today? The data inside which table
has been modified?
Thank you very much.
regards,
florenceEasiest to implement would be setup a SQL Profiler Trace.
Alternitavly you could create UPDATE triggers on the tables in question
--
HTH
Ryan Waight, MCDBA, MCSE
"florencelee@.visualsolutions.com.my" <anonymous@.discussions.microsoft.com>
wrote in message news:0f5101c3a863$91345320$a501280a@.phx.gbl...
> Hi,
> I have one sql server and many users access the sql
> server in the same time. How i can know which table has
> been recently updated? How i can know someone has update
> the some data into which table?
> Can i keep track what tables or data have been
> updated to sql server today? The data inside which table
> has been modified?
> Thank you very much.
> regards,
> florence
>

how to know status of previous control flow

Hi,

I have a package which has 3 data flows and a script component to write log file. Those 3 data flows are in sequence (mean upon success, next will run). If the first 2 data flows fails, control will go to script and upon completion of 3rd, control will go to script to create log file.

Now, my question is, within my script component, is there any way to find out what was the last ran data flow and its status? What I know is, adding a 'activex task' between each data flow and script component which updates a variable and then I can use that variable in the script component to know the last ran data flow. But what I want to know is, is there any system variable (or something) which will do the samething without any need to add additional task?

Thx.

Where you say script component I assume you mean script task? (the two are different you see)

There is no way of knowing what the previous task is unless you tell it. You are proposing to do this with an ActiveX Script task. I recommend you don't do that as the are only there for backward compatibility. Use a script task instead.

-Jamie

|||

If you mean a script task, you can do this as follows:

Create a string variable at the scope of the package - call it something like LastTask. http://msdn2.microsoft.com/en-us/library/ms141670(SQL.90).aspx

Create a sequence container and build your control flow inside the sequence - you can drag in existing objects, or copy and paste them in. http://msdn2.microsoft.com/en-us/library/ms139855.aspx

Create an OnPostExecute EventHandler for the sequence container. http://msdn2.microsoft.com/en-us/library/ms139744(SQL.90).aspx

Add a Script Task to the EventHandler. Configure the Script Task to use your LastTask variable as a ReadWrite variable and the system variable SourceName. (For detailed info on using variables with the script task see http://msdn2.microsoft.com/en-us/library/ms135941.aspx)

In the ScriptTask you could use the following line to set LastTask to the name of the last task to execute within the sequence ...

Dts.Variables("LastTask").Value = Dts.Variables("SourceName").Value

If you need to insert this value into a data flow, you could use the DerivedColumn component to insert the value of LastTask into a new column in each row passing.

hth

Donald

|||

Hi Donald,

Thx for giving me the perfect solution. I have couple of questions to that. The above method will tell me which is the last task. But how do I know the status of the task? Also in case of failure how do I know the error message?

Thx again.

how to know standard or enterprise

Hi ,
Is there any way to know the sql edition running is standard or enterprise
Thanks
ARRHi
SELECT SERVERPROPERTY ('Edition')
Regards
Mike
"Aju" wrote:
> Hi ,
> Is there any way to know the sql edition running is standard or enterprise
>
> Thanks
> ARR
>
>

how to know standard or enterprise

Hi ,
Is there any way to know the sql edition running is standard or enterprise
Thanks
ARRHi
SELECT SERVERPROPERTY ('Edition')
Regards
Mike
"Aju" wrote:

> Hi ,
> Is there any way to know the sql edition running is standard or enterprise
>
> Thanks
> ARR
>
>

how to know standard or enterprise

Hi ,
Is there any way to know the sql edition running is standard or enterprise
Thanks
ARR
Hi
SELECT SERVERPROPERTY ('Edition')
Regards
Mike
"Aju" wrote:

> Hi ,
> Is there any way to know the sql edition running is standard or enterprise
>
> Thanks
> ARR
>
>

how to know space used per column

I know I can use sp_spaceused to check space used per table.
However, how can I know space used per column?SELECT SUM(DATALENGTH(<column_name> )) should get you pretty close to that.
Jacco Schalkwijk
SQL Server MVP
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:eMxBo4RBFHA.608@.TK2MSFTNGP15.phx.gbl...
>I know I can use sp_spaceused to check space used per table.
> However, how can I know space used per column?
>

how to know simultaneous connected count to a Database?

how to know simultaneous connected count to a Database?
Thank youStored Procedure 'sp_who' => Provides information about current Microsoft?
SQL Server? users and processes.
sp_who2 => list the active processes.
Lemme know if you were looking for this or something else.
Thanks,
Sree
"Asking" wrote:

> how to know simultaneous connected count to a Database?
> Thank you
>
>|||Were you looking something like below query?
Select COUNT(loginame) Count, loginame=rtrim(loginame)
From master.dbo.sysprocesses
GROUP BY loginame
Thanks,
Sree
"Asking" wrote:

> how to know simultaneous connected count to a Database?
> Thank you
>
>

how to know simultaneous connected count to a Database?

how to know simultaneous connected count to a Database?
Thank youStored Procedure 'sp_who' => Provides information about current MicrosoftÂ®
SQL Serverâ?¢ users and processes.
sp_who2 => list the active processes.
Lemme know if you were looking for this or something else.
Thanks,
Sree
"Asking" wrote:
> how to know simultaneous connected count to a Database?
> Thank you
>
>|||Were you looking something like below query?
Select COUNT(loginame) Count, loginame=rtrim(loginame)
From master.dbo.sysprocesses
GROUP BY loginame
Thanks,
Sree
"Asking" wrote:
> how to know simultaneous connected count to a Database?
> Thank you
>
>

how to know simultaneous connected count to a Database?

how to know simultaneous connected count to a Database?
Thank you
Stored Procedure 'sp_who' => Provides information about current Microsoft?
SQL Server? users and processes.
sp_who2 => list the active processes.
Lemme know if you were looking for this or something else.
Thanks,
Sree
"Asking" wrote:

> how to know simultaneous connected count to a Database?
> Thank you
>
>
|||Were you looking something like below query?
Select COUNT(loginame) Count, loginame=rtrim(loginame)
From master.dbo.sysprocesses
GROUP BY loginame
Thanks,
Sree
"Asking" wrote:

> how to know simultaneous connected count to a Database?
> Thank you
>
>

how to know simultaneous connected count to a Database?

how to know simultaneous connected count to a Database?
Thank youTo determine the number of current user database connections:
SELECT COUNT(*)
FROM master..sysprocesses
WHERE
spid > 50 AND
dbid = DB_ID('MyDatabase')
Hope this helps.
Dan Guzman
SQL Server MVP
"Asking" <asking@.ispro.net.tr> wrote in message
news:OSvVy06NGHA.3272@.tk2msftngp13.phx.gbl...
> how to know simultaneous connected count to a Database?
> Thank you
>

How to know pages flushed at checkpoint?

The checkpoints at our system take way too long.
I'd like to figure out the number of dirty pages
flushed at a checkpoint and ideally which tables and indexes
they belong to.
Is there any way to do it?
Thanks a lotHi,
Use SQL Server performance monitor counters specifically SQL Server Buffer
Mgr: Checkpoint Pages/Sec and SQL Server Buffer Mgr: Lazy Writes/Sec
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
<news1_skh@.yahoo.com> wrote in message
news:61be40e2.0308150733.56398e08@.posting.google.com...
> The checkpoints at our system take way too long.
> I'd like to figure out the number of dirty pages
> flushed at a checkpoint and ideally which tables and indexes
> they belong to.
> Is there any way to do it?
> Thanks a lot|||Thanks - these counters are very helpful.
They, however, do not give an exact info on the number
of flushed pages - just an estimate.
The reason I'd like to have an exact info is this:
checkpoints on our Production database take very long (~20 sec).
The majority of the deta from the Production DB is replicated
on another server where checkpoints take ~0.2 sec.
I'd like to compare what exactly is flushed on both servers
to see if the difference is due to the hardware problems
or there is something I can do with the DB settings
to improve performance.
Any help is greatly appreciated...

how to know logged user (odbc) ?

hi
inside a stored procedure :
how can i know
which user is logged on
the current odbc session ?
thanks
atte,
Hernn Castelo
SGA - UTN - FRBA"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:eON6U6jzEHA.3804@.TK2MSFTNGP10.phx.gbl...
> hi
> inside a stored procedure :
> how can i know
> which user is logged on
> the current odbc session ?
> thanks
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
Take a look at the CURRENT_USER function in the Books Online.
Rick Sawtell
MCT, MCSD, MCDBA|||To add to Rick's response, you can also use SUSER_SNAME() to get the login
name.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:eON6U6jzEHA.3804@.TK2MSFTNGP10.phx.gbl...
> hi
> inside a stored procedure :
> how can i know
> which user is logged on
> the current odbc session ?
> thanks
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>|||how i did not guess it ?!
(idiomatic matters...)
thanks
atte,
Hernn Castelo
SGA - UTN - FRBA
"Rick Sawtell" <quickening@.msn.com> escribi en el mensaje
news:%23UQodAkzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> "Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
> news:eON6U6jzEHA.3804@.TK2MSFTNGP10.phx.gbl...
>
> Take a look at the CURRENT_USER function in the Books Online.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Sunday, February 19, 2012

how to know logged user (odbc) ?

hi
inside a stored procedure :
how can i know
which user is logged on
the current odbc session ?
thanks
--
atte,
Hernán Castelo
SGA - UTN - FRBA"Hernán Castelo" <bajopalabra@.hotmail.com> wrote in message
news:eON6U6jzEHA.3804@.TK2MSFTNGP10.phx.gbl...
> hi
> inside a stored procedure :
> how can i know
> which user is logged on
> the current odbc session ?
> thanks
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>
Take a look at the CURRENT_USER function in the Books Online.
Rick Sawtell
MCT, MCSD, MCDBA|||To add to Rick's response, you can also use SUSER_SNAME() to get the login
name.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hernán Castelo" <bajopalabra@.hotmail.com> wrote in message
news:eON6U6jzEHA.3804@.TK2MSFTNGP10.phx.gbl...
> hi
> inside a stored procedure :
> how can i know
> which user is logged on
> the current odbc session ?
> thanks
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>|||how i did not guess it ?!
(idiomatic matters...)
thanks
--
atte,
Hernán Castelo
SGA - UTN - FRBA
"Rick Sawtell" <quickening@.msn.com> escribió en el mensaje
news:%23UQodAkzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> "Hernán Castelo" <bajopalabra@.hotmail.com> wrote in message
> news:eON6U6jzEHA.3804@.TK2MSFTNGP10.phx.gbl...
> > hi
> > inside a stored procedure :
> >
> > how can i know
> > which user is logged on
> > the current odbc session ?
> >
> > thanks
> >
> > --
> > atte,
> > Hernán Castelo
> > SGA - UTN - FRBA
> >
> >
>
> Take a look at the CURRENT_USER function in the Books Online.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Friday, February 24, 2012

Sunday, February 19, 2012

Blog Archive

About Me