Upgrade Challenge - For The SQL Server Guru's
Nov 3, 2000
This is an upgrade issue :
I have a SQL server 6.5 with NT users mapped into the SQL Server running in production. At one point of time, this Server was set up for mixed security, which has since been changed to standard ( still with NT users mapped in ).
When upgrading to SQL 7.0, I noticed that it brought in both : Nt USers as well as the mapped login id's as if they were separate entities ( Probably since SQL Server stores mapped login id's in master..syslogins, and mapped NT logins information are stored in the registry ). It also, does not bring in passwords.
I am planning to re-write this part of the upgrade and write my own scripts to transfer the logins ( Unfortunately, I will have to drop them first from the database using sp_dropuser, immediately after the upgrade process ).
Here's how I'm planning to do this : BCP out the contents of the syslogins table from the 6.5, bring it in into, say 'sys_xlogins' ( New table ) in 7.0, update the 'sysxlogins' system table in 7.0 with the passwords from the 'sys_xlogins' table, and add all the users back into the database.
If anyone else has been thru this, or anything like this ( Upgrading from mixed security 6.5 to 7.0 ), I would appreciate their inputs. Any words of wisdom / experience welcome !
View 3 Replies
ADVERTISEMENT
Mar 6, 2008
To whom it may concern,
I have developed a web application using ASP 3.0 with a Access DB 2002 backend. I have decided to recently to upgrade the database to SQL 2005 but I am having issues that I cannot work out because I am new to MsSql Language. It appears that the FORMAT function in Access does not work in MsSql ( does not recognise the function ) along with some of the PIVOT and TRANSFORM calls (coming up with "Syntax error near "..."") . Please see the below code. The areas that are showing up as errors when the application is run are highlighted in red.
Thanking you in advance
Darren
<!-- #include file="./../../common/includes/i_header_login.asp" -->
<link href="../../common/css/proceduremanual.css" rel="stylesheet" type="text/css">
<%Sub RecordsetToCSV(ByRef RS, ByVal CSVFilePath, ByVal IncludeFieldNames)
Set objCSVFile = CreateObject("ADODB.Stream")
Call objCSVFile.Open
If IncludeFieldNames Then
'string concatenation issues aren't a problem for a small string of field names
For Each Field In RS.Fields
If FieldNames = "" Then
FieldNames = Field.Name
Else
FieldNames = FieldNames & "," & Field.Name
End If
Next
FieldNames = FieldNames & vbCRLF
Call objCSVFile.WriteText(FieldNames, 1)
End If
Call objCSVFile.WriteText(FieldNames & RS.GetString(adClipString, , ",", vbCRLF, ""))
Call objCSVFile.SaveToFile(CSVFilePath, 2)
Set objCSVFile = Nothing
End Sub
%>
<%
function evaluate(pFormula, pUserID, pMonth, pYear)
if isnull(pFormula) then
evaluate = 0
exit function
end if
x = 0
key = ""
zero = "0"
nine = "9"
eFormula = ""
lf = len(pFormula)
for i = 1 to lf
c = mid(pFormula,i,1)
if x = 1 and c >= zero and c <= nine then key = key & c
if x = 1 and (c < zero or c > nine or i = lf) then
set rsd = cn.execute("select sum(total) from Data where UserID = " & pUserID & " and month(weekEnding) = " & pMonth & " and year(weekEnding) = " & pYear & " and GroupTaskID=" & key)
eFormula = eFormula & rsd.fields(0)
rsd.close
key = ""
if c < zero or c > nine then x = 0
end if
if c = "v" then
key = ""
x = 1
end if
if x = 0 then eFormula = eFormula & c
next
on error resume next
evaluate = round(eval(eFormula),0)
'if err then evaluate = err.description & ": " & eformula
if err then evaluate = 0
end function
%>
<%
period = split(request.form("period"),",")
if ubound(period) = 1 then
mmmm = period(0)+0
yyyy = period(1)+0
else
mmmm = 0
yyyy = 0
end if
%>
<div align="left" class="MainBodyText" style="width: 100%; height: 100%">
<div align="left" valign="top" width="200" style="margin:5; padding:3; font-family:arial; font-size:10pt; font-weight:bold; font-color000000;" >
<% if session("groupid") = 1 then%>
<a href="../main/administration.asp"> <<< Administration Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
<%if session("groupid") = 2 then%>
<a href="../main/manager.asp"> <<< Manager Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
<%if session("groupid") = 3 then%>
<a href="../main/user.asp"> <<< User Home</a>
<%end if %>
<%if session("groupid") = 4 then%>
<a href="../main/general.asp"> <<< Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
</div>
<p align="center" class="MainBodyText">
<img src="../../images/si.gif" align="center"><br><b>Month to Date Dashboard</b></p>
<p align="center" class="MainBodyText">
To use the Print Page function please select the period required then select
view report. You can then select Print Page which will print out the rendered
page.</p>
<form name="f1" method="POST" action="dialmtd.asp">
<div id="printReady">
<table align="center" border="0" width="663" style="border-collapse: collapse" bordercolor="#111111" cellpadding="2">
<tr>
<td align="left" width="127"><font face="Arial" size="2">
<select name="benchmarkgroup" style="color: #000000; border: 0px solid #000000; background-color: #FFE737">
<option value="0" <%if request.form("benchmarkgroup") = "" then response.write "selected"%>>Select Report
<% set rs = cn.execute("select TaskGroupID,TaskGroup from BenchmarkGroups where Active = 1 order by TaskGroupID")
do until rs.eof
if request.form("taskgroupid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="143"><font face="Arial" size="2">
<select name="period">
<option value="" <%if request.form("period") = "" then response.write "selected"%>>Select Period
<% set rs = cn.execute("select year(WeekEnding), month(WeekEnding), format(Weekending,""mmmm yyyy"") from data group by Year(WeekEnding), Month(WeekEnding), format(WeekEnding,""mmmm yyyy"") order by 1,2")
do until rs.eof
x = rs.fields(1) & "," & rs.fields(0)
if request.form("period") = x then selected = "selected" else selected = "" %>
<option value=<%=x & " " & selected%>><%=rs.fields(2)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="117"><font face="Arial" size="2">
<select name="stateid">
<option value="0" <%if request.form("stateid") = "" then response.write "selected"%>>All States
<% set rs = cn.execute("select stateid,state from tblState where benchmarkactive order by state")
do until rs.eof
if request.form("stateid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="127"><font face="Arial" size="2">
<select name="countryid">
<option value="0" <%if request.form("countryid") = "" then response.write "selected"%>>All Countries
<% set rs = cn.execute("select countryid,country from tblCountry where benchmarkactive order by country")
do until rs.eof
if request.form("countryid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="129"><input type="image" src="../../Images/view-report.gif" onclick="f1.submit()" name="f1" alt="Click Here to Generate Report"></td>
<td width="127"><input type="image" src="../../Images/print-report.gif" rname="printMe" onClick="printSpecial()" alt="Click Here to Print the Page"></td>
</tr>
</table>
<% if mmmm > 0 then %>
<table border="0" width="450" align="left" style="margin-left:10px; border-collapse: collapse" bordercolor="#111111" cellpadding="2" cellspacing="4">
<thead>
<tr>
<% sql = " where 1 = 1 AND BenchmarkGroup =" & request("benchmarkgroup")
if request.form("stateid")+0 then sql = sql & " and stateid = " & request.form("stateid")
if request.form("countryid")+0 then sql = sql & " and countryid = " & request.form("countryid")
set rs = cn.Execute("TRANSFORM First(UserID) SELECT description, minimum, mintype, formula FROM BenchmarksActiveUsers" & sql & " GROUP BY displayOrder, description, minimum, mintype, formula ORDER BY displayOrder PIVOT Name")
lastfield = rs.fields.count-1
s = 4 %>
<td align="left" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Benchmark</b></font> </td>
<td align="center" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Company Average</b></font> </td>
<% for i = s to lastfield %>
<% next %>
</tr>
</thead>
<tbody>
<br>
<% dim v()
do until rs.eof
min = int(rs.fields("minimum"))
mintype = rs.fields("mintype")
sumv = 0
avg = 0
n = 0
for j = s to lastfield
redim preserve v(j)
v(j) = evaluate(rs.fields("formula"),rs.fields(j),mmmm,yyyy)
sumv = sumv + v(j)
n = n + 1
next
if n > 0 then avg = round(sumv / n) %>
<tr><td></td><td align="center" valign="bottom"><font face="Arial" size="2">Target: <%=min%> <%=mintype%></font></td><td></td></tr>
<tr>
<td bgcolor="#FFFFFF">
<p align="right"><font face="Arial" size="2"><%=rs.fields("description")%></font></td>
<td bgcolor="#FFFFFF">
<p align="center"><font face="Arial" size="1"><% If (avg/min)*100 <=12 Then %><IMG SRC="../images/dial-1.gif">
<% ELSEIF (avg/min)*100 <=24 Then%><IMG SRC="../images/dial-2.gif">
<% ELSEIF (avg/min)*100 <=36 Then%><IMG SRC="../images/dial-3.gif">
<% ELSEIF (avg/min)*100 <=48 Then%><IMG SRC="../images/dial-4.gif">
<% ELSEIF (avg/min)*100 <=60 Then%><IMG SRC="../images/dial-5.gif">
<% ELSEIF (avg/min)*100 <=72 Then%><IMG SRC="../images/dial-6.gif">
<% ELSEIF (avg/min)*100 <=86 Then%><IMG SRC="../images/dial-7.gif">
<% ELSEIF (avg/min)*100 <=99 Then%><IMG SRC="../images/dial-8.gif">
<% ELSEIF (avg/min)*100 =100 Then%><IMG SRC="../images/dial-9.gif">
<% ELSEIF (avg/min)*100 <=112 Then%><IMG SRC="../images/dial-10.gif">
<% ELSEIF (avg/min)*100 <=124 Then%><IMG SRC="../images/dial-11.gif">
<% ELSEIF (avg/min)*100 <=136 Then%><IMG SRC="../images/dial-12.gif">
<% ELSEIF (avg/min)*100 <=148 Then%><IMG SRC="../images/dial-13.gif">
<% ELSEIF (avg/min)*100 <=160 Then%><IMG SRC="../images/dial-14.gif">
<% ELSEIF (avg/min)*100 <=172 Then%><IMG SRC="../images/dial-15.gif">
<% ELSEIF (avg/min)*100 <=186 Then%><IMG SRC="../images/dial-16.gif">
<% ELSEIF (avg/min)*100 =>187 Then%><IMG SRC="../images/dial-17.gif">
<%END IF%></font></td><td><img src="../images/under-target.gif"><br><br><img src="../images/over-target.gif"></td>
</tr><tr><td></td><td valign="top" align="center"><font face="Arial" size="2">Actual: <%=avg%> <%=mintype%></font><hr></td><td></td></tr><% for j = s to lastfield
color = "#000000"
if int(v(j)) < min then color="#FF0000" %>
<% next %>
</tr>
<% rs.movenext
loop
rs.close
cn.close
set cn = nothing %>
</tbody>
</table>
<% end if %>
</div>
</form>
</div>
<!-- #include file="./../../common/includes/i_footer.asp" -->
View 2 Replies
View Related
Feb 26, 2008
To whom it may concern,
I have developed a web application using ASP 3.0 with a Access DB 2002 backend. I have decided to recently to upgrade the database to SQL 2005 but I am having issues that I cannot work out because I am new to MsSql Language. It appears that the FORMAT function in Access does not work in MsSql ( does not recognise the function ) along with some of the PIVOT and TRANSFORM calls (coming up with "Syntax error near "..."") . Please see the below code. The areas that are showing up as errors when the application is run are highlighted in red.
Thanking you in advance
Darren
<!-- #include file="./../../common/includes/i_header_login.asp" -->
<link href="../../common/css/proceduremanual.css" rel="stylesheet" type="text/css">
<%Sub RecordsetToCSV(ByRef RS, ByVal CSVFilePath, ByVal IncludeFieldNames)
Set objCSVFile = CreateObject("ADODB.Stream")
Call objCSVFile.Open
If IncludeFieldNames Then
'string concatenation issues aren't a problem for a small string of field names
For Each Field In RS.Fields
If FieldNames = "" Then
FieldNames = Field.Name
Else
FieldNames = FieldNames & "," & Field.Name
End If
Next
FieldNames = FieldNames & vbCRLF
Call objCSVFile.WriteText(FieldNames, 1)
End If
Call objCSVFile.WriteText(FieldNames & RS.GetString(adClipString, , ",", vbCRLF, ""))
Call objCSVFile.SaveToFile(CSVFilePath, 2)
Set objCSVFile = Nothing
End Sub
%>
<%
function evaluate(pFormula, pUserID, pMonth, pYear)
if isnull(pFormula) then
evaluate = 0
exit function
end if
x = 0
key = ""
zero = "0"
nine = "9"
eFormula = ""
lf = len(pFormula)
for i = 1 to lf
c = mid(pFormula,i,1)
if x = 1 and c >= zero and c <= nine then key = key & c
if x = 1 and (c < zero or c > nine or i = lf) then
set rsd = cn.execute("select sum(total) from Data where UserID = " & pUserID & " and month(weekEnding) = " & pMonth & " and year(weekEnding) = " & pYear & " and GroupTaskID=" & key)
eFormula = eFormula & rsd.fields(0)
rsd.close
key = ""
if c < zero or c > nine then x = 0
end if
if c = "v" then
key = ""
x = 1
end if
if x = 0 then eFormula = eFormula & c
next
on error resume next
evaluate = round(eval(eFormula),0)
'if err then evaluate = err.description & ": " & eformula
if err then evaluate = 0
end function
%>
<%
period = split(request.form("period"),",")
if ubound(period) = 1 then
mmmm = period(0)+0
yyyy = period(1)+0
else
mmmm = 0
yyyy = 0
end if
%>
<div align="left" class="MainBodyText" style="width: 100%; height: 100%">
<div align="left" valign="top" width="200" style="margin:5; padding:3; font-family:arial; font-size:10pt; font-weight:bold; font-color000000;" >
<% if session("groupid") = 1 then%>
<a href="../main/administration.asp"> <<< Administration Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
<%if session("groupid") = 2 then%>
<a href="../main/manager.asp"> <<< Manager Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
<%if session("groupid") = 3 then%>
<a href="../main/user.asp"> <<< User Home</a>
<%end if %>
<%if session("groupid") = 4 then%>
<a href="../main/general.asp"> <<< Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
</div>
<p align="center" class="MainBodyText">
<img src="../../images/si.gif" align="center"><br><b>Month to Date Dashboard</b></p>
<p align="center" class="MainBodyText">
To use the Print Page function please select the period required then select
view report. You can then select Print Page which will print out the rendered
page.</p>
<form name="f1" method="POST" action="dialmtd.asp">
<div id="printReady">
<table align="center" border="0" width="663" style="border-collapse: collapse" bordercolor="#111111" cellpadding="2">
<tr>
<td align="left" width="127"><font face="Arial" size="2">
<select name="benchmarkgroup" style="color: #000000; border: 0px solid #000000; background-color: #FFE737">
<option value="0" <%if request.form("benchmarkgroup") = "" then response.write "selected"%>>Select Report
<% set rs = cn.execute("select TaskGroupID,TaskGroup from BenchmarkGroups where Active = 1 order by TaskGroupID")
do until rs.eof
if request.form("taskgroupid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="143"><font face="Arial" size="2">
<select name="period">
<option value="" <%if request.form("period") = "" then response.write "selected"%>>Select Period
<% set rs = cn.execute("select year(WeekEnding), month(WeekEnding), format(Weekending,""mmmm yyyy"") from data group by Year(WeekEnding), Month(WeekEnding), format(WeekEnding,""mmmm yyyy"") order by 1,2")
do until rs.eof
x = rs.fields(1) & "," & rs.fields(0)
if request.form("period") = x then selected = "selected" else selected = "" %>
<option value=<%=x & " " & selected%>><%=rs.fields(2)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="117"><font face="Arial" size="2">
<select name="stateid">
<option value="0" <%if request.form("stateid") = "" then response.write "selected"%>>All States
<% set rs = cn.execute("select stateid,state from tblState where benchmarkactive order by state")
do until rs.eof
if request.form("stateid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="127"><font face="Arial" size="2">
<select name="countryid">
<option value="0" <%if request.form("countryid") = "" then response.write "selected"%>>All Countries
<% set rs = cn.execute("select countryid,country from tblCountry where benchmarkactive order by country")
do until rs.eof
if request.form("countryid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="129"><input type="image" src="../../Images/view-report.gif" onclick="f1.submit()" name="f1" alt="Click Here to Generate Report"></td>
<td width="127"><input type="image" src="../../Images/print-report.gif" rname="printMe" onClick="printSpecial()" alt="Click Here to Print the Page"></td>
</tr>
</table>
<% if mmmm > 0 then %>
<table border="0" width="450" align="left" style="margin-left:10px; border-collapse: collapse" bordercolor="#111111" cellpadding="2" cellspacing="4">
<thead>
<tr>
<% sql = " where 1 = 1 AND BenchmarkGroup =" & request("benchmarkgroup")
if request.form("stateid")+0 then sql = sql & " and stateid = " & request.form("stateid")
if request.form("countryid")+0 then sql = sql & " and countryid = " & request.form("countryid")
set rs = cn.Execute("TRANSFORM First(UserID) SELECT description, minimum, mintype, formula FROM BenchmarksActiveUsers" & sql & " GROUP BY displayOrder, description, minimum, mintype, formula ORDER BY displayOrder PIVOT Name")
lastfield = rs.fields.count-1
s = 4 %>
<td align="left" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Benchmark</b></font> </td>
<td align="center" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Company Average</b></font> </td>
<% for i = s to lastfield %>
<% next %>
</tr>
</thead>
<tbody>
<br>
<% dim v()
do until rs.eof
min = int(rs.fields("minimum"))
mintype = rs.fields("mintype")
sumv = 0
avg = 0
n = 0
for j = s to lastfield
redim preserve v(j)
v(j) = evaluate(rs.fields("formula"),rs.fields(j),mmmm,yyyy)
sumv = sumv + v(j)
n = n + 1
next
if n > 0 then avg = round(sumv / n) %>
<tr><td></td><td align="center" valign="bottom"><font face="Arial" size="2">Target: <%=min%> <%=mintype%></font></td><td></td></tr>
<tr>
<td bgcolor="#FFFFFF">
<p align="right"><font face="Arial" size="2"><%=rs.fields("description")%></font></td>
<td bgcolor="#FFFFFF">
<p align="center"><font face="Arial" size="1"><% If (avg/min)*100 <=12 Then %><IMG SRC="../images/dial-1.gif">
<% ELSEIF (avg/min)*100 <=24 Then%><IMG SRC="../images/dial-2.gif">
<% ELSEIF (avg/min)*100 <=36 Then%><IMG SRC="../images/dial-3.gif">
<% ELSEIF (avg/min)*100 <=48 Then%><IMG SRC="../images/dial-4.gif">
<% ELSEIF (avg/min)*100 <=60 Then%><IMG SRC="../images/dial-5.gif">
<% ELSEIF (avg/min)*100 <=72 Then%><IMG SRC="../images/dial-6.gif">
<% ELSEIF (avg/min)*100 <=86 Then%><IMG SRC="../images/dial-7.gif">
<% ELSEIF (avg/min)*100 <=99 Then%><IMG SRC="../images/dial-8.gif">
<% ELSEIF (avg/min)*100 =100 Then%><IMG SRC="../images/dial-9.gif">
<% ELSEIF (avg/min)*100 <=112 Then%><IMG SRC="../images/dial-10.gif">
<% ELSEIF (avg/min)*100 <=124 Then%><IMG SRC="../images/dial-11.gif">
<% ELSEIF (avg/min)*100 <=136 Then%><IMG SRC="../images/dial-12.gif">
<% ELSEIF (avg/min)*100 <=148 Then%><IMG SRC="../images/dial-13.gif">
<% ELSEIF (avg/min)*100 <=160 Then%><IMG SRC="../images/dial-14.gif">
<% ELSEIF (avg/min)*100 <=172 Then%><IMG SRC="../images/dial-15.gif">
<% ELSEIF (avg/min)*100 <=186 Then%><IMG SRC="../images/dial-16.gif">
<% ELSEIF (avg/min)*100 =>187 Then%><IMG SRC="../images/dial-17.gif">
<%END IF%></font></td><td><img src="../images/under-target.gif"><br><br><img src="../images/over-target.gif"></td>
</tr><tr><td></td><td valign="top" align="center"><font face="Arial" size="2">Actual: <%=avg%> <%=mintype%></font><hr></td><td></td></tr><% for j = s to lastfield
color = "#000000"
if int(v(j)) < min then color="#FF0000" %>
<% next %>
</tr>
<% rs.movenext
loop
rs.close
cn.close
set cn = nothing %>
</tbody>
</table>
<% end if %>
</div>
</form>
</div>
<!-- #include file="./../../common/includes/i_footer.asp" -->
View 5 Replies
View Related
Mar 21, 2002
Is it possible to change table design without running an alter table SQL Script??
For example, change the following :
Table_A ( column1 int identity(1,1) <<----BEFORE
, column2 varchar(10) null
, column3 char(20) not null
)
to
Table_A ( column1 int identity(1,1) <<----AFTER
, column2 char(255) not null
, column3 varchar(20) null
)
Is it possible to change sysobjects and syscolumns or other system objects for this to occur??
I know this is not recommended but if I had to, what are the steps required to do this??
Thanks very much
View 1 Replies
View Related
Sep 27, 2000
Hi Guru,,
There are bunch of file on a SQL SERVER installed machine under WINNT floder they are as follows..
OTM1C.tmp
OTM2.tmp
OTM3.tmp
OTM4.tmp
OTM5.tmp
OTM6.tmp
OTMB.tmp
This files occupies 2G.B of disk space... We are in the cleaning process to create some disk space. The thing is no one really understand which application uses this files..
My question is does sql server uses this files... Can we delete this files..
Thank you..
truely
jessi.
View 1 Replies
View Related
May 29, 2004
As a developer, we always say "using a stored procedure, instead of a cliet side SQL statement, provides performance benefits". However, it seems it has not been true anymore since SQL Server 7.0.
See SQL online "Execution Plan Caching and Reuse" at http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409
I am quite confused with the following questions:
1. it seems since SQL 7.0, a SQL statement in client side uses the existing execution plan as a stored procedure does. That means SP doesn't has much advantage over SQL statement in terms of performance.
2. It seems, a stored procedure is not always compled ONLY once. If a stored procedure is not used for a long time, it could be kicked out from procedure cashe.
3. In order to use an existing execution plan, it seems that we have to use the fully qualified identifier, such as
SELECT * FROM Northwind.dbo.Employees
instead of
SELECT * FROM Employees
However, I rarely see anyone uses these kind of fully qualified references for objects both in SQL statements and SP. For example, in the sample database pubs and NorthWind, they don't use the fully qualified expression. I only see the use of it in master database.
I guess I might miss something in the issues above. I would like to get any explanation from SQL guru or anybody. Thanks a lot.
View 7 Replies
View Related
Apr 5, 2007
NEVER let anyone tell you that Oracle is faster than SQL Server:
http://www.dbforums.com/showthread.php?p=6265757#post6265757
So here is my challenge. What would you have to do to SQL Server to make it take 10 minutes to return a count from an empty table? Other than coding a loop that waits 10 minutes, of course.
I'm not sure it could be done.
View 11 Replies
View Related
May 12, 2000
Hello,
I need to know if I am able to use OLE DB connection strings without the username and password for Challenge/Response logins.
I have a web site that uses SSL and Challenge/Response to authenticate users, but my connection to the database is by embedding a generic username and password in the connection string.
I would like to leave that off and have the connection to the database use the challenge/response authentication when they first logged into the web site. This way I can control their permissions in SQL server.
I appreciate any help
Kevin
View 1 Replies
View Related
May 13, 2008
We have a server instance on SQL Server 2005 SP2 build 3042. We have a 32 bit x86 server. We attempted to upgrade to SP2 build 3054 KB934458. And we got the following error as stated in the Summary.txt file.
Code Snippet
**********************************************************************************
Product Installation Status
Product : SQL Server Database Services 2005 (MSSQLSERVER)
Product Version (Previous): 3042
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB934458_sqlrun_sql.msp.log
Error Number : 29538
Error Description : MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: e:SQLDatamssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again.
----------------------------------------------------------------------------------
The client tools and workstation components were successfully installed. The server is still reporting build 3042.
Here is the portions of the HotFix.log file.
Code Snippet
05/12/2008 09:19:09.041 Copy Engine: Creating MSP install log file at: C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB934458_sqlrun_sql.msp.log
05/12/2008 09:19:09.072 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller"
05/12/2008 09:19:09.103 Registry: Cannot read registry key value "Debug"
05/12/2008 09:21:29.382 MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: e:SQLDatamssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again.
05/12/2008 09:22:33.678 MSP returned 1603: A fatal error occurred during installation.
05/12/2008 09:22:33.724 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller"
Any help would be appreciated.
View 3 Replies
View Related
Apr 7, 2004
I would like to create a command that will make a new logical field that is true or false depending on weather field is a certian value for example
col1,col2,col3
col1 is firstname
col2 is lastname
col3 is numeric
SELECT col1,col2,(if col3=6)=true AS Boolean
FROM table
i know this code doesnt work but it is a representation of what i would like to happen
View 2 Replies
View Related
Jul 23, 2005
Hello, this probably isnt the best place to ask but I can't find a moresuitable sql newsgroup so I hope y'all dont mind too much.I have 2 tables; Cellar and ColourCELLAR contains the wine name, its year and the no.of bottles.Wine Year BottlesChardonnay 87 4Fume Blanc 87 2Pinot Noir 82 3Zinfandel 84 9COLOUR contains wine name and it's colourWine ColourChardonnay WhiteFume Blanc WhitePinot NoirRedZinfandel RoseThis is from a past exam paper btwOne of the questions was:Write the sql to count how many white wines there are in the table cellar.The solution that the lecturers included is:SELECT count(wine)FROM cellarWHERE colour='White'Now i havent' been able to try out this sql yet but to me that looks wrong.My solution would be:SELECT count(wine)FROM cellarWHERE cellar.wine = colour.wine and colour.colour='White'Can anyone tell me which one is correct, and if mine isn't correct then whyisn't it?Thanks
View 3 Replies
View Related
Oct 30, 2000
One of of server was restarted... In the early hours... And i found this has a resson in event viewer..
Does anybody knows what this means..
The computer has rebooted from a bugcheck. The bugcheck was: 0x0000007f (0x00000008, 0x00000000, 0x00000000, 0x00000000). Microsoft Windows NT [v15.1381]. A dump was saved in: C:WINNTMEMORY.DMP.
Thank you
Jessi.
View 1 Replies
View Related
Aug 28, 2000
Hi,
Below is the insert statement... it insert a new record by selecting the max ID and add plus 1 to the existing value.. with in the same statement..
My question is can i get the inserted value of NewID into a VARIABLE with in the same insert statement.. I dont want to pass another sql statement to select the MAX ID..
INSERT INTO tbTest(ID,EName)
SELECT (SELECT MAX(ID)+1 FROM TEST),'BRAIN'
Thanks
Jessi..
View 1 Replies
View Related
Nov 9, 2005
I am trying to write a DTS package at work that uses a loop to fire a query off against a different server/database at each loop iteration. Prior to the execute SQL task , I use ActiveX (VBScript) to change the Catalog and DataSource of the connection (created by drag and drop if icon). My Execute SQL task selects @@servername and getdate() just as a dummy query to make sure I am actually pointed at a different server.
While testing the properties of the connection using global variables and msgbox shows that the database (catalog) and server (data source) ip address is being changed, the result returned is always from the first server. I feel like I need to disconnect the connection object, change the parameters and then reconnect to the new server but there doesn't appear to be anyway to do this.
Anyone out there able to successfully change a connection object inthis manner??
View 1 Replies
View Related
May 12, 2006
I got the following error on my database when I ran DBCC CHECKDB
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'PhoneCall' (ID 254623950). Missing or invalid key in index 'PhoneCall0' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:98383:16) identified by (RID = (1:98383:16) ) has index values (PhoneCallID = 46361).
This error is on a couple of different indexii?? What do I do? Any help will be appreciated. Thank you
View 5 Replies
View Related
Aug 2, 2006
Ok guys I have read everything in my previous post but unfortunately can not seem to get it to work properly. Would anyone like to do this 10 minutes work (I am sure). Obviously I dont expect it to be free but if I continue I am going to get a sledge hammer to this now.
Thanks in advance
View 20 Replies
View Related
Mar 7, 2008
My boss asked me to interview 2 people on Monday for a programmer/developer position. They say they are gurus, but my boss wants me to find out for sure. I thought a question about bitwise operations would be good, maybe one about semi-joins. Are there any questions you can think of that will spot the phony pretty quick?
Thanks,
Jim
View 12 Replies
View Related
Jan 2, 2008
Hi,
I am trying to import comma delimitted text files which contain data for any particular country.
I have created SSIS package to import data into sql server table. table structure is exactly same as text file except one additional column which is to identify the CountryID. Text files we recieved does not contain CountryID column.
I am using BULK INSERT Task in SSIS to import the data as text files are quite big (up to 1 GB). during processing I do determine the country id for that text file as file name contain countrycode which can be use for lookup and get the countryid.
cyn_bills_20071208_032242.txt
CYN is the country code and country table has got country id for it.
I am using Format file with BULK INSERT task as table has additional column which does not present in source file.
currently after inserting all rows into table i have to run an UPDATE statement to update the value of CountryID column which is very expensive some time taking more then 30 min for (30 million rows)
Can anyone tell me how to insert an expression when using BULK INSERT command / Task in SSIS. there must be a way to insert litterals using Bulk Insert.
Thanks in Advance.
Furrukh Baig
View 6 Replies
View Related
Jan 3, 2001
Good afternoon one and all,
I have the following problem :
I have a table which currently has 40+ mill records. Some of the fields are of type nvarchar and these need changing to varchar. I have attempted to change these fields to varchar but the process proceeds to increase the transaction log (to 6 Gigs!) until there is no space left on the server (causing a multitude of other problems with existing programs on the server). Once this happens I have no choice but to kill the change.
Does any1 know how I can make these changes without SQL writing to the transaction log?
Any and all help appreciated,
Thanks
Gurmi
View 6 Replies
View Related
Feb 6, 2001
Hello,
I am executing the bcp statement with i a stored procedure, everything works fine. But the question is I want to pass the delimiter as a parameter to the BCP command. I tried but i couldnt make it. I declare a variable and set that variable to a delimiter and try to pass that variable to BCP, but it doesnt accept it, in the export file the delimiter is '@i' instead of ';'
DECLARE
@i CHAR(2)
SELECT @i = ';'
EXEC master..xp_cmdshell 'bcp " select city, name = case whenname like(''%''+char(13)+char(10)+''%'') then replace(name, char(13)+char(10),'''') whenname like(''%''+char(13)+''%'') then replace(name, char(13),'''')whenname like(''%''+char(10)+''%'') then replace(name, char(10),'''')else name end From dbDev..tbtest" queryout d:invoice.txt -c -Sdevelop_server -Uadmin -Padmin -t@i'
THANKS INDEED
JESSI.
View 1 Replies
View Related
Nov 20, 2000
Upon running DTS manually to transfer data from Excel into SQL Server, I
get the error:
-----------------------------ERROR OUPTUT ------------------------------------
Error at Source for Row number 264. Errors encountered so far in this task: 1. General error -2147217887 (80040E21).
Data for source column 3 ('Value') is too large for the specified buffer size.
---------------------------END ERROR OUTPUT----------------------------------
*** 'Value' is varchar(4000); largest having length of 1000.
*** The network packet size is 4096.
?? AM I SUPPOSED TO CHANGE THE BUFFER SIZE??
Your kind help is greatly appreciated
Thanks
Ziggy
View 2 Replies
View Related
Dec 11, 2000
Does anyone know if there is an updated Books Online for 2000 published anywhere?? Or even a more recent copy of the 7.0? MS has probably made corrections
to them.
Also, I'm looking for all the free info about DTS for 2000. Not anything that MS publishes, more of a third party evaluation.
Thanks in advance,
Adam
View 1 Replies
View Related
May 26, 2006
I have a application that uses a connection that is always active until the application is closed, and many other connections that are opened and closed as different data is requested. I DO close and dispose these connections, datasets, and data readers after requesting data to release the resources. BUT they still show in SQL Server 2005 Express Edition. I believe they are NOT active and are part of the Pooling system.
The problem is the connections don't seem to be reused, the Pooling system just makes more, so I end up with hundreds of them until no more connections can be created but SQL Server; hence timing out on some attempt to open a connection.
I have experimented with the 'Min Pool Size' and 'Max Pool Size' values in the connection string but the only change is behavior is that I can make more connection showing in SQL Server but the problem still happens anyway.
I am rewriting this application from VB6 to VB.Net. The VB6 version uses SQL Server 2005 Edition too and the connection problem doesn't exist.
Any ideas would be appreciated.
View 4 Replies
View Related
Dec 19, 2000
Hi,
Till couple of days ago.. I was able to watch different object counters in SQL Server performance monitor..
For some reason now i am unable to see non of the object counters related to sql server in performance monitor..
The error it records in event viewer is as follows..
The description for Event ID[1001] in Source [ SQLCTR70] could not be found.
It contains the following insertion string[s]: SQL Performance DLL Open function failed..
help is greatly appericated...
thanks indeed
jessi...
View 3 Replies
View Related
Jan 19, 2007
Hello all,
I have a question on whether the following stored precedure would be open to an SQL Injection attack. Assume that a string query would be passed to the SP.
I am told that because the password parameter is only varchar(8) that it is safe.
Can someone prove this wrong?
Thanks....
I have added sample code below.
CREATE TABLE [dbo].[JB_Test](
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Name] DEFAULT (''),
[Email] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Email] DEFAULT (''),
[Password] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Password] DEFAULT (''),
) ON [PRIMARY]
GO
Insert dbo.JB_Test (Name, Email, Password )
values ('John', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Paul', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Geroge', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Ringo', 'asdf@asdf.com', '2345')
GO
Create procedure dbo.JB_Test_Login
@Username varchar(100),
@Password varchar(8)
AS
select Name
from dbo.JB_Test
where Name = @Username
and Password = @Password
GO
--Clean Up Your Mess
--Drop procedure dbo.JB_Test_Login
--GO
--Drop Table dbo.JB_Test
--GO
JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
• Position SEO can provide your company with SEO Services at an affordable price
› As far as myself... I do this for fun!
View 20 Replies
View Related
Feb 27, 2007
Hello,
I've been trying to fill a tree view based on some criteria in another table.
I have the standard table with a ID - ParentID - Text
when I bind the table everything works fine.
What I need to do is display only the tree items based off a username and ID in another table.
Files can be found http://www.cthere.com/treeviewhelp.zip
EXAMPLE:
tblTourgroup(UserName = Jim)
tblTourgroup (GroupID = 110)
tblGroups (GroupID = 110)
Results from tblGroup:
- RESTAURANTS
- Cuisine
- Steakhouse
Query Results should be layed out like tblGroups
Any ideas?
This is what I have so far... But it does not traverse up and get the other nodes..
USE SQL2005_335573_cplanet;
GO
WITH DynamicTree (ParentID, GroupID, GroupName)
AS
(
SELECT e.ParentID, e.GroupID, e.GroupName
FROM tblGroups AS e
INNER JOIN tblTourGroup AS edh
ON e.GroupID = edh.GroupID
)
SELECT e.ParentID, e.GroupID, e.GroupName
FROM DynamicTree as e
INNER JOIN tblTourGroup AS dp
ON e.GroupID = dp.GroupID
WHERE dp.UserName = N'Test'
GO
View 1 Replies
View Related
May 3, 2007
I need some help I have this massive sql script the problem is I tried to put it in to the query string box in my sql reports and it will not take it this script will run if I break it up but I think it is to large is there a sql guru out there that can show me how to reduce the size of this script maybe by using an out parameter to a stored proceedure. I just dont know what to do and need to produce the report from this script. Below is the entire script
SELECT 'Prior Year All ' as 'qtr', COUNT(JOB.JOBID) AS 'transcount', COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount', SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost', SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost', AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc', SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled', SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT', SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT', SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT', JOB.JURISDICTION, PAYER.PAY_GROUPNAME, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.PATIENTID, JOB.INVOICE_DATE, JOB.JOBOUTCOMEID, JOB.SERVICEOUTCOME, INVOICE_AR.INVOICE_NO, INVOICE_AR.INVOICE_DATE AS Expr1, INVOICE_AR.AMOUNT_DUE, INVOICE_AR.CLAIMNUMBER, PATIENT.LASTNAME, PATIENT.FIRSTNAME, PATIENT.EMPLOYERNAME, JOB_OUTCOME.DESCRIPTION, SERVICE_TYPE.DESCRIPTION, PAT_SERVICES_HISTORY.TRANSPORT_TYPE,
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedSuccessfullyItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithComplaintItems', (SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoShowItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoChargeItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithSituationItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Not Completed') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'NotCompletedItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledPriorToServiceItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledDuringServiceItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'AwaitingforcompletionItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Pending for review') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like'%T ')) AS 'PendingforreviewItems'
FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@startate) and DATEADD(year,0,@endate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12))AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%')AND (INVOICE_AR.INVOICE_NO like '%T')
GROUP BY JOB.JURISDICTION, PAYER.PAY_GROUPNAME, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.PATIENTID, JOB.INVOICE_DATE, JOB.JOBOUTCOMEID, JOB.SERVICEOUTCOME, INVOICE_AR.INVOICE_NO, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE, INVOICE_AR.CLAIMNUMBER, PATIENT.LASTNAME, PATIENT.FIRSTNAME, PATIENT.EMPLOYERNAME, JOB_OUTCOME.DESCRIPTION, SERVICE_TYPE.DESCRIPTION, PAT_SERVICES_HISTORY.TRANSPORT_TYPE
UNION ALL
SELECT 'Current Year 2007 All ' as 'qtr', COUNT(JOB.JOBID) AS 'transcount', COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount', SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost', SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost', AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc', SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled', SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT', SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT', SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT', JOB.JURISDICTION, PAYER.PAY_GROUPNAME, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.PATIENTID, JOB.INVOICE_DATE, JOB.JOBOUTCOMEID, JOB.SERVICEOUTCOME, INVOICE_AR.INVOICE_NO, INVOICE_AR.INVOICE_DATE AS Expr1, INVOICE_AR.AMOUNT_DUE, INVOICE_AR.CLAIMNUMBER, PATIENT.LASTNAME, PATIENT.FIRSTNAME, PATIENT.EMPLOYERNAME, JOB_OUTCOME.DESCRIPTION, SERVICE_TYPE.DESCRIPTION, PAT_SERVICES_HISTORY.TRANSPORT_TYPE,
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@startDate) and DATEADD(@enddate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedSuccessfullyItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@startdate) and DATEADD(@enddate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithComplaintItems', (SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (startdate) and DATEADD(@enddate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoShowItems',
(SELECT COUNT(JOB.JOBOUTCOMEID) FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') AND (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@startdate) and DATEADD(@enddate)) AND (MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @Company + '%') AND (INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoChargeItems',
&nb
View 8 Replies
View Related
Oct 4, 2006
I just don't have the skills to work this out...maybe there is an SQL guru out there that can solve this:I can't work out how to do a recursive query so I'm using this function:Public Shared Function FindFriendsFriends(ByVal userID As Integer)Dim iUserID As Integer = userIDDim friendAdapter As New TableAdapters.FriendsTableAdapterDim oFriends As New FrontEnd.FriendsFriendsDataTableDim oTemp As New Data.DataTableDim oContainer As New Data.DataTableoFriends = friendAdapter.GetFriendsByUserID(iUserID)For InLoopCounter As Integer = 0 To oFriends.Count - 1oTemp = friendAdapter.GetFriendsByUserID(oFriends.Rows(0)("UserID"))oContainer.Merge(oTemp)NextReturn oContainerEnd FunctionWith this SQL statement in the table adapter is:SELECT tblFriends.FriendHashID, tblFriends.cCreated, tblFriends.UserOwnerID, tblUsers.UserID, tblUsers.Displayname, tblUsers.EmailAddress, tblFriends.RequestStatus, tblFriends.FriendUserID, tblUsers.ProfilePhoto FROM tblFriends INNER JOIN tblUsers ON tblFriends.FriendUserID = tblUsers.UserID WHERE (tblFriends.UserOwnerID = @UserID) AND (tblFriends.RequestStatus = 2) UNION SELECT tblFriends_1.FriendHashID, tblFriends_1.cCreated, tblFriends_1.UserOwnerID, tblUsers_1.UserID, tblUsers_1.Displayname, tblUsers_1.EmailAddress, tblFriends_1.RequestStatus, tblFriends_1.FriendUserID, tblUsers_1.ProfilePhoto FROM tblFriends AS tblFriends_1 INNER JOIN tblUsers AS tblUsers_1 ON tblFriends_1.UserOwnerID = tblUsers_1.UserID WHERE (tblFriends_1.RequestStatus = 2) AND (tblFriends_1.FriendUserID = @UserID)I want to replace the SQL statement with a recursive query that I simply pass the UserID to and get rid of the function which is very inefficient!
View 2 Replies
View Related
Sep 2, 2004
Ok, here's the problem. I have CA's Unicenter ServiceIT Enterprise Edition 5.0 running on a production box that has NT4 (SP6) and SQL server 2000 running on it.
Just before we continue, this is about backing up the database that ServiceIT connects to.
I know that you can schedule EM to make backup's of a specific database over a certain time, but this is part of the problem. What i have since discovered is that ServiceIT will not run on a database backed up and restored by EM.
It will however run on the restored backup created through the dos command pdm_backup (comes with ServiceIT).
What i am trying to figure out is to try a find a way that i can run a dos prompt command to do the following tasks at a specific time of day:
Stop the Paradigm Server Daemons (service)
Run a DBCC CHECKDB on the database AHDTEST50.
If there are no errors, create a verbose backup using the dos command pdm_backup -v -fC:ackup[todaysdate]
If there are errors run a script to correct them, then run a backup
Restart the Paradigm Server Daemons
View 5 Replies
View Related
Aug 2, 2007
How can i programmatically via Tsql change the datatype of all the columns of a table to varchar(1000)???
Like I have a table employee
Employee
(
colA int
colB int
colC varchar
)
If i run the tsql..
it should give me
Employee
(
colA varchar
colB varchar
colC varchar
)
View 5 Replies
View Related
Jan 8, 2008
Hello guy!
I wrote a stored procedure that searches for user on my table depending on the search option chosen by the person doing the search. But my challenge is displaying users without a picture. On my members table, i used -1 to indicate a user that doesn't have or set his/her picture, this makes it difficult for me to track that person when the person searching chooses to display members with or without pictures. What happen is that my inner join of the photos table dat reference the user primarypictureid does not exist in the photos table. How do i overcome this challenge. My script
CREATE PROCEDURE [dbo].[spSearchMember]
@username nvarchar(30),
@searchfor char(1),
@firstage int,
@secondage int,
@countryid varchar(50),
@withpicture nvarchar(5)
AS
IF(@withpicture = 'False')
SET @withpicture = '%[0-9]'
IF (@withpicture = 'True')
SET @withpicture = '%[0-9][^-]'
IF(@countryId = '-1')
SET @countryid = '%'
BEGIN
SELECT m.UserName, m.MemberId, m.Gender, m.PrimaryPictureId, DATEDIFF(year,m.BirthDate,GETDATE()) AS 'Age',
c.CountryName, p.PicFileName
FROM Members AS m
INNER JOIN Photos AS p
ON m.PrimaryPictureId = p.PictureId
INNER JOIN Countries AS c
ON m.CountryId = c.CountryId
WHERE (m.Gender = @searchfor) AND (DATEDIFF(year,m.BirthDate,GETDATE()) BETWEEN @firstage AND @secondage)
AND (m.CountryId LIKE @countryid) AND (p.PictureId LIKE @withpicture) AND (m.UserName <> @username)
ORDER BY m.BirthDate ASC
END
Thank!!!!!
View 5 Replies
View Related
Aug 26, 2005
I have a challenge for all DBAs. I am getting INCONSISTENT TIMINGS for Querry Results from a SQL Server 2000 Standard Edition on a HP Proliant 2 CPU Server with 4 GB RAM and SCSI DrivAFAe.
Our Database is 72 million records and have 8 columns. Most of them are indexed, which are used in the “Where…� clause. In addition to independent indexes, we also have a covering index for 3 most frequently searched fields.
My challenge is that out of the 7000 odd queries that hit the server with various search conditions in a Week, 5% of the queries return result in less than a minute.
The same query at a different Time or with a different Value, returns results inconsistently. For e.g. searching FirstName = “Anna�; LastName = “Williams� returns result in 0.01 sec. Searching for FirstName “Benjamin�; LastName = “Watson� returns in 5 minutes.
Any kind of help is welcome and will be highly appreciated.
- Santy
san.rely@gmail.comNote: Edited to fix white on white font.
View 1 Replies
View Related
Jun 14, 2001
I have a query that I am trying to optimize. It works on some 9000 records and runs too slow. What the query does is takes the multiple assignment of a single contact record to multiple attributes (a.k.a many-to-many). For example:
Membership table.
Contact_ID
1
2
3
Relate table
1 1
1 3
1 4
Relate Item
1 item1
2 item2
3 item3
4 item4
The query will take all ocurrences of the related items and place them in a single field while delimiting by comma "item1" , "item3", "item4"
Here is the query as it exists now:
select
CONTACT_ID,
UNION_NAME
into #tmp
from MEM_UN MU
inner join MEM_UN_REL MUR
on MU.UNION_ID = MUR.UNION_ID
order by CONTACT_ID, UNION_NAME
create TABLE #unionlist (
CONTACT_ID int primary key,
UNIONS varchar(2000) null)
insert into #unionlist (CONTACT_ID, UNIONS)
select distinct CONTACT_ID, UNIONS = '' from MEMBERSHIP
while exists(select CONTACT_ID from #tmp)
BEGIN
update #unionlist
set UNIONS = UNIONS + '"' + (
select min(UNION_NAME) from #tmp
where #unionlist.CONTACT_ID = #tmp.CONTACT_ID
) + '",'
where CONTACT_ID in (select CONTACT_ID from #tmp)
update #unionlist
set UNIONS = UNIONS + '"",'
where CONTACT_ID not in (select CONTACT_ID FROM #tmp)
delete FROM #tmp where UNION_NAME in (
select min(UNION_NAME) from #tmp tmp2
where #tmp.CONTACT_ID = tmp2.CONTACT_ID
)
END
I believe that the slow down is in the process of deleting from #tmp every time it loops through the recordset.
Any suggestions appreciated,
Thx,
Dave
View 1 Replies
View Related
