collapsing address lines (remove blank lines)

Well I've seen several related posts but nothing seems to exactly fit
what I need. I real new to RS so bare with me. What I have is a
stored procedure that returns several elements of an address per row.
For instance, Name1, Name2, Name3, Street1, Street2, CityStateZip.
What I need is to collapse the address to where there are no blanks.
For example:
Name1=Michael, Name2=Dale, Name3 is blank, Street1=ABC St., Street2=is
blank, and CityStateZip = LittleRockArkansas72201
I need the data to look like:
Michael
Dale
ABC St.
LittleRockArkansas72201
and not like:
Michael
Dale
ABC St.
LittleRockArkansas72201
Any help would be greatly appreciated.On Oct 24, 5:34 pm, mlafarl...@.yahoo.com wrote:
> Well I've seen several related posts but nothing seems to exactly fit
> what I need. I real new to RS so bare with me. What I have is a
> stored procedure that returns several elements of an address per row.
> For instance, Name1, Name2, Name3, Street1, Street2, CityStateZip.
> What I need is to collapse the address to where there are no blanks.
> For example:
> Name1=Michael, Name2=Dale, Name3 is blank, Street1=ABC St., Street2=is
> blank, and CityStateZip = LittleRockArkansas72201
> I need the data to look like:
> Michael
> Dale
> ABC St.
> LittleRockArkansas72201
> and not like:
> Michael
> Dale
> ABC St.
> LittleRockArkansas72201
> Any help would be greatly appreciated.
What you will want to do is filter out the empty records via: right-
click the table control >> select Properties >> select the Filters tab
>> below Expression select: Fields!address.value >> below Operator
select != >> below Value enter =Nothing.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Oct 24, 6:34 pm, mlafarl...@.yahoo.com wrote:
> Well I've seen several related posts but nothing seems to exactly fit
> what I need. I real new to RS so bare with me. What I have is a
> stored procedure that returns several elements of an address per row.
> For instance, Name1, Name2, Name3, Street1, Street2, CityStateZip.
> What I need is to collapse the address to where there are no blanks.
> For example:
> Name1=Michael, Name2=Dale, Name3 is blank, Street1=ABC St., Street2=is
> blank, and CityStateZip = LittleRockArkansas72201
> I need the data to look like:
> Michael
> Dale
> ABC St.
> LittleRockArkansas72201
> and not like:
> Michael
> Dale
> ABC St.
> LittleRockArkansas72201
> Any help would be greatly appreciated.
Set the Visibility > Hidden property for each line/row to somthing
like this:
=IIF(Name3 = Nothing, True, False) or =IIF(Street2 = Nothing, True,
False)
Make sure you set the property for the row and not individual cells
otherwise it will not close the white space left by the hidden row.
HTH
toolman|||On Oct 25, 12:50 pm, toolman <t...@.infocision.com> wrote:
> On Oct 24, 6:34 pm, mlafarl...@.yahoo.com wrote:
>
> > Well I've seen several related posts but nothing seems to exactly fit
> > what I need. I real new to RS so bare with me. What I have is a
> > stored procedure that returns several elements of anaddressper row.
> > For instance, Name1, Name2, Name3, Street1, Street2, CityStateZip.
> > What I need is to collapse theaddressto where there are no blanks.
> > For example:
> > Name1=Michael, Name2=Dale, Name3 is blank, Street1=ABC St., Street2=is
> > blank, and CityStateZip = LittleRockArkansas72201
> > I need the data to look like:
> > Michael
> > Dale
> > ABC St.
> > LittleRockArkansas72201
> > and not like:
> > Michael
> > Dale
> > ABC St.
> > LittleRockArkansas72201
> > Any help would be greatly appreciated.
> Set the Visibility > Hidden property for each line/row to somthing
> like this:
> =IIF(Name3 = Nothing, True, False) or =IIF(Street2 = Nothing, True,
> False)
> Make sure you set the property for the row and not individual cells
> otherwise it will not close the white space left by the hidden row.
> HTH
> toolman
When you're recommending these solutions, are you clear that each row
has the address information like:
12345, Bob, Smith, ABC st., , St.Louis MO 43234
45323, Mark, Jones, DEF st, Suite 1, LR AR 72432|||On Oct 26, 10:07 am, mlafarl...@.yahoo.com wrote:
> On Oct 25, 12:50 pm, toolman <t...@.infocision.com> wrote:
>
>
> > On Oct 24, 6:34 pm, mlafarl...@.yahoo.com wrote:
> > > Well I've seen several related posts but nothing seems to exactly fit
> > > what I need. I real new to RS so bare with me. What I have is a
> > > stored procedure that returns several elements of anaddressper row.
> > > For instance, Name1, Name2, Name3, Street1, Street2, CityStateZip.
> > > What I need is to collapse theaddressto where there are no blanks.
> > > For example:
> > > Name1=Michael, Name2=Dale, Name3 is blank, Street1=ABC St., Street2=is
> > > blank, and CityStateZip = LittleRockArkansas72201
> > > I need the data to look like:
> > > Michael
> > > Dale
> > > ABC St.
> > > LittleRockArkansas72201
> > > and not like:
> > > Michael
> > > Dale
> > > ABC St.
> > > LittleRockArkansas72201
> > > Any help would be greatly appreciated.
> > Set the Visibility > Hidden property for each line/row to somthing
> > like this:
> > =IIF(Name3 = Nothing, True, False) or =IIF(Street2 = Nothing, True,
> > False)
> > Make sure you set the property for the row and not individual cells
> > otherwise it will not close the white space left by the hidden row.
> > HTH
> > toolman
> When you're recommending these solutions, are you clear that each row
> has the address information like:
> 12345, Bob, Smith, ABC st., , St.Louis MO 43234
> 45323, Mark, Jones, DEF st, Suite 1, LR AR 72432- Hide quoted text -
> - Show quoted text -
When you're speaking of rows, do you mean rows in your report or rows
returned from your stored proc (your dataset). It looks like you're
meaning the latter.
To get the results you describe in your first post, my suggestion is
to use a table data region in the body of your report. Drag the Table
from the Toolbox into the report body. By default the table starts
with a header, a footer and one detail row each with three columns.
You may or may not want to keep the header and footer rows but you
will definitely want to add as many additional detail rows you need to
allow for all your fields. Then drag your fields from the Datasets
pane into the table.
It should follow this pattern:
--Column1--Column2--Column3
DetailRow1--(Name1 field)
DetailRow2--(Name2 field)
DetailRow3--(Name3 field)
DetailRow4--(House# field)--(Street1 field)
DetailRow5--(Street2 field)
DetailRow6--(CityStateZip field)
Then (to reiterate and elaborate on my earlier reply) right click the
row handle (gray box, left hand side of table, has three lines in it),
select Properties from the menu.
Scroll to and change the Visibility > Hidden property from False to an
Expression like this:
=IIF(Name1 = Nothing, True, False)
Make sure you set the property for the row and not individual cells
otherwise it will not close the white space left by the hidden row.
Repeat for each of the other detail rows.

cold backup

Hi
And lots of thanks to Vishal P for the quick answer. The
sp_attach_db system stored procedure seems to be the
fastest way to go I suppose, but which is the best way to
get a cold backup?
Subject: Re: Disaster recovery
From: "Vishal Parkar" <a@.hotmail.com> Sent: 9/29/2003
6:58:43 AM
>do it. Is a new installation of SQL2000 Server needed on
>the test server, or can we do it by only restoring from
>the backup tapes of the existing SQL2000 Server
>environment?
If you want to restore the existing copy of database on
another server then you have to have SQL Server instance
installed. If one already exists no need to do
reinstallation of SQL instance.
If you possess copies of the data and log files. (ie cold
backup) you can copy these files to the destination server
and simply run sp_attach_db system stored procedure to
recreate the database.
See more help on sp_attach_db in BOL(it contains examples
as well.)
If you have taken backup of the database using "backup
database" command then you will have to restore it
using "restore database" command.
See more help on this in BOL.
- Vishal
>--Original Message--
>Hi
>We are about to install a test invironment identical to
>the existing SQL2000 Server environmnet, running on
>Win2000. The question is however, which is the best way
to
>do it. Is a new installation of SQL2000 Server needed on
>the test server, or can we do it by only restoring from
>the backup tapes of the existing SQL2000 Server
>environment? We backup everything on the existing server
+
>the database files and transaction logs.
>Your prompt answer will be highly appreciated!
>With many thanks,
>Suzy
>
>.
>
.You can detach a database using sp_detach_db. The detached
files remain and can be moved to another server or copied
to another backup device using os command.
If you are using sp_detach_db then other databases will
remain untouched which is not the case when you shutdown
the server completely just for the sake of taking backup
of single database.
- Vishal.

Code review tool for Stored procedures

Hi,
Is there any tool (freeware or licensed) which can automate code reviews for
stored procedures based on various parameters. I am looking out for somethin
g
similar to FXCop for stored procedures on SQL Server 2000.
Afaq ChoonawalaYou can start with SQL BPA
http://www.microsoft.com/downloads/...&displaylang=en
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Afaq" <Afaq@.discussions.microsoft.com> wrote in message
news:36129FBF-13FB-4492-B7F3-C21C75BD2B7A@.microsoft.com...
> Hi,
> Is there any tool (freeware or licensed) which can automate code reviews
> for
> stored procedures based on various parameters. I am looking out for
> something
> similar to FXCop for stored procedures on SQL Server 2000.
> --
> Afaq Choonawala

Code Level for Deadlock Detection

I've got a VB app that calls an SQL stored procedure that calls
another SQL stored procedure that updates multiple tables. I want
deadlock detection and recovery. Where can it be done?
The inherited code attempts to check for error 1205 inside the inner
sql stored procedure - an error code check after every update
statement. Will that work? I thought the deadlock victim would kill
the entire stored procedure, not just the blocked update statement.
If the inherited code is wrong, will it work to put the deadlock test
in the outer stored procedure? Will the deadlock victim kill only the
inner stored procedure and return error code 1205 to the outer stored
procedure? Or does it kill both the inner and outer stored
procedures?The transaction is rolled back and the entire back is aborted when you are
chosen as the deadlock victim
(http://www.sommarskog.se/error-handling-I.html). If you want to retry the
operation, you'll need to handle the error in your VB code.
Hope this helps.
Dan Guzman
SQL Server MVP
"William Lipp" <w.b.(MyLastNameHere)@.ieee.org> wrote in message
news:t6sv32d0vahsjme8olhjc90l7dnqu1a9gj@.
4ax.com...
> I've got a VB app that calls an SQL stored procedure that calls
> another SQL stored procedure that updates multiple tables. I want
> deadlock detection and recovery. Where can it be done?
> The inherited code attempts to check for error 1205 inside the inner
> sql stored procedure - an error code check after every update
> statement. Will that work? I thought the deadlock victim would kill
> the entire stored procedure, not just the blocked update statement.
> If the inherited code is wrong, will it work to put the deadlock test
> in the outer stored procedure? Will the deadlock victim kill only the
> inner stored procedure and return error code 1205 to the outer stored
> procedure? Or does it kill both the inner and outer stored
> procedures?|||Try testing it.
A deadlock will terminate the connection so there's nothing that can be done
in the stored procedures.
Put the retry in the VB app.
I use an interface class to access the database so it's easy to put code
like this in all calls.
see
http://www.nigelrivett.net/VB/VB6DataAccessLayer.html
"William Lipp" wrote:

> I've got a VB app that calls an SQL stored procedure that calls
> another SQL stored procedure that updates multiple tables. I want
> deadlock detection and recovery. Where can it be done?
> The inherited code attempts to check for error 1205 inside the inner
> sql stored procedure - an error code check after every update
> statement. Will that work? I thought the deadlock victim would kill
> the entire stored procedure, not just the blocked update statement.
> If the inherited code is wrong, will it work to put the deadlock test
> in the outer stored procedure? Will the deadlock victim kill only the
> inner stored procedure and return error code 1205 to the outer stored
> procedure? Or does it kill both the inner and outer stored
> procedures?
>|||William Lipp (w.b.(MyLastNameHere)@.ieee.org) writes:
> The inherited code attempts to check for error 1205 inside the inner
> sql stored procedure - an error code check after every update
> statement. Will that work? I thought the deadlock victim would kill
> the entire stored procedure, not just the blocked update statement.
Up to SQL 2000 this is correct.
In SQL 2005 you can use TRY-CATCH to trap deadlock errors in a stored
procedure. Since you say "error check after every update statement",
it does not sound like TRY-CATCH, and thus that code is not really
working.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Sat, 15 Apr 2006 12:23:49 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>William Lipp writes:
> Erland Sommarskog writes
>In SQL 2005 you can use TRY-CATCH to trap deadlock errors in a stored
>procedure. Since you say "error check after every update statement",
>it does not sound like TRY-CATCH, and thus that code is not really
>working.
Yes, the existing code just checks @.@.error after the insert - I was
pretty sure that wasn't working. I had hoped that the outer stored
procedure could that method to detect deadlocks within the inner
stored procedure. I believe these responses say that won't work
either - that a deadlock victim in the inner stored procedure will
also kill the outer stored procedure, and the only place I can detect
the deadlock victim and reattempt is from the VB code that called the
outer procedure. Have I got that right?|||William Lipp (w.b.(MyLastNameHere)@.ieee.org) writes:
> Yes, the existing code just checks @.@.error after the insert - I was
> pretty sure that wasn't working. I had hoped that the outer stored
> procedure could that method to detect deadlocks within the inner
> stored procedure. I believe these responses say that won't work
> either - that a deadlock victim in the inner stored procedure will
> also kill the outer stored procedure, and the only place I can detect
> the deadlock victim and reattempt is from the VB code that called the
> outer procedure. Have I got that right?
Yes, for SQL 2000 your assumption is entirely accurate. (Well, there is a
trick you can play with linked servers, but it's fairly obscure.)
For SQL 2005, as I said, it is possible to catch the error in a stored
procedure with the new TRY-CATCH construct.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Code inside! --> How to return the @@identity parameter without using stored procedures

Hi.
here is my code with my problem described in the syntax.
I am using asp.net 1.1 and VB.NET
Thanks in advance for your help.
I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row.

PublicFunction howToReturnID(ByVal aCompanyAsString,ByVal aNameAsString)AsInteger

'that is the variable for the new id.
Dim intNewIDAsInteger

Dim strSQLAsString ="INSERT INTO tblAnfragen(aCompany, aName)" & _
"VALUES (@.aCompany, @.aName); SELECT @.NewID = @.@.identity"

Dim dbConnectionAs SqlConnection =New SqlConnection(connectionString)
Dim dbCommandAs SqlCommand =New SqlCommand()
dbCommand.CommandText = strSQL

'Here is my problem.
'What do I have to do in order to add the parameter @.NewID and
'how do I read and return the value of @.NewID within that function howToReturnID
'any help is greatly appreciated!
'I cannot use SPs in this application - have to do it this way! :-(

dbCommand.Parameters.Add("@.aFirma", aCompany.Trim)
dbCommand.Parameters.Add("@.aAnsprAnrede", aName.Trim)

dbCommand.Connection = dbConnection

Try
dbConnection.Open()
dbCommand.ExecuteNonQuery()

'here i want to return the new ID!
Return intNewID

Catch exAs Exception

ThrowNew System.Exception("Error: " & ex.Message.ToString())

Finally

dbCommand.Dispose()
dbConnection.Close()
dbConnection.Dispose()

EndTry

EndFunction

Why don't you put your SQL statement something like this;
Insert Into table (col1, col2) Values (1, 2); Select @.@.IDENTITY
And from there, to retrieve the @.@.IDENTITY, you will execute a scalar return. For example:
Dim identity As Integer = Convert.ToInt32(command.ExecuteScalar())
|||

Hi,

thank you very much for your help.
I tried your suggestion but got always two rows inserted.

Obviously the command object executed the insert statement two times?

first here: db.command.ExecuteNonQuery()
and here:Dim identity As Integer = Convert.ToInt32(command.ExecuteScalar())
this is what I did:
Is this correct ? -at least it works ;-) - but is it the "right way" to do it?
PublicFunction howToReturnID(ByVal aCompanyAsString,ByVal aNameAsString)AsInteger

'that is the variable for the new id.
Dim intNewIDAsInteger

Dim strSQLAsString ="INSERT INTO tblAnfragen(aCompany, aName)" & _
"VALUES (@.aCompany, @.aName);"

'I separated the SQL query string
Dim strSQL2AsString ="Select @.@.IDENTITY;"

Dim dbConnectionAs SqlConnection =New SqlConnection(connectionString)
Dim dbCommandAs SqlCommand =New SqlCommand()
dbCommand.CommandText = strSQL

dbCommand.Parameters.Add("@.aFirma", aCompany.Trim)
dbCommand.Parameters.Add("@.aAnsprAnrede", aName.Trim)

dbCommand.Connection = dbConnection

Try
dbConnection.Open()
'execute first query
dbCommand.ExecuteNonQuery()

'execute second query - this actually returns the id of the currently inserted row.
'But is this the CORRECT WAY to do it?? Any objections?
'this solution only inserts one row not two - as it did when the SQL-query was in one string
dbCommand.CommandText = strSQL2
newID = Convert.ToInt32(dbCommand.ExecuteScalar)

Return intNewID

Catch exAs Exception

ThrowNew System.Exception("Error: " & ex.Message.ToString())

Finally

dbCommand.Dispose()
dbConnection.Close()
dbConnection.Dispose()

EndTry

EndFunction

|||Don't call the ExecuteNonQuery() method. Every Execute*() method that you call runs it as a completely new query, if you know what I mean.
Regards,
Justin|||Well, not really
You mean the .ExecuteScalar method of the command object does also run the insert query?
If so why is there a ExecuteNonQuery method at all?
Is the way I did it in my second source code example not recommendable?
But it works for me - or is there a good reason not to do it that way (performance issues, etc.) ?|||Well, with your second code example, you have described what is wrong with it - it inserts the same data twice into the table. So this breaks logics reason. Besides calling the Execute*() twice, I don't see anything else wrong with the second code posting.
The reason why we have the three Execute() methods (ExecuteNonQuery, ExecuteReader, and ExecuteScalar) on the commands are that each one tells the 'executer' (loosely saying it here) on what type of result to expect. With ExecuteNonQuery(), it returns how many rows were affected. With ExecuteScalar() method, you are expect a simple value type to be returned. And with the ExecuteReader(), it returns a data reader...
They all 'execute' but you have to decide on what information you need to retrieve from execution of that command. Am I making sense now?|||Hi,
yes, everything works now the way it is supposed to :)
Thank you very much for your help!
But in my second code example that i have posted it does not insert the row twice ;-)
Just take a closer look at it - I have provided the command object with a second query that only selects @.@.identity .
But I did that "workaround" because I didn't know that executeScalar also "executes" insert queries ...!
My problem is solved now!|||You are right. I did overlook that. Silly me. Anyway, it was a pleasure.

Code Help Please

I did not write this stored procedure but have been asked to edit the code and display new data. The column name is H.BilletUOM. It only contains only three values. B71,B72, and B73. I was able to select the column in the stored procedure easily so the data is available for the report but the next step is where I am having difficulty. If the value is B71 then I want to display 28ft. B72 display 34ft. B73 display 40ft. Can someone please take a look at this code and try to help me figure this out. I highlighted the H.BilletUOM in red so you can quickly see where I am selecting this column. Any assistance is greatly appreciated. I am new to SQL and have not started classes yet. Thanks again.

CREATE PROCEDURE SP_Melt_HeatReport_HeatList
@.HeatNo varchar(50) = NULL,
@.Date varchar(50) = NULL
AS

UPDATE WebPageData
SET Hits = Hits + 1
WHERE SPName = 'SP_Melt_HeatReport_HeatList'

DECLARE @.Site varchar(100) SET @.Site = dbo.UDF_SystemParameter('Site')
DECLARE @.SiteName varchar(100) SET @.SiteName = dbo.UDF_SystemParameter('SiteName')

DECLARE @.MinHeatNo int SET @.MinHeatNo = CSCMelting.dbo.UDF_SystemParameter('MinClevelandHeat')
DECLARE @.MaxHeatNo int SET @.MaxHeatNo = CSCMelting.dbo.UDF_SystemParameter('MaxClevelandHeat')

DECLARE @.HeatsPerPage int SET @.HeatsPerPage = 40

IF @.Date IS NOT NULL
BEGIN
SELECT @.MinHeatNo = MIN(HeatNo)
FROM CSCMelting.dbo.MS_HeatLog
WHERE RptDate = @.Date AND HeatNo BETWEEN @.MinHeatNo AND @.MaxHeatNo

SELECT @.MaxHeatNo = MAX(HeatNo)
FROM CSCMelting.dbo.MS_HeatLog
WHERE RptDate = @.Date AND HeatNo BETWEEN @.MinHeatNo AND @.MaxHeatNo

SELECT @.HeatNo = @.MinHeatNo
SELECT @.HeatsPerPage = (@.MaxHeatNo - @.MinHeatNo) / 10 + 1
END

IF PATINDEX('%,%', @.HeatNo) > 0
SET @.HeatNo = SUBSTRING(@.HeatNo ,PATINDEX('%,%', @.HeatNo) + 1, 100)

DECLARE @.ColorRed varchar(7) SET @.ColorRed = '#FF0000'
DECLARE @.ColorRedDark varchar(7) SET @.ColorRedDark = '#AA0000'
DECLARE @.ColorBlue varchar(7) SET @.ColorBlue = '#0000FF'
DECLARE @.ColorGreen varchar(7) SET @.ColorGreen = '#008800'
DECLARE @.ColorYellow varchar(7) SET @.ColorYellow = '#999900'
DECLARE @.ColorWhite varchar(7) SET @.ColorWhite = '#FFFFFF'
DECLARE @.ColorBlack varchar(7) SET @.ColorBlack = '#000000'
DECLARE @.ColorGray varchar(7) SET @.ColorGray = '#eeeeee'

IF @.HeatNo IS NULL OR @.HeatNo = '' OR ISNUMERIC(@.HeatNo) = 0 OR @.HeatNo = '0'
SELECT @.HeatNo = MAX(HeatNo) - (@.HeatsPerPage * 10) + 10
FROM CSCMelting.dbo.MS_HeatLog
WHERE HeatNo BETWEEN @.MinHeatNo AND @.MaxHeatNo

SELECT Tag = 1, Parent = NULL,
[Title!1!Title1] = 'Charter Steel ' + @.SiteName + ' Melting',
[Title!1!Title2] = 'LEVEL II HEAT REPORTS'
FOR XML EXPLICIT

SELECT Tag = 1, Parent = NULL,
[PageVars!1!Site] = @.Site,
[PageVars!1!WebPageID] = 20500,
[PageVars!1!HeatNo] = @.HeatNo,
[PageVars!1!HeatNoNext] = @.HeatNo + (@.HeatsPerPage * 10) ,
[PageVars!1!HeatNoPrev] = @.HeatNo - (@.HeatsPerPage * 10) ,
[PageVars!1!HeatNoLast] = 0,
[PageVars!1!HomeLink] = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20500'
FOR XML EXPLICIT

SELECT HeatNo,
Job = JOB_NUMBER,
RowColor = CASE WHEN (Heatno /10) % 2 = 0 THEN @.ColorGray ELSE @.ColorWhite END,
Grade = CONVERT(varchar(20), GRADE + ' ' + CS_CMDESC),
Status = CASE Status WHEN 4 THEN
CASE WHEN ChemCert = 1 THEN 'Certified'
WHEN EXISTS(SELECT * FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo AND Location = '99') THEN 'Inspection'
WHEN EXISTS (SELECT * FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo) THEN 'CCM'
ELSE 'Inspected'
END
WHEN 3 THEN 'CCM'
WHEN 2 THEN 'LRF'
WHEN 1 THEN 'EAF'
ELSE ''
END, --+ CASE WHEN EXISTS (SELECT * FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo) THEN ' (PB)' ELSE '' END,
H.NonConforming,
PourBackTons = (SELECT MIN(PourBackAmt) / 2000 FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo),
Location = (SELECT MAX(Location) FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo),
CntCounted = H.BilletsMade1 + H.BilletsMade2 + H.BilletsMade3 + H.BilletsMade4,
ScrapCount = (SELECT ScrapCount FROM CSCMelting.dbo.VW_Prod_ScrapBilletsByHeat Sc WHERE Sc.Heatno = H.HeatNo),
CntInv = (SELECT COUNT(*) FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo),
CntRolled = (SELECT SUM(Quantity) FROM CSCRolling.dbo._Lot WHERE Heat = H.HeatNo),
CntSched = (SELECT SUM(IC_QUANTITY) FROM CSCRolling.dbo._Lot_Scheduled WHERE IC_LOT_NUMBER = H.HeatNo),

CCM.SEQ_COUNTER,
SeqHeat = (SELECT COUNT(*) FROM CSCMelting.dbo.REP_CCM CCM2 WHERE CCM2.SEQ_COUNTER = CCM.SEQ_COUNTER AND CCM2.REPORT_COUNTER <= R.REPORT_COUNTER), --CCM.SEQ_HEAT_COUNTER,
SeqTotal = (SELECT COUNT(*) FROM CSCMelting.dbo.REP_CCM CCM2 WHERE CCM2.SEQ_COUNTER = CCM.SEQ_COUNTER),

RptDate = CONVERT(varchar(10), RptDate, 120),
EndTapTime = H.EndTapTime,
H.BilletUOM,

ConsumptionsLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20501&HeatNo=' + CONVERT(varchar(20), HeatNo),
FCERepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 400),0),
FCERepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20100&HeatNo=' + CONVERT(varchar(20), HeatNo),
-- VADRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 600),0),
-- VADRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),

LRFRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 600),0),
LRFRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),

VODRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 800),0),
VODRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),

CCMRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 1100),0),
CCMRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20300&HeatNo=' + CONVERT(varchar(20), HeatNo),
RunoutOrderLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20301&HeatNo=' + CONVERT(varchar(20), HeatNo)

INTO #Heats
FROM CSCMelting.dbo.MS_HeatLog H
LEFT JOIN CSCMelting.dbo.REPORTS R ON R.HEAT_ID = H.HeatNo AND R.AREA_ID = 1100
LEFT JOIN CSCMelting.dbo.REP_CCM CCM ON CCM.REPORT_COUNTER = R.REPORT_COUNTER
WHERE HeatNo >= @.HeatNo AND HeatNo < @.HeatNo + (@.HeatsPerPage * 10) AND HeatNo % 10 = 0

SELECT *,
EndTapTimeStr = CONVERT(varchar(25), EndTapTime, 100),
MissChemStr = CASE WHEN NonConforming = 1 THEN 'Y' ELSE '' END,
PourBackTonsStr = CASE WHEN PourBackTons = 0 THEN '' ELSE CONVERT(varchar(10), PourBackTons) + 'tn' END,
CntCountedStr = NULLIF(CntCounted, 0),
ScrapCountStr = NULLIF(ScrapCount, 0),
CntInvStr = NULLIF(CntInv, 0),
CntRolledStr = NULLIF(CntRolled, 0),
CntSchedStr = NULLIF(CntSched, 0),
HeatSeqStr = '' + char(CONVERT(int, SEQ_COUNTER) % 26 + 65) + ' - ' + CASE WHEN SeqTotal <= 1 THEN 'single' ELSE CONVERT(varchar(2), SeqHeat) + ' of ' + CONVERT(varchar(2), SeqTotal) END,
StatusColor = CASE
WHEN PourBackTons > 0 THEN @.ColorBlack
WHEN Status = 'EAF' THEN @.ColorBlue
WHEN Status = 'LRF' THEN @.ColorGreen
WHEN Status = 'CCM' THEN @.ColorYellow
WHEN Status = 'Inspection' THEN @.ColorRedDark
WHEN Status = 'Inspected' THEN @.ColorRed
WHEN Status = 'Certified' THEN @.ColorBlack
ELSE ''
END
FROM #Heats Heats
ORDER BY HeatNo
FOR XML AUTO

print @.HeatNo + (@.HeatsPerPage * 10) + 10

DROP TABLE #Heats
GO

Look at the "status" field above your billetUOM field. It's using a case statement. You'll want to perform similar stuff against your billetUOM field.

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

I came up with this code and it still is still giving me syntax errors. Could you please take a look and see what I am doing wrong? Thank you very much for any help.

H.BilletUOM = CASE H.BilletUOM WHEN B71 THEN '28ft.'
WHEN B72 THEN '34ft.'
WHEN B73 THEN '40ft.'
ELSE ''
END,

|||I think you need to put your criteria in quotes as well.

WHEN 'B71' THEN...
WHEN 'B72' THEN...
etc..

code getting progressively slower within a transaction

Hi guys,
I've got a stored proc running in a transaction, it does a lot of
complicated processing in terms of selecting from just about every
table in the db, and making a variety of updates. I can't post the DDL
or code, but hopefully it will suffice to say that it selects, updates,
deletes and inserts into lots of tables, and all runs wrapped up within
just 1 transaction (there are no nested transactions - at least not
explicit ones).
The code loops through with a cursor, running another stored procedure
to actually then process the row.
Each iteration takes longer and longer, but if we take it out of the
outer transaction then it's fine. What might be building up that could
cause this to happen? I assume it's not locking as this would cause
things to just deadlock rather than slow wouldn't it? My only guess is
that it's not able to clear something in the transaction log, and it is
having to grow the file which is taking time, but that would not get
progressively worse.
Cheers
WillI forgot to say, this job runs during maintenance, so there are no
other processes to lock with or compete with|||Will
u might be having a small transaction log file with an autogrowth by
a few percent. The process might get slowed if the transaction log grows
frequenlty. try to increase the size of the transaction log file and run the
proc. There might also be a problem with the tempdb overuse. Try to put the
tempdb and the transaction log file in seperate disks if u can. hope this
helps|||Autogrow will take longer each time if you have a % defined. The key is to
always ensure there is enough free space before you start the process. What
kind of cursors are you using? Try to declare them as Static and see if
that helps.
Andrew J. Kelly SQL MVP
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144830633.350622.218100@.z34g2000cwc.googlegroups.com...
> Hi guys,
> I've got a stored proc running in a transaction, it does a lot of
> complicated processing in terms of selecting from just about every
> table in the db, and making a variety of updates. I can't post the DDL
> or code, but hopefully it will suffice to say that it selects, updates,
> deletes and inserts into lots of tables, and all runs wrapped up within
> just 1 transaction (there are no nested transactions - at least not
> explicit ones).
> The code loops through with a cursor, running another stored procedure
> to actually then process the row.
> Each iteration takes longer and longer, but if we take it out of the
> outer transaction then it's fine. What might be building up that could
> cause this to happen? I assume it's not locking as this would cause
> things to just deadlock rather than slow wouldn't it? My only guess is
> that it's not able to clear something in the transaction log, and it is
> having to grow the file which is taking time, but that would not get
> progressively worse.
> Cheers
> Will
>

Code for Scripts

I'm looking for code that will create a script of a stored procedure vs.
going through the gui. Can anyone help?
See the below link:
http://www.karaszi.com/sqlserver/inf...ate_script.asp
Thanks
Hari
SQL Server MVP
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:69FCF02E-E868-4A1C-AB28-36562C8B5571@.microsoft.com...
> I'm looking for code that will create a script of a stored procedure vs.
> going through the gui. Can anyone help?

Code coverage tools

Do code coverage tools exist for SQL. I am not talking about really
complicated one. Just perhaps a tool that will scan a stored proc, then
mark/remove unused variables, for instance.
RegardsI'm not aware of any. It would be interesting, but you'd have to first
define what you mean by code coverage. To me, it is a testing algorithm
which determins what percentage of the possible code paths have been tested.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:%23HJp0HpJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Do code coverage tools exist for SQL. I am not talking about really
> complicated one. Just perhaps a tool that will scan a stored proc, then
> mark/remove unused variables, for instance.
> Regards|||Michael Hotek wrote:[vbcol=seagreen]
> I'm not aware of any. It would be interesting, but you'd have to first
> define what you mean by code coverage. To me, it is a testing algorithm
> which determins what percentage of the possible code paths have been tested.[/vbco
l]
I'd settle for dead code coverage. Anyway, sounds like a weekend project.|||It's something that is nice in theory, but again, it comes down to how you
define it. Finding unused variables is pretty straightforward. How do you
figure out whether a code path can be taken, no so trivial? How do you
figure out if a piece of code within a stored procedure is executed, again,
not so trivial. You'd need a much better definition of what you mean by
"code coverage" (a better term would be code validity) before trying to
create something.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:OUp9F%230JGHA.2248@.TK2MSFTNGP15.phx.gbl...
> Michael Hotek wrote:
> I'd settle for dead code coverage. Anyway, sounds like a weekend project.

Code coverage tools

Do code coverage tools exist for SQL. I am not talking about really
complicated one. Just perhaps a tool that will scan a stored proc, then
mark/remove unused variables, for instance.
RegardsI'm not aware of any. It would be interesting, but you'd have to first
define what you mean by code coverage. To me, it is a testing algorithm
which determins what percentage of the possible code paths have been tested.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:%23HJp0HpJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Do code coverage tools exist for SQL. I am not talking about really
> complicated one. Just perhaps a tool that will scan a stored proc, then
> mark/remove unused variables, for instance.
> Regards|||Michael Hotek wrote:
> I'm not aware of any. It would be interesting, but you'd have to first
> define what you mean by code coverage. To me, it is a testing algorithm
> which determins what percentage of the possible code paths have been tested.
I'd settle for dead code coverage. Anyway, sounds like a weekend project.|||It's something that is nice in theory, but again, it comes down to how you
define it. Finding unused variables is pretty straightforward. How do you
figure out whether a code path can be taken, no so trivial? How do you
figure out if a piece of code within a stored procedure is executed, again,
not so trivial. You'd need a much better definition of what you mean by
"code coverage" (a better term would be code validity) before trying to
create something.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:OUp9F%230JGHA.2248@.TK2MSFTNGP15.phx.gbl...
> Michael Hotek wrote:
>> I'm not aware of any. It would be interesting, but you'd have to first
>> define what you mean by code coverage. To me, it is a testing algorithm
>> which determins what percentage of the possible code paths have been
>> tested.
> I'd settle for dead code coverage. Anyway, sounds like a weekend project.

Code coverage tools

Do code coverage tools exist for SQL. I am not talking about really
complicated one. Just perhaps a tool that will scan a stored proc, then
mark/remove unused variables, for instance.
Regards
I'm not aware of any. It would be interesting, but you'd have to first
define what you mean by code coverage. To me, it is a testing algorithm
which determins what percentage of the possible code paths have been tested.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:%23HJp0HpJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Do code coverage tools exist for SQL. I am not talking about really
> complicated one. Just perhaps a tool that will scan a stored proc, then
> mark/remove unused variables, for instance.
> Regards
|||Michael Hotek wrote:
> I'm not aware of any. It would be interesting, but you'd have to first
> define what you mean by code coverage. To me, it is a testing algorithm
> which determins what percentage of the possible code paths have been tested.
I'd settle for dead code coverage. Anyway, sounds like a weekend project.
|||It's something that is nice in theory, but again, it comes down to how you
define it. Finding unused variables is pretty straightforward. How do you
figure out whether a code path can be taken, no so trivial? How do you
figure out if a piece of code within a stored procedure is executed, again,
not so trivial. You'd need a much better definition of what you mean by
"code coverage" (a better term would be code validity) before trying to
create something.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:OUp9F%230JGHA.2248@.TK2MSFTNGP15.phx.gbl...
> Michael Hotek wrote:
> I'd settle for dead code coverage. Anyway, sounds like a weekend project.

Code Coverage Tool

I want to analyze what statemetns were executed in stored procedures,
for example.
I'm trying hard to find tools for sql code coverage while there are
tons of such tools for Java or C#.
I was able to find only : http://www.sqlpower.com/dsa.html but I want
to compare it with other solutions.Andrei
Have you tried using SQL Server Profiler?
<andrei_tapt@.mail.ru> wrote in message
news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
>I want to analyze what statemetns were executed in stored procedures,
> for example.
> I'm trying hard to find tools for sql code coverage while there are
> tons of such tools for Java or C#.
> I was able to find only : http://www.sqlpower.com/dsa.html but I want
> to compare it with other solutions.
>|||Yes, I use it as performance analyzer. I know that I can save trace in
database but it doesn't provide me any info about what sql statements
were executed ... or I don't know how to get it.
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> Andrei
> Have you tried using SQL Server Profiler?
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> >I want to analyze what statemetns were executed in stored procedures,
> > for example.
> > I'm trying hard to find tools for sql code coverage while there are
> > tons of such tools for Java or C#.
> > I was able to find only : http://www.sqlpower.com/dsa.html but I want
> > to compare it with other solutions.
> >|||<andrei_tapt@.mail.ru> wrote in message
news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
?me any info about what sql statements
>were executed ... or I don't know how to get it.
Why> There are some events to provide it. Under TSQL events select
SQL:StmtCompleted
And see in the TEXT column the resuts
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
<andrei_tapt@.mail.ru> wrote in message
news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
Yes, I use it as performance analyzer. I know that I can save trace in
database but it doesn't provide me any info about what sql statements
were executed ... or I don't know how to get it.
Uri Dimant '?(?):
> Andrei
> Have you tried using SQL Server Profiler?
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> >I want to analyze what statemetns were executed in stored procedures,
> > for example.
> > I'm trying hard to find tools for sql code coverage while there are
> > tons of such tools for Java or C#.
> > I was able to find only : http://www.sqlpower.com/dsa.html but I want
> > to compare it with other solutions.
> >|||AFAIK, I will see full text of stored procedure in TEXT column, like
using sp_helptext with stored procedure name, but I want to know which
statements were executed in this stored procedure.
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> ?me any info about what sql statements
> >were executed ... or I don't know how to get it.
> Why> There are some events to provide it. Under TSQL events select
> SQL:StmtCompleted
> And see in the TEXT column the resuts
> http://www.sql-server-performance.com/sql_server_profiler_tips.asp
>
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> Yes, I use it as performance analyzer. I know that I can save trace in
> database but it doesn't provide me any info about what sql statements
> were executed ... or I don't know how to get it.
> Uri Dimant '?(?):
> > Andrei
> > Have you tried using SQL Server Profiler?
> >
> >
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> > >I want to analyze what statemetns were executed in stored procedures,
> > > for example.
> > > I'm trying hard to find tools for sql code coverage while there are
> > > tons of such tools for Java or C#.
> > > I was able to find only : http://www.sqlpower.com/dsa.html but I want
> > > to compare it with other solutions.
> > >|||Andrei, ty chital chto ya tebe napisal? Link etot chital?
<andrei_tapt@.mail.ru> wrote in message
news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
AFAIK, I will see full text of stored procedure in TEXT column, like
using sp_helptext with stored procedure name, but I want to know which
statements were executed in this stored procedure.
Uri Dimant '?(?):
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> ?me any info about what sql statements
> >were executed ... or I don't know how to get it.
> Why> There are some events to provide it. Under TSQL events select
> SQL:StmtCompleted
> And see in the TEXT column the resuts
> http://www.sql-server-performance.com/sql_server_profiler_tips.asp
>
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> Yes, I use it as performance analyzer. I know that I can save trace in
> database but it doesn't provide me any info about what sql statements
> were executed ... or I don't know how to get it.
> Uri Dimant '?(?):
> > Andrei
> > Have you tried using SQL Server Profiler?
> >
> >
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> > >I want to analyze what statemetns were executed in stored procedures,
> > > for example.
> > > I'm trying hard to find tools for sql code coverage while there are
> > > tons of such tools for Java or C#.
> > > I was able to find only : http://www.sqlpower.com/dsa.html but I want
> > > to compare it with other solutions.
> > >|||Spasibo, kak raz to chto nado:) Povozitsya pridetsya, no lucshe chem
nichego. Ran'she etu stat'u videl, no bystro probezhalsya b vnimania ne
obratil. Spasibo
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> Andrei, ty chital chto ya tebe napisal? Link etot chital?
> <andrei_tapt@.mail.ru> wrote in message
> news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
> AFAIK, I will see full text of stored procedure in TEXT column, like
> using sp_helptext with stored procedure name, but I want to know which
> statements were executed in this stored procedure.
> Uri Dimant '?(?):
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> > ?me any info about what sql statements
> > >were executed ... or I don't know how to get it.
> >
> > Why> There are some events to provide it. Under TSQL events select
> > SQL:StmtCompleted
> > And see in the TEXT column the resuts
> >
> > http://www.sql-server-performance.com/sql_server_profiler_tips.asp
> >
> >
> >
> >
> >
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> > Yes, I use it as performance analyzer. I know that I can save trace in
> > database but it doesn't provide me any info about what sql statements
> > were executed ... or I don't know how to get it.
> >
> > Uri Dimant '?(?):
> >
> > > Andrei
> > > Have you tried using SQL Server Profiler?
> > >
> > >
> > > <andrei_tapt@.mail.ru> wrote in message
> > > news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> > > >I want to analyze what statemetns were executed in stored procedures,
> > > > for example.
> > > > I'm trying hard to find tools for sql code coverage while there are
> > > > tons of such tools for Java or C#.
> > > > I was able to find only : http://www.sqlpower.com/dsa.html but I wa=nt
> > > > to compare it with other solutions.
> > > >|||Ok, ydachi, obrashaysya esli est' problemy
<andrei_tapt@.mail.ru> wrote in message
news:1155122187.034692.167400@.n13g2000cwa.googlegroups.com...
Spasibo, kak raz to chto nado:) Povozitsya pridetsya, no lucshe chem
nichego. Ran'she etu stat'u videl, no bystro probezhalsya b vnimania ne
obratil. Spasibo
Uri Dimant '?(?):
> Andrei, ty chital chto ya tebe napisal? Link etot chital?
> <andrei_tapt@.mail.ru> wrote in message
> news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
> AFAIK, I will see full text of stored procedure in TEXT column, like
> using sp_helptext with stored procedure name, but I want to know which
> statements were executed in this stored procedure.
> Uri Dimant '?(?):
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> > ?me any info about what sql statements
> > >were executed ... or I don't know how to get it.
> >
> > Why> There are some events to provide it. Under TSQL events select
> > SQL:StmtCompleted
> > And see in the TEXT column the resuts
> >
> > http://www.sql-server-performance.com/sql_server_profiler_tips.asp
> >
> >
> >
> >
> >
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> > Yes, I use it as performance analyzer. I know that I can save trace in
> > database but it doesn't provide me any info about what sql statements
> > were executed ... or I don't know how to get it.
> >
> > Uri Dimant '?(?):
> >
> > > Andrei
> > > Have you tried using SQL Server Profiler?
> > >
> > >
> > > <andrei_tapt@.mail.ru> wrote in message
> > > news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> > > >I want to analyze what statemetns were executed in stored procedures,
> > > > for example.
> > > > I'm trying hard to find tools for sql code coverage while there are
> > > > tons of such tools for Java or C#.
> > > > I was able to find only : http://www.sqlpower.com/dsa.html but I
> > > > want
> > > > to compare it with other solutions.
> > > >|||I did not know there were ANY code coverage tools for SQL Server. Now
I know there is at least one - thanks!
Roy
On 9 Aug 2006 00:43:24 -0700, andrei_tapt@.mail.ru wrote:
>I want to analyze what statemetns were executed in stored procedures,
>for example.
>I'm trying hard to find tools for sql code coverage while there are
>tons of such tools for Java or C#.
>I was able to find only : http://www.sqlpower.com/dsa.html but I want
>to compare it with other solutions.

Code Coverage Tool

I want to analyze what statemetns were executed in stored procedures,
for example.
I'm trying hard to find tools for sql code coverage while there are
tons of such tools for Java or C#.
I was able to find only : http://www.sqlpower.com/dsa.html but I want
to compare it with other solutions.Andrei
Have you tried using SQL Server Profiler?
<andrei_tapt@.mail.ru> wrote in message
news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
>I want to analyze what statemetns were executed in stored procedures,
> for example.
> I'm trying hard to find tools for sql code coverage while there are
> tons of such tools for Java or C#.
> I was able to find only : http://www.sqlpower.com/dsa.html but I want
> to compare it with other solutions.
>|||Yes, I use it as performance analyzer. I know that I can save trace in
database but it doesn't provide me any info about what sql statements
were executed ... or I don't know how to get it.
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
[vbcol=seagreen]
> Andrei
> Have you tried using SQL Server Profiler?
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...|||<andrei_tapt@.mail.ru> wrote in message
news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
?me any info about what sql statements
>were executed ... or I don't know how to get it.
Why> There are some events to provide it. Under TSQL events select
SQL:StmtCompleted
And see in the TEXT column the resuts
http://www.sql-server-performance.c...ofiler_tips.asp
<andrei_tapt@.mail.ru> wrote in message
news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
Yes, I use it as performance analyzer. I know that I can save trace in
database but it doesn't provide me any info about what sql statements
were executed ... or I don't know how to get it.
Uri Dimant '?(?):
[vbcol=seagreen]
> Andrei
> Have you tried using SQL Server Profiler?
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...|||AFAIK, I will see full text of stored procedure in TEXT column, like
using sp_helptext with stored procedure name, but I want to know which
statements were executed in this stored procedure.
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
[vbcol=seagreen]
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> ?me any info about what sql statements
> Why> There are some events to provide it. Under TSQL events select
> SQL:StmtCompleted
> And see in the TEXT column the resuts
> http://www.sql-server-performance.c...ofiler_tips.asp
>
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> Yes, I use it as performance analyzer. I know that I can save trace in
> database but it doesn't provide me any info about what sql statements
> were executed ... or I don't know how to get it.
> Uri Dimant '?(?):
>|||Andrei, ty chital chto ya tebe napisal? Link etot chital?
<andrei_tapt@.mail.ru> wrote in message
news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
AFAIK, I will see full text of stored procedure in TEXT column, like
using sp_helptext with stored procedure name, but I want to know which
statements were executed in this stored procedure.
Uri Dimant '?(?):
[vbcol=seagreen]
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> ?me any info about what sql statements
> Why> There are some events to provide it. Under TSQL events select
> SQL:StmtCompleted
> And see in the TEXT column the resuts
> http://www.sql-server-performance.c...ofiler_tips.asp
>
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> Yes, I use it as performance analyzer. I know that I can save trace in
> database but it doesn't provide me any info about what sql statements
> were executed ... or I don't know how to get it.
> Uri Dimant '?(?):
>|||Spasibo, kak raz to chto nado Povozitsya pridetsya, no lucshe chem
nichego. Ran'she etu stat'u videl, no bystro probezhalsya b vnimania ne
obratil. Spasibo
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
[vbcol=seagreen]
> Andrei, ty chital chto ya tebe napisal? Link etot chital?
> <andrei_tapt@.mail.ru> wrote in message
> news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
> AFAIK, I will see full text of stored procedure in TEXT column, like
> using sp_helptext with stored procedure name, but I want to know which
> statements were executed in this stored procedure.
> Uri Dimant '?(?):
>
nt[vbcol=seagreen]|||Ok, ydachi, obrashaysya esli est' problemy
<andrei_tapt@.mail.ru> wrote in message
news:1155122187.034692.167400@.n13g2000cwa.googlegroups.com...
Spasibo, kak raz to chto nado Povozitsya pridetsya, no lucshe chem
nichego. Ran'she etu stat'u videl, no bystro probezhalsya b vnimania ne
obratil. Spasibo
Uri Dimant '?(?):
[vbcol=seagreen]
> Andrei, ty chital chto ya tebe napisal? Link etot chital?
> <andrei_tapt@.mail.ru> wrote in message
> news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
> AFAIK, I will see full text of stored procedure in TEXT column, like
> using sp_helptext with stored procedure name, but I want to know which
> statements were executed in this stored procedure.
> Uri Dimant '?(?):
>|||I did not know there were ANY code coverage tools for SQL Server. Now
I know there is at least one - thanks!
Roy
On 9 Aug 2006 00:43:24 -0700, andrei_tapt@.mail.ru wrote:

>I want to analyze what statemetns were executed in stored procedures,
>for example.
>I'm trying hard to find tools for sql code coverage while there are
>tons of such tools for Java or C#.
>I was able to find only : http://www.sqlpower.com/dsa.html but I want
>to compare it with other solutions.

CmdExec issue

Hello,
I have a batch file in the operating system, which runs an osql command that
create a stored procedure, a view and a function on a database. It is with
-E trust connect. It works when I just run it on windows.
However, if I run in as a SQL Job by using the CmdExec, it could not create
any stored procedure, view or function. And there is no error return
neither. The account to run the job is the same NT account with local admin
rights on the server.
Does anyone have some idea about this matter?
Many Thanks,
LianneAre you running the Job from a different server?
If so you need to use -U and -P providing a username and password. I
have experienced this problem before, but apparently linked servers do
not pass the user via windows authentication.|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I have a batch file in the operating system, which runs an osql command
> that create a stored procedure, a view and a function on a database. It
> is with -E trust connect. It works when I just run it on windows.
> However, if I run in as a SQL Job by using the CmdExec, it could not
> create any stored procedure, view or function. And there is no error
> return neither. The account to run the job is the same NT account with
> local admin rights on the server.
Did the job fail or succeed?
In case it failed, there is a checkbox in the upper right of the View
History dialog which says "Show job steps" or somesuch. The output from
the job step should have the real error.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The job was running on the physical server. and the job return as
successful. but nothing was created on SQL Server side.
Thanks again,
Lianne
"Johnny D" wrote:

> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Hi John,
I change the -E to -U sa login, but I got this error message:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server
connection.
Any idea?
Many Thanks,
Lianne
"Johnny D" wrote:

> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:605603
Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I change the -E to -U sa login, but I got this error message:
> Login failed for user 'sa'. Reason: Not associated with a trusted SQL
> Server connection.
> Any idea?
That would indicate that SQL authentication is not enabled on the server.
Well, changing the autentication should not matter, so consider that a
dead end.
As for why your job does not work as expected, I'm afraid that with the
information you have provided, all we can offer is wild guesses. What
is the exact command line you are using?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Eriand,
the command line is below:
for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U sa -P
zhongshan -n -d test -i "%%f" >> Createcp.txt
this is the command I put in .bat file. on the same level, I have three sql
files, which creates a view, a user defined function and a stored procedure.
Also, I had set SQL Server running on both (SQL and NT authentication)
If you meed more informations, let me know.
Many Thanks,
Lianne
"Erland Sommarskog" wrote:

> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> That would indicate that SQL authentication is not enabled on the server.
> Well, changing the autentication should not matter, so consider that a
> dead end.
> As for why your job does not work as expected, I'm afraid that with the
> information you have provided, all we can offer is wild guesses. What
> is the exact command line you are using?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> the command line is below:
> for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U
> sa -P zhongshan -n -d test -i "%%f" >> Createcp.txt
> this is the command I put in .bat file. on the same level, I have three
> sql files, which creates a view, a user defined function and a stored
> procedure.
Real stupid question: the BAT file does include a CD to the directory
where you have the SQL, doesn't it?
I composed this file on my machine:
F:
cd \moje dokumenty\WWW\dynsearch
for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale -i "
%%f" >> Createcp.txt
And the I created a job of which the command text was
C:\temp\test.bat
I deleted all stored procedure in Northgale, and then started the job.
When I had completed the job, all my procedures were back.
In job history I saw this for the job step:
Executed as user: KESMETS\sql. ...search
F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Createcp.tx
t & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.txt &
.. Process Exit Code 0. The step succeeded.
A way to get more output from the job step is to go the Advanced tab
for the job step, and there select a output file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thank you very much for your suggection. I did not enclude the Cd line in
the .bat file since it work just fine when I run it through windows. My bad
.
I guess SQL is very piggy on directory specification then.
Thank you again. I appreciate your help very much!
Lianne
"Erland Sommarskog" wrote:

> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> Real stupid question: the BAT file does include a CD to the directory
> where you have the SQL, doesn't it?
> I composed this file on my machine:
> F:
> cd \moje dokumenty\WWW\dynsearch
> for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale
-i "%%f" >> Createcp.txt
> And the I created a job of which the command text was
> C:\temp\test.bat
> I deleted all stored procedure in Northgale, and then started the job.
> When I had completed the job, all my procedures were back.
> In job history I saw this for the job step:
>
> Executed as user: KES?METS?\sql. ...search
> F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Create
cp.txt & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.t
xt &... Process Exit Code 0. The step succeeded.
> A way to get more output from the job step is to go the Advanced tab
> for the job step, and there select a output file.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

cmd.Parameters question?

I have several parameters that are passed to a stored procedure. One of them is a website URL. How can I strip out the http:// if it exists from the value before it is passed to the SP? Can the removal be handled in the cmd. statement in the code behind?

SqlConnection con3 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString3"].ConnectionString);
SqlCommand cmd3 = new SqlCommand("AdminStoresStudiosLinkSubmit", con3);
cmd3.CommandType = CommandType.StoredProcedure;
cmd3.Parameters.AddWithValue("@.SL_Name", L_Name);
cmd3.Parameters.AddWithValue("@.L_URL", L_URL);

L_URL =http://www.homepage.com and want onlywww.homepage.com to be passed into database.

Thank you,

Use the Replace command e.g. L_URL.Replace("http://", "")

|||

How would that be embedded in -cmd3.Parameters.AddWithValue("@.SL_URL", SL_URL);

thank you

|||

Below is how I initially assign the parameter value. How would I Replace the http:// at this stage?

string SL_URL = tbx_SL_URL.Text;

|||I see it now - different from classic asp -

string L_URL = tbxL_URL.Text;

L_URL = L_URL.Replace("http://","");

thank you

|||

Consider that a URL may begin with other schemes. So, is it good enough to just replacehttp:// or do you needhttps:// andftp:// andssh:// etc... ? If so, you'll want to remove*:// which could be done with well crafted regular expression (in your favorite .Net language of choice).

CM for stored procedures

What methods are people using out there to keep their stored procedures
under CM control? We are using CVS for our CM system. Looking for an easy
way to get stored procedures scripted out as text so we can check in/check
out of a CM system.

Thanks!

-- Mike BrownMike Brown (mbrownATautotecDOTcom) writes:
> What methods are people using out there to keep their stored procedures
> under CM control? We are using CVS for our CM system. Looking for an
> easy way to get stored procedures scripted out as text so we can check
> in/check out of a CM system.

It appears that the most commonly used version-control system is
Microsoft's own Visual SourceSafe. The prime reason for this is
probably because it's easily available. It does not have the name
for being the best tool in the game. (But it's OK for smaller shops.)

Anyway, you can use Query Analyzer or Enterprise Manger to script out
your SQL objects, but this something you should only do once. Once you
have your SQL code i CVS, CVS should be your source, nothing else. If
you need to change a procedure, you check it out and open it in Query
Analyzer. Or even better you use a 3rd party text editor that permits
you to invoke a command-line tool from the editor. That command-line
tool would be ISQL or OSQL. The point with this is that since the
file hits the disk before it hits the database, you have greater
certainty that what you check in is what's the database. (If you use
QA, you can forget to save after that last fix.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

clustered indexes

Hello,
Does anyone know if a clustered index location dictates
where the actual table data is stored? Can the clustered
index be located on a data file other than the one the
table resides on?
Thanks,
DianeYes it does dictate where the data is stored.
Moving the clustered index to a different FileGroup will move the data
--
HTH
Ryan Waight, MCDBA, MCSE
"Diane" <dfleming@.webmd.com> wrote in message
news:07ec01c3a794$ef088fe0$a501280a@.phx.gbl...
> Hello,
> Does anyone know if a clustered index location dictates
> where the actual table data is stored? Can the clustered
> index be located on a data file other than the one the
> table resides on?
> Thanks,
> Diane|||> Can the clustered
> index be located on a data file other than the one the
> table resides on?
No, essentially, the clustered index IS the data. If you move the clustered
index to a different filegroup, the data will also be on that filegroup.|||Hi,
This might explain a bit further.
Creating a clustered index dictates how the data is to be
stored, and affects behind the scenes pages.
Its sort of like having a manual filing system sorted
alphabetically in a filing cabinet. However it will always
be in order.
It works by ordering the pages in you datafile to come in
a spcific order. So the guys were right, just a bit brief.
If you want further info my email (in reverse) is
ku.oc.elcannip@.nalon.retep
Peter
>--Original Message--
>Hello,
>Does anyone know if a clustered index location dictates
>where the actual table data is stored? Can the clustered
>index be located on a data file other than the one the
>table resides on?
>Thanks,
>Diane
>.
>|||Ever noticed that you can never critique your own work.
Apoligies if it sounded condecending, it wasn't suppost to.
Peter
>--Original Message--
>Hi,
>This might explain a bit further.
>Creating a clustered index dictates how the data is to be
>stored, and affects behind the scenes pages.
>Its sort of like having a manual filing system sorted
>alphabetically in a filing cabinet. However it will
always
>be in order.
>It works by ordering the pages in you datafile to come in
>a spcific order. So the guys were right, just a bit brief.
>If you want further info my email (in reverse) is
>ku.oc.elcannip@.nalon.retep
>Peter
>
>>--Original Message--
>>Hello,
>>Does anyone know if a clustered index location dictates
>>where the actual table data is stored? Can the
clustered
>>index be located on a data file other than the one the
>>table resides on?
>>Thanks,
>>Diane
>>.
>.
>

Clustered Index Question

sql2k sp3
If my Clustered Index is an Identity Coulmn, should
Inserts still be slowed down. In other words, since a
newly Inserted row should be stored at the end, why would
it be any slower than when theres no Clustered Index? It
shouldnt need to reorganize anything. Aslo, should
Updates be any slower since you cant Update an Identity
field anyways?
TIA, ChrisR
Actually it should be slightly faster than if it were just a heap. A heap
has to do some lookups to determine where to place the next row that have a
slight bit of overhead. A CI insert with a monotonically incrementing value
is a no brainer per say for SQL Server. As you stated it goes to the end of
the last page.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
> sql2k sp3
> If my Clustered Index is an Identity Coulmn, should
> Inserts still be slowed down. In other words, since a
> newly Inserted row should be stored at the end, why would
> it be any slower than when theres no Clustered Index? It
> shouldnt need to reorganize anything. Aslo, should
> Updates be any slower since you cant Update an Identity
> field anyways?
> TIA, ChrisR
|||Would Updates be the same?

>--Original Message--
>Actually it should be slightly faster than if it were
just a heap. A heap
>has to do some lookups to determine where to place the
next row that have a
>slight bit of overhead. A CI insert with a
monotonically incrementing value
>is a no brainer per say for SQL Server. As you stated
it goes to the end of
>the last page.
>--
>Andrew J. Kelly SQL MVP
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
would[vbcol=seagreen]
It
>
>.
>
|||It depends on which columns you modify. If you modify a column which is part of the clustered index,
the row need to be moved. If you modify a column which is part of a non-clustered index, then the
non-clustered index need to be modified accordingly.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:254001c4a4d8$6d1a0710$a501280a@.phx.gbl...[vbcol=seagreen]
> Would Updates be the same?
>
> just a heap. A heap
> next row that have a
> monotonically incrementing value
> it goes to the end of
> message
> would
> It
|||Did Karen mention something about "hot spots" if using clustered identity
column in her book? I have to look it up...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#tM$WXNpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> Actually it should be slightly faster than if it were just a heap. A heap
> has to do some lookups to determine where to place the next row that have
a
> slight bit of overhead. A CI insert with a monotonically incrementing
value
> is a no brainer per say for SQL Server. As you stated it goes to the end
of
> the last page.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
>
|||Who's Karen? What book?

>--Original Message--
>Did Karen mention something about "hot spots" if using
clustered identity
>column in her book? I have to look it up...
>
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
message[vbcol=seagreen]
>news:#tM$WXNpEHA.1668@.TK2MSFTNGP14.phx.gbl...
just a heap. A heap[vbcol=seagreen]
next row that have[vbcol=seagreen]
>a
monotonically incrementing[vbcol=seagreen]
>value
it goes to the end[vbcol=seagreen]
>of
in message[vbcol=seagreen]
would[vbcol=seagreen]
Index? It[vbcol=seagreen]
Identity
>
>.
>
|||It's Kalen Delaney and her book is "Inside SQL Server 2000" which every good
dba should have a copy of...
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:03ec01c4a4ea$a9707ac0$a601280a@.phx.gbl...[vbcol=seagreen]
> Who's Karen? What book?
>
> clustered identity
> message
> just a heap. A heap
> next row that have
> monotonically incrementing
> it goes to the end
> in message
> would
> Index? It
> Identity
|||In addition to Tibor's comments if you update a variable column it may make
the row too large to fit everything on the page and cause a split. But this
will happen regardless of the column the CI is on if the data won't fit.
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e1EmQnNpEHA.1712@.tk2msftngp13.phx.gbl...
> It depends on which columns you modify. If you modify a column which is
part of the clustered index,
> the row need to be moved. If you modify a column which is part of a
non-clustered index, then the
> non-clustered index need to be modified accordingly.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:254001c4a4d8$6d1a0710$a501280a@.phx.gbl...
>
|||Hi
Hot spotting used to be a bigger problem in pre SQL 2000 SP 2.
The storage engine team have done a lot of improvements in their quest for
better performance. The actual limitation is now the Page Allocation Map.
Unless yopu are pushing 1'000's of inserts per second, you won't have an
issue.
Standard rules apply, keep the transactions short and don't have excessive
indexes.
Regards
Mike
"Raymond Fang" wrote:

> Did Karen mention something about "hot spots" if using clustered identity
> column in her book? I have to look it up...
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#tM$WXNpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> a
> value
> of
>
>
|||Mike, Thanks!
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:6DC591BA-8EC3-4F9F-BF43-993CB1BA534E@.microsoft.com...[vbcol=seagreen]
> Hi
> Hot spotting used to be a bigger problem in pre SQL 2000 SP 2.
> The storage engine team have done a lot of improvements in their quest for
> better performance. The actual limitation is now the Page Allocation Map.
> Unless yopu are pushing 1'000's of inserts per second, you won't have an
> issue.
> Standard rules apply, keep the transactions short and don't have excessive
> indexes.
> Regards
> Mike
> "Raymond Fang" wrote:
identity[vbcol=seagreen]
heap[vbcol=seagreen]
have[vbcol=seagreen]
end[vbcol=seagreen]