collation conflict

Hi, all
I am doing testing of my application, actualy one Store Procedure at the moment.
I have development database.
I copy tables that I need for the testing.
The same queries that run on live database fail here wth the message
'cannot resolve collation conflict...'
I discovered that it is due to all copied tables have other collation in char columns.
When I change them to database default setting, the stored procedure works OK.
My question is:
how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happeni
ng.
I am very grateful for any helpful information.
TIA
Elizabeta
Elizabeta,
If you script the CREATE TABLE scripts in Query Analyzer, you can go
to Tools|Options|[Script tab at far right] and check the box for "script
only 7.0 compatible features." If the collate clause is the only SQL
Server 2000 feature you are using, it might work for you. Otherwise,
assuming all the collations are the same, you can just do a search and
replace after scripting all the tables all at once from somewhere.
Steve Kass
Drew University
Elizabeta wrote:

>Hi, all
>I am doing testing of my application, actualy one Store Procedure at the moment.
>I have development database.
>I copy tables that I need for the testing.
>The same queries that run on live database fail here wth the message
>'cannot resolve collation conflict...'
>I discovered that it is due to all copied tables have other collation in char columns.
>When I change them to database default setting, the stored procedure works OK.
>My question is:
>how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happen
ing.
>I am very grateful for any helpful information.
>TIA
>Elizabeta
>
|||Thanks, Steve,
it works from SQL query analyzer. I tick the option
'do not csript collation clause...'
Cheers
"Steve Kass" wrote:
[vbcol=seagreen]
> Elizabeta,
> If you script the CREATE TABLE scripts in Query Analyzer, you can go
> to Tools|Options|[Script tab at far right] and check the box for "script
> only 7.0 compatible features." If the collate clause is the only SQL
> Server 2000 feature you are using, it might work for you. Otherwise,
> assuming all the collations are the same, you can just do a search and
> replace after scripting all the tables all at once from somewhere.
> Steve Kass
> Drew University
> Elizabeta wrote:
ening.
>

collation conflict

Hi, all
I am doing testing of my application, actualy one Store Procedure at the moment.
I have development database.
I copy tables that I need for the testing.
The same queries that run on live database fail here wth the message
'cannot resolve collation conflict...'
I discovered that it is due to all copied tables have other collation in char columns.
When I change them to database default setting, the stored procedure works OK.
My question is:
how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happening.
I am very grateful for any helpful information.
TIA
ElizabetaElizabeta,
If you script the CREATE TABLE scripts in Query Analyzer, you can go
to Tools|Options|[Script tab at far right] and check the box for "script
only 7.0 compatible features." If the collate clause is the only SQL
Server 2000 feature you are using, it might work for you. Otherwise,
assuming all the collations are the same, you can just do a search and
replace after scripting all the tables all at once from somewhere.
Steve Kass
Drew University
Elizabeta wrote:
>Hi, all
>I am doing testing of my application, actualy one Store Procedure at the moment.
>I have development database.
>I copy tables that I need for the testing.
>The same queries that run on live database fail here wth the message
>'cannot resolve collation conflict...'
>I discovered that it is due to all copied tables have other collation in char columns.
>When I change them to database default setting, the stored procedure works OK.
>My question is:
>how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happening.
>I am very grateful for any helpful information.
>TIA
>Elizabeta
>|||Thanks, Steve,
it works from SQL query analyzer. I tick the option
'do not csript collation clause...'
Cheers
"Steve Kass" wrote:
> Elizabeta,
> If you script the CREATE TABLE scripts in Query Analyzer, you can go
> to Tools|Options|[Script tab at far right] and check the box for "script
> only 7.0 compatible features." If the collate clause is the only SQL
> Server 2000 feature you are using, it might work for you. Otherwise,
> assuming all the collations are the same, you can just do a search and
> replace after scripting all the tables all at once from somewhere.
> Steve Kass
> Drew University
> Elizabeta wrote:
> >Hi, all
> >
> >I am doing testing of my application, actualy one Store Procedure at the moment.
> >
> >I have development database.
> >
> >I copy tables that I need for the testing.
> >
> >The same queries that run on live database fail here wth the message
> >'cannot resolve collation conflict...'
> >
> >I discovered that it is due to all copied tables have other collation in char columns.
> >
> >When I change them to database default setting, the stored procedure works OK.
> >
> >My question is:
> >
> >how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happening.
> >
> >I am very grateful for any helpful information.
> >
> >TIA
> >
> >Elizabeta
> >
> >
>

collation conflict

Hi, all
I am doing testing of my application, actualy one Store Procedure at the mo
ment.
I have development database.
I copy tables that I need for the testing.
The same queries that run on live database fail here wth the message
'cannot resolve collation conflict...'
I discovered that it is due to all copied tables have other collation in cha
r columns.
When I change them to database default setting, the stored procedure works O
K.
My question is:
how can I avoid to have collation in new copied tables, although the databas
es have it the same. I tried to copy using CREATE TABLE scripts or DTS servi
ces, copy as objects, always the same problem. Is there any setting on SQL s
erver to avoid this happeni
ng.
I am very grateful for any helpful information.
TIA
ElizabetaElizabeta,
If you script the CREATE TABLE scripts in Query Analyzer, you can go
to Tools|Options|[Script tab at far right] and check the box for "script
only 7.0 compatible features." If the collate clause is the only SQL
Server 2000 feature you are using, it might work for you. Otherwise,
assuming all the collations are the same, you can just do a search and
replace after scripting all the tables all at once from somewhere.
Steve Kass
Drew University
Elizabeta wrote:

>Hi, all
>I am doing testing of my application, actualy one Store Procedure at the m
oment.
>I have development database.
>I copy tables that I need for the testing.
>The same queries that run on live database fail here wth the message
>'cannot resolve collation conflict...'
>I discovered that it is due to all copied tables have other collation in ch
ar columns.
>When I change them to database default setting, the stored procedure works
OK.
>My question is:
>how can I avoid to have collation in new copied tables, although the databases have
it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as ob
jects, always the same problem. Is there any setting on SQL server to avoid this hap
pen
ing.
>I am very grateful for any helpful information.
>TIA
>Elizabeta
>|||Thanks, Steve,
it works from SQL query analyzer. I tick the option
'do not csript collation clause...'
Cheers
"Steve Kass" wrote:

> Elizabeta,
> If you script the CREATE TABLE scripts in Query Analyzer, you can go
> to Tools|Options|[Script tab at far right] and check the box for "scri
pt
> only 7.0 compatible features." If the collate clause is the only SQL
> Server 2000 feature you are using, it might work for you. Otherwise,
> assuming all the collations are the same, you can just do a search and
> replace after scripting all the tables all at once from somewhere.
> Steve Kass
> Drew University
> Elizabeta wrote:
>
ening.[vbcol=seagreen]
>sqlsql

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 to calculate lat/lon given a lat/lon and distance?

My math skills are not up to it so I have to ask for it!
I need a procedure which accepts one lat, lon, and Distance(in feet? miles?)
and returns the lat's and lon's which describe a square around the given
lat/lon with 2 * Distance on a side. This will be used to search a database
for objects which fit within the given square. I can't draw it here, but the
given lat/lon would be at the center of the square. Any suggestions where I
might find an example? I have code to find the distance between two lat/lon'
s
but converting it is well beyond me.
Thanks,
MichaelPost DDL and some sample data, and we can help you find a solution.
ML|||> I need a procedure which accepts one lat, lon, and Distance(in feet? miles?)d">
> and returns the lat's and lon's which describe a square around the given
> lat/lon with 2 * Distance on a side.
Well, for starters, there's no such thing as a square on the surface of the
Earth. A square can only exist on a flat surface. So that leads us to...
How accurate does the result need to be?
And how large will the distance be, at max?
To get *really* accurate, you need to use spherical trig and a formula that
is really hairy.
But to get "reasonable" results--as in searching for all zip codes that are
within 50 miles of a given zip code--you can get away with approximating
things just using the pythagorean theorem ( a^2 + b^2 = c^2: square of the
hypotenuse of a right triangle is equal to the squares of the other two
sides).
Also, the number of miles per degree (or degrees per mile) will vary from
the North side of the "square" to the South side thereof. [Obvious when you
look at a globe: Near the poles, there are many fewer miles per degree than
there are at the equator.]

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 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 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 behaviour/performance on 2 machines

Hi,
I have UAT and production servers with same database schema. I am running a
stored procedure on both machines, Its taking much more time on UAT (31 hrs)
where as it is taking less time i.e. 8 hrs (which is expected because of
nature of query) Can someone please explain why is it taking more time on UA
T?
UAT SQL SERVER 2000 has SP4
Production SQL SERVER 2000 has SP3
Please help ASAP.
I can also be reached at sahil.arora@.cit.com
Thanks
SahilJust becuase the servers share the same database model, there is the issue
of differences in hardware configuration and even differences in the
physical implementation of the database such as: volume of data, placement
of files, index fragmentation, statistics, sever/database configurations,
etc. To start with, take a look at the Performance tab of Task Manager on
both servers and compare to what extent they are maxing out on memory and
CPU.
Compare the execution plan between the 2 servers:
http://msdn.microsoft.com/library/d... />
1_1pfd.asp
Also, the following article provides some good performance oriented check
lists and explains how to audit performance.
http://www.sql-server-performance.c...mance_audit.asp
"Sahil Arora" <Sahil Arora@.discussions.microsoft.com> wrote in message
news:40E34A48-D573-46EF-98BE-BA4A84A9A113@.microsoft.com...
> Hi,
> I have UAT and production servers with same database schema. I am running
> a
> stored procedure on both machines, Its taking much more time on UAT (31
> hrs)
> where as it is taking less time i.e. 8 hrs (which is expected because of
> nature of query) Can someone please explain why is it taking more time on
> UAT?
> UAT SQL SERVER 2000 has SP4
> Production SQL SERVER 2000 has SP3
> Please help ASAP.
> I can also be reached at sahil.arora@.cit.com
> Thanks
> Sahil|||I just checked everything on both servers, its same and the server on which
is taking more time is much more powerful than server with less time. any
comments?
"JT" wrote:

> Just becuase the servers share the same database model, there is the issue
> of differences in hardware configuration and even differences in the
> physical implementation of the database such as: volume of data, placement
> of files, index fragmentation, statistics, sever/database configurations,
> etc. To start with, take a look at the Performance tab of Task Manager on
> both servers and compare to what extent they are maxing out on memory and
> CPU.
> Compare the execution plan between the 2 servers:
> http://msdn.microsoft.com/library/d...>
n_1_1pfd.asp
> Also, the following article provides some good performance oriented check
> lists and explains how to audit performance.
> http://www.sql-server-performance.c...mance_audit.asp
>
> "Sahil Arora" <Sahil Arora@.discussions.microsoft.com> wrote in message
> news:40E34A48-D573-46EF-98BE-BA4A84A9A113@.microsoft.com...
>
>|||Do both servers have identical volumes of data, is the execution plan of the
query identical, is the OS level file fragmentation and database level index
fragmentation optimized or similar on both servers, etc.. It could be one or
all of a hundred things. Perhaps running a performance audit log on both
servers and comparing the results will reveal something.
INF: Job to Monitor SQL Server 2000 Performance and Activity
http://support.microsoft.com/defaul...kb;en-us;283696
"Sahil Arora" <SahilArora@.discussions.microsoft.com> wrote in message
news:2FE8B79F-9EC2-445A-9648-65F1C7CAABAD@.microsoft.com...
>I just checked everything on both servers, its same and the server on which
> is taking more time is much more powerful than server with less time. any
> comments?
> "JT" wrote:
>

Coalesce

Hi.

I have a piece of a store procedure I don't quite understand, as follows:

SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId
FROM utDeal d WITH (NOLOCK)
INNER JOIN utContactIllustration ci WITH (NOLOCK)
ON ci.IllustrationId = d.IllustrationId
WHERE d.DealReference LIKE (COALESCE(@.DealReference,'%'))

What exactly is the COALESCE function doing here with the parameter?Returns the first nonnull expression among its arguments.|||Ok,

What does that mean? it returns the first parameter that is null or it returns the first record that is null.|||Originally posted by SQLSlammer
Ok,

What does that mean? it returns the first parameter that is null or it returns the first record that is null.

declare @.id int,@.id2 int
set @.id=null
set @.id2=1
select COALESCE(@.id,@.id2)

It returns the first parameter that is not null...

WHERE d.DealReference LIKE (COALESCE(@.DealReference,'%'))

In your query COALESCE(@.DealReference,'%')) means that if @.DealReference is null then COALESCE returns '%' and finally it is LIKE '%'.|||coalesce returns the first non-null value in its list

in your particular example, it's a sloppy stored proc

if the parameter @.DealReference is null, then the sql statement will actually look like this:

... where d.DealReference LIKE (COALESCE(null,'%'))

but in that case, since it's a stored proc, it should not generate a WHERE clause at all

furthermore, since it's a LIKE condition, the @.DealReference has better include the wildcard characters, otherwise it will function as an equality

again, the stored proc should tailor the sql specifically|||Hi,

Thanks for that,
so basically it will return all records if the parameter is null, what would be a beter way of handling a null parameter?|||what would be a better way of handling a null parameter?

don't generate the WHERE clause

that, too, will return all records|||Hi,

Ok, I should explain a little more, there is lot more to this sp than I have shown, there are 7 parameters for filtering and I really don't want it to return all records cos that would be over 7 million records into the temp table which is pointless:

The full SP is attached and yes I know its not very good (I did not write this).

Any advice would be appreciated very much|||Originally posted by SQLSlammer
Hi,

Ok, I should explain a little more, there is lot more to this sp than I have shown, there are 7 parameters for filtering and I really don't want it to return all records cos that would be over 7 million records into the temp table which is pointless:

The full SP is attached and yes I know its not very good (I did not write this).

Any advice would be appreciated very much

Well, there is bad with using coalesce in this proc. I guess you could change a little logic in your proc:

You are inserting more records in table @.Deals than late you selecting from it because in select you are using more filters then for insert. You could use all filters during inserting - it will be more complicated query but it is worth to try.

INSERT @.Deals (DealReference, DealId, IllustrationId, ContactId)\par
SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId\par
FROM utDeal d WITH (NOLOCK)\par
\tab INNER JOIN utContactIllustration ci WITH (NOLOCK)\par
\tab\tab ON ci.IllustrationId = d.IllustrationId\par
WHERE d.DealReference LIKE (COALESCE(@.DealReference,'%')) \par

SELECT DISTINCT * FROM @.deals \par
WHERE \tab\par
\tab DealReference LIKE (COALESCE(@.DealReference,'%')) \par
AND\par
\tab UserName LIKE (COALESCE(@.UserName, '%'))\par
AND\par
\tab LastAction LIKE (COALESCE(@.ActionType, '%'))\par
AND\par
\tab PostCode LIKE (COALESCE(@.PostCode, '%'))\par
AND\par
\tab CurrentStage LIKE (COALESCE(@.Stage, '%'))\par
AND\par
\tab Surname LIKE (COALESCE(@.Surname, '%'))\par
AND\par
\tab Telephone LIKE (COALESCE(@.Telephone, '%'))\par
ORDER BY DealId\par|||you know your data better than i do, but the relevant statement in your proc is

INSERT @.Deals (DealReference, DealId, IllustrationId, ContactId)
SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId
FROM utDeal d WITH (NOLOCK)
INNER JOIN utContactIllustration ci WITH (NOLOCK)
ON ci.IllustrationId = d.IllustrationId
WHERE d.DealReference LIKE (COALESCE(@.DealReference,'%'))

so if @.DealReference is null, it will bring back all rows that satisfy that join, whether that's 7 million, i don't know, but it could be

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).

Clustered Active/Active in Single-user mode

What is the right procedure to start an instance of a clustered Active/Active Sql Server ?
The online-book unspecifically writes:
From a command prompt, enter:
sqlservr.exe -c -m
But some KB Technet articles say do start/stop SQL instance only from cluster administrator!
So how to do it ?
Thanks!
Michele Moglia
For normal operating procedures the proper method is to start from the
Cluster Administator.
Starting from the command prompt with sqlservr.exe -c -m is a trouble
shooting technique. It starts SQL Server as an application in single user
mode and as such would be pretty useless in a production environment.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Ok but if I have to restore the master db of the clustered sql server instance
I have to start the instance in single-user mode so can I start from command prompt
without any fear or problem ?
Thanks
Michele Moglia