Rendering report: replacement

Showing posts with label replacement. Show all posts

Wednesday, March 28, 2012

Replacement Root Query string

anyone can tell me how it is used it in Reportviewer to render
drillthrough links in the same page. I have been experimenting for a
while. would like to know if anything i need to take care of
thanx
raviHere's a thread on this topic:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/fc71a51d020123ed/a295a0d77da59b6c?q=replacementroot&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.reportingsvcs%2Fsearch%3Fq%3Dreplacementroot%26start%3D10%26&_doneTitle=Back+to+Search&&d#a295a0d77da59b6c
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"RemoteDeploy" <bofobofo@.yahoo.com> wrote in message
news:1109378507.771712.185840@.g14g2000cwa.googlegroups.com...
> anyone can tell me how it is used it in Reportviewer to render
> drillthrough links in the same page. I have been experimenting for a
> while. would like to know if anything i need to take care of
> thanx
> ravi
>|||Building on the thread I linked below...
In the code behind the page that contains ReportViewer, you need to set the
ReplacementRoot property, something like this:
reportView.ReplacementRoot = Protocol +
Request.ServerVariables["SERVER_NAME"] + Port + Request.FilePath +
"?report=";
You also have to add a bit more code to strip out extra rc: parameters that
build up as the user clicks on links. I hope that makes sense.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:uWnN6inHFHA.2620@.tk2msftngp13.phx.gbl...
> Here's a thread on this topic:
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/fc71a51d020123ed/a295a0d77da59b6c?q=replacementroot&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.reportingsvcs%2Fsearch%3Fq%3Dreplacementroot%26start%3D10%26&_doneTitle=Back+to+Search&&d#a295a0d77da59b6c
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "RemoteDeploy" <bofobofo@.yahoo.com> wrote in message
> news:1109378507.771712.185840@.g14g2000cwa.googlegroups.com...
>> anyone can tell me how it is used it in Reportviewer to render
>> drillthrough links in the same page. I have been experimenting for a
>> while. would like to know if anything i need to take care of
>> thanx
>> ravi
>|||Hello Jeff,
Thanks for the reply. And I will make sure I dont create multiple
threads of the same topic.
Sadly, the problem still exists. I havent had time to work much on the
interface for the past week or so. I tried all you have said and also
looked at your past posts regarding this topic. I am send the code I
have regarding this whole thing. If you can tell where the problem is i
will appreciate very much.
I have a treeview control, which uses the ListChildren method of RS web
service to get all the reports listed as a tree. When I click on a
report (or rather the selected node changes) the following event is
fired.
BEGIN SUB---
Private Sub TreeView1_SelectedIndexChange(ByVal sender As Object, ByVal
e As Microsoft.Web.UI.WebControls.TreeViewSelectEventArgs) Handles
TreeView1.SelectedIndexChange
Dim curnode As Microsoft.Web.UI.WebControls.TreeNode
curnode = TreeView1.GetNodeFromIndex(e.NewNode)
Xflag = True
ReportViewer1.ServerUrl = "http://serverIP/ReportServer"
ReportViewer1.ReportPath = curnode.ID
ReportViewer1.Toolbar = ReportViewer.multiState.True
ReportViewer1.Zoom = "95"
ReportViewer1.ReplacementRoot =Server.UrlEncode("http://localhost/Reportdashboard/default.aspx?Report=")
If (ReportViewer1.ReportPath.IndexOf("&rc") > 0) Then
ReportViewer1.ReportPath =ReportViewer1.ReportPath.Substring(0,
ReportViewer1.ReportPath.IndexOf("&rc"))
End If
ReportViewer1.Visible = True
END SUB.--
And this is the code i use to populate the Treeview will loading the
default page
BEGIN SUB--
Private Sub LoadSampleReports()
Dim rs As New ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim newNode As Microsoft.Web.UI.WebControls.TreeNode
For Each item In rs.ListChildren("/PGR1", False)
If item.Type = ItemTypeEnum.Report Then
newNode = New Microsoft.Web.UI.WebControls.TreeNode
newNode.Text = item.Name
newNode.ID = item.Path
newNode.Type = item.Type.ToString
newNode.HoverStyle.CssText ="font-size:8pt;color:dimgray;font-family:MS Sans
Serif;font-weight:bold;"
newNode.DefaultStyle.CssText = "font:MS Sans
Serif;color:Indigo; background:white;font-size:8pt"
& "/default.aspx?/PGR1/Averiguaciones" &
"&rc:LinkTarget=Myframe"
newNode.Expanded = False
newNode.Expandable =Microsoft.Web.UI.WebControls.ExpandableValue.Auto
TreeView1.Nodes.Add(newNode)
End If
Next
End Sub
END SUB---
When I click on a drillthrough link, it is opening the default page
again but without the report.
and the following is the URL associated with a drillthrough link
--
http://localhost/Reportdashboard/default.aspx?Report=http%3a%2f%2fserverIP%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_O2%3dAAAJ710623%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252flocalhost%252fReportdashboard%252fdefault.aspx%253fReport%253d
--
Reportdashboard is the Web app.
Thanks very much
Ravi|||Okay, here's a tangent. If you test the reports in the report manager, does
the drill through work correctly?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ravi R" <bofobofo@.yahoo.com> wrote in message
news:1109898353.744329.74340@.z14g2000cwz.googlegroups.com...
> Hello Jeff,
> Thanks for the reply. And I will make sure I dont create multiple
> threads of the same topic.
> Sadly, the problem still exists. I havent had time to work much on the
> interface for the past week or so. I tried all you have said and also
> looked at your past posts regarding this topic. I am send the code I
> have regarding this whole thing. If you can tell where the problem is i
> will appreciate very much.
> I have a treeview control, which uses the ListChildren method of RS web
> service to get all the reports listed as a tree. When I click on a
> report (or rather the selected node changes) the following event is
> fired.
> BEGIN SUB---
> Private Sub TreeView1_SelectedIndexChange(ByVal sender As Object, ByVal
> e As Microsoft.Web.UI.WebControls.TreeViewSelectEventArgs) Handles
> TreeView1.SelectedIndexChange
> Dim curnode As Microsoft.Web.UI.WebControls.TreeNode
> curnode = TreeView1.GetNodeFromIndex(e.NewNode)
> Xflag = True
> ReportViewer1.ServerUrl = "http://serverIP/ReportServer"
> ReportViewer1.ReportPath = curnode.ID
> ReportViewer1.Toolbar = ReportViewer.multiState.True
> ReportViewer1.Zoom = "95"
> ReportViewer1.ReplacementRoot => Server.UrlEncode("http://localhost/Reportdashboard/default.aspx?Report=")
> If (ReportViewer1.ReportPath.IndexOf("&rc") > 0) Then
> ReportViewer1.ReportPath => ReportViewer1.ReportPath.Substring(0,
> ReportViewer1.ReportPath.IndexOf("&rc"))
> End If
> ReportViewer1.Visible = True
> END SUB.--
> And this is the code i use to populate the Treeview will loading the
> default page
> BEGIN SUB--
> Private Sub LoadSampleReports()
> Dim rs As New ReportingService
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> Dim newNode As Microsoft.Web.UI.WebControls.TreeNode
> For Each item In rs.ListChildren("/PGR1", False)
> If item.Type = ItemTypeEnum.Report Then
> newNode = New Microsoft.Web.UI.WebControls.TreeNode
> newNode.Text = item.Name
> newNode.ID = item.Path
> newNode.Type = item.Type.ToString
> newNode.HoverStyle.CssText => "font-size:8pt;color:dimgray;font-family:MS Sans
> Serif;font-weight:bold;"
> newNode.DefaultStyle.CssText = "font:MS Sans
> Serif;color:Indigo; background:white;font-size:8pt"
> & "/default.aspx?/PGR1/Averiguaciones" &
> "&rc:LinkTarget=Myframe"
> newNode.Expanded = False
> newNode.Expandable => Microsoft.Web.UI.WebControls.ExpandableValue.Auto
> TreeView1.Nodes.Add(newNode)
> End If
> Next
> End Sub
> END SUB---
> When I click on a drillthrough link, it is opening the default page
> again but without the report.
> and the following is the URL associated with a drillthrough link
> --
> http://localhost/Reportdashboard/default.aspx?Report=http%3a%2f%2fserverIP%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_O2%3dAAAJ710623%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252flocalhost%252fReportdashboard%252fdefault.aspx%253fReport%253d
> --
> Reportdashboard is the Web app.
>
> Thanks very much
> Ravi
>|||Hello Jeff,
Yes the drillthrough and the drilldown both work fine in the
ReportManager.
Thanks
Ravi|||Hello Jeff,
The following are the URLs First one from the ReportManager where the
drillthrough works. And the second one from my web app.
The reportserver is running on another machine.
http://servename/Reports/Pages/Report.aspx?ServerUrl=http%3a%2f%2fservername%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_1%3dAACA7113%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252fservername%252fReports%252fPages%252fReport.aspx%253fServerUrl%253d
------
http://localhost/Reportdashboard/default.aspx?ServerUrl=http%3a%2f%2fservername%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_1%3dAACA7113%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252flocalhost%252fReportdashboard%252fdefault.aspx%253fServerUrl%253d
------
Thanks
Ravisql

Replacement of values within a string

Hi,
I would like to find out what function I would need to
call to replace characters other than asterisks (*) in a
string value.
For example, I have values in a column in a table like
********SFWE, *****100****, *****200****, *****300****,
and *****400****. I now want these values to be
represented as ********YYYY, *****YYY****, *****YYY****,
*****YYY****, respectively. I want all non-asterisks
value in the string to be replaced with 'Y'.
Thank you in advance,
bpdeeTake a look at CharIndex...
Rick
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
> Hi,
> I would like to find out what function I would need to
> call to replace characters other than asterisks (*) in a
> string value.
> For example, I have values in a column in a table like
> ********SFWE, *****100****, *****200****, *****300****,
> and *****400****. I now want these values to be
> represented as ********YYYY, *****YYY****, *****YYY****,
> *****YYY****, respectively. I want all non-asterisks
> value in the string to be replaced with 'Y'.
> Thank you in advance,
> bpdee|||Hi Rick,
Thank you for your response. Although, I would not know
other than it is a non-asterisk value that is in the
string that I need to replace with the value of 'Y'. It
could be any alphanumeric value and any combination of it
that is in the string that I need to replace. It could
also be anywhere in the string.
Thanks,
bpdee
>--Original Message--
>Take a look at CharIndex...
>Rick
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
>> Hi,
>> I would like to find out what function I would need to
>> call to replace characters other than asterisks (*) in a
>> string value.
>> For example, I have values in a column in a table like
>> ********SFWE, *****100****, *****200****, *****300****,
>> and *****400****. I now want these values to be
>> represented as ********YYYY, *****YYY****, *****YYY****,
>> *****YYY****, respectively. I want all non-asterisks
>> value in the string to be replaced with 'Y'.
>> Thank you in advance,
>> bpdee
>
>.
>|||You probably want to do something like this. I've got the first part of the
replace working, you can probably figure out the second part. You might
want to put this into a function...
The last column in the example is what you're after; the others are so you
can follow what I'm doing.
declare @.myfield varchar(100)
set @.myfield = '*****100****'
select CHARINDEX('*', @.myfield),
PATINDEX('%[^*]%', @.myfield)-1,
RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1), -- you might want to
put 100 Y's into the string at the start - depending on what you expect to
have in your field
STUFF(@.myfield, CHARINDEX('*', @.myfield), PATINDEX('%[^*]%', @.myfield)-1,
RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1))
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
> Hi,
> I would like to find out what function I would need to
> call to replace characters other than asterisks (*) in a
> string value.
> For example, I have values in a column in a table like
> ********SFWE, *****100****, *****200****, *****300****,
> and *****400****. I now want these values to be
> represented as ********YYYY, *****YYY****, *****YYY****,
> *****YYY****, respectively. I want all non-asterisks
> value in the string to be replaced with 'Y'.
> Thank you in advance,
> bpdee|||Hi Andre,
Thank you for your response. You are correct that the
second one is the one I need to solve my problem. I ran
the select statement against my database and it is
definitely closer to what I need. Although, the result is
actually the opposite of what I want. The result I got
is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
want to keep all asterisks and replace those in the string
value that is NOT an asterisk with 'Y'. How would I do
that using the select you sent me?
Thanks,
bpdee
>--Original Message--
>You probably want to do something like this. I've got
the first part of the
>replace working, you can probably figure out the second
part. You might
>want to put this into a function...
>The last column in the example is what you're after; the
others are so you
>can follow what I'm doing.
>declare @.myfield varchar(100)
>set @.myfield = '*****100****'
>select CHARINDEX('*', @.myfield),
> PATINDEX('%[^*]%', @.myfield)-1,
> RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1), --
you might want to
>put 100 Y's into the string at the start - depending on
what you expect to
>have in your field
> STUFF(@.myfield, CHARINDEX('*', @.myfield), PATINDEX('%
[^*]%', @.myfield)-1,
>RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1))
>Andre
>
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
>> Hi,
>> I would like to find out what function I would need to
>> call to replace characters other than asterisks (*) in a
>> string value.
>> For example, I have values in a column in a table like
>> ********SFWE, *****100****, *****200****, *****300****,
>> and *****400****. I now want these values to be
>> represented as ********YYYY, *****YYY****, *****YYY****,
>> *****YYY****, respectively. I want all non-asterisks
>> value in the string to be replaced with 'Y'.
>> Thank you in advance,
>> bpdee
>
>.
>|||Hi,
You can probably use ASCII function to determine the ASCII value of *...
CREATE TABLE #temp
(a1 int, a2 char(1), a3 char(1))
DECLARE @.position int, @.string char(12)
SET @.position = 1
SET @.string = '********SFWE'
WHILE @.position <= DATALENGTH(@.string)
BEGIN
INSERT INTO #temp SELECT ASCII(SUBSTRING(@.string, @.position, 1)) a1,
CHAR(ASCII(SUBSTRING(@.string, @.position, 1))) a2, 'Y' a3
SET @.position = @.position + 1
END
SELECT a2,a3 FROM #temp
WHERE a1 NOT IN( 42, 44, 32)
Now I guess you can figure out, how you can replace a2 with a3 and transform
them into columns...
Thanks
GYK
"bpdee" wrote:
> Hi Rick,
> Thank you for your response. Although, I would not know
> other than it is a non-asterisk value that is in the
> string that I need to replace with the value of 'Y'. It
> could be any alphanumeric value and any combination of it
> that is in the string that I need to replace. It could
> also be anywhere in the string.
> Thanks,
> bpdee
>
> >--Original Message--
> >Take a look at CharIndex...
> >
> >Rick
> >
> >"bpdee" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
> >> Hi,
> >>
> >> I would like to find out what function I would need to
> >> call to replace characters other than asterisks (*) in a
> >> string value.
> >>
> >> For example, I have values in a column in a table like
> >> ********SFWE, *****100****, *****200****, *****300****,
> >> and *****400****. I now want these values to be
> >> represented as ********YYYY, *****YYY****, *****YYY****,
> >> *****YYY****, respectively. I want all non-asterisks
> >> value in the string to be replaced with 'Y'.
> >>
> >> Thank you in advance,
> >> bpdee
> >
> >
> >.
> >
>|||Here is some code that should work...
You could make this into a sproc or a function...
DECLARE @.OrgCol varchar(100)
@.Count int,
@.NewCol varchar(100)
SELECT @.OrgCol = ColumnName FROM TableName
SET @.Count = 1
SET @.NewCol = ''
WHILE (@.Count < DATALENGTH(@.OrgCol))
BEGIN
IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
SET @.NewCol = @.NewCol + 'Y'
ELSE
SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
SET @.Count = @.Count + 1
END
-- Write your update statement here or return @.NewCol if a UDF
Rick Sawtell
MCT, MCSD, MCDBA|||Sorry, I didn't read well enough. :)
While I still think functions are efficient and might be a good thing to
use, if I can accomplish what I want in a query, I'll generally pick that
route first. That said, give this a whirl. It worked for me regardless of
whether or not there were asterisks at the beginning or end of the string,
and no matter how many there were. The reason I'd opt for a function is
this isn't very intuitive to understand when you look at it, and you can
probably take a more intuitive approach in a function, like the examples
that have been given by others here.
declare @.myfield varchar(100)
set @.myfield = '******SFWE'
select STUFF(@.myfield, PATINDEX('%[^*]%', @.myfield), 4, RIGHT('YYYYYYYYYY',
(LEN(@.myfield) - ((PATINDEX('%[^*]%', @.myfield)-1) + (PATINDEX('%[^*]%',
REVERSE(@.myfield))-1)))))
I hope this helps.
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:155701c4b627$8638b6a0$a501280a@.phx.gbl...
> Hi Andre,
> Thank you for your response. You are correct that the
> second one is the one I need to solve my problem. I ran
> the select statement against my database and it is
> definitely closer to what I need. Although, the result is
> actually the opposite of what I want. The result I got
> is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
> want to keep all asterisks and replace those in the string
> value that is NOT an asterisk with 'Y'. How would I do
> that using the select you sent me?
> Thanks,
> bpdee
>>--Original Message--
>>You probably want to do something like this. I've got
> the first part of the
>>replace working, you can probably figure out the second
> part. You might
>>want to put this into a function...
>>The last column in the example is what you're after; the
> others are so you
>>can follow what I'm doing.
>>declare @.myfield varchar(100)
>>set @.myfield = '*****100****'
>>select CHARINDEX('*', @.myfield),
>> PATINDEX('%[^*]%', @.myfield)-1,
>> RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1), --
> you might want to
>>put 100 Y's into the string at the start - depending on
> what you expect to
>>have in your field
>> STUFF(@.myfield, CHARINDEX('*', @.myfield), PATINDEX('%
> [^*]%', @.myfield)-1,
>>RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1))
>>Andre
>>
>>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
>> Hi,
>> I would like to find out what function I would need to
>> call to replace characters other than asterisks (*) in a
>> string value.
>> For example, I have values in a column in a table like
>> ********SFWE, *****100****, *****200****, *****300****,
>> and *****400****. I now want these values to be
>> represented as ********YYYY, *****YYY****, *****YYY****,
>> *****YYY****, respectively. I want all non-asterisks
>> value in the string to be replaced with 'Y'.
>> Thank you in advance,
>> bpdee
>>
>>.|||Hi Rick,
Thank you so much, Rick! This did the trick.
Thanks again,
Bettina
"Rick Sawtell" wrote:
> Here is some code that should work...
> You could make this into a sproc or a function...
> DECLARE @.OrgCol varchar(100)
> @.Count int,
> @.NewCol varchar(100)
> SELECT @.OrgCol = ColumnName FROM TableName
> SET @.Count = 1
> SET @.NewCol = ''
> WHILE (@.Count < DATALENGTH(@.OrgCol))
> BEGIN
> IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
> SET @.NewCol = @.NewCol + 'Y'
> ELSE
> SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
> SET @.Count = @.Count + 1
> END
> -- Write your update statement here or return @.NewCol if a UDF
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Replacement of values within a string

While I still think functions are efficient and might be a good thing to
use, if I can accomplish what I want in a query, I'll generally pick that
route first. That said, give this a whirl. It worked for me regardless of
whether or not there were asterisks at the beginning or end of the string,
and no matter how many there were. The reason I'd opt for a function is
this isn't very intuitive to understand when you look at it, and you can
probably take a more intuitive approach in a function, like the examples
that have been given by others here.
declare @.myfield varchar(100)
set @.myfield = '******SFWE'
select STUFF(@.myfield, PATINDEX('%[^*]%', @.myfield), 4, RIGHT('YYYYYYYYYY',
(LEN(@.myfield) - ((PATINDEX('%[^*]%', @.myfield)-1) + (PATINDEX('%[^*]%',
REVERSE(@.myfield))-1)))))
I hope this helps.
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:155701c4b627$8638b6a0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi Andre,
> Thank you for your response. You are correct that the
> second one is the one I need to solve my problem. I ran
> the select statement against my database and it is
> definitely closer to what I need. Although, the result is
> actually the opposite of what I want. The result I got
> is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
> want to keep all asterisks and replace those in the string
> value that is NOT an asterisk with 'Y'. How would I do
> that using the select you sent me?
> Thanks,
> bpdee
> the first part of the
> part. You might
> others are so you
> you might want to
> what you expect to
> [^*]%', @.myfield)-1,
> message
|||Hi Rick,
Thank you so much, Rick! This did the trick.
Thanks again,
Bettina
"Rick Sawtell" wrote:

> Here is some code that should work...
> You could make this into a sproc or a function...
> DECLARE @.OrgCol varchar(100)
> @.Count int,
> @.NewCol varchar(100)
> SELECT @.OrgCol = ColumnName FROM TableName
> SET @.Count = 1
> SET @.NewCol = ''
> WHILE (@.Count < DATALENGTH(@.OrgCol))
> BEGIN
> IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
> SET @.NewCol = @.NewCol + 'Y'
> ELSE
> SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
> SET @.Count = @.Count + 1
> END
> -- Write your update statement here or return @.NewCol if a UDF
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Replacement of values within a string

While I still think functions are efficient and might be a good thing to
use, if I can accomplish what I want in a query, I'll generally pick that
route first. That said, give this a whirl. It worked for me regardless of
whether or not there were asterisks at the beginning or end of the string,
and no matter how many there were. The reason I'd opt for a function is
this isn't very intuitive to understand when you look at it, and you can
probably take a more intuitive approach in a function, like the examples
that have been given by others here.
declare @.myfield varchar(100)
set @.myfield = '******SFWE'
select STUFF(@.myfield, PATINDEX('%[^*]%', @.myfield), 4, RIGHT('YYYYYYYYY
Y',
(LEN(@.myfield) - ((PATINDEX('%[^*]%', @.myfield)-1) + (PATINDEX('%[^*
]%',
REVERSE(@.myfield))-1)))))
I hope this helps.
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:155701c4b627$8638b6a0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi Andre,
> Thank you for your response. You are correct that the
> second one is the one I need to solve my problem. I ran
> the select statement against my database and it is
> definitely closer to what I need. Although, the result is
> actually the opposite of what I want. The result I got
> is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
> want to keep all asterisks and replace those in the string
> value that is NOT an asterisk with 'Y'. How would I do
> that using the select you sent me?
> Thanks,
> bpdee
>
> the first part of the
> part. You might
> others are so you
> you might want to
> what you expect to
> [^*]%', @.myfield)-1,
> message|||Hi Rick,
Thank you so much, Rick! This did the trick.
Thanks again,
Bettina
"Rick Sawtell" wrote:

> Here is some code that should work...
> You could make this into a sproc or a function...
> DECLARE @.OrgCol varchar(100)
> @.Count int,
> @.NewCol varchar(100)
> SELECT @.OrgCol = ColumnName FROM TableName
> SET @.Count = 1
> SET @.NewCol = ''
> WHILE (@.Count < DATALENGTH(@.OrgCol))
> BEGIN
> IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
> SET @.NewCol = @.NewCol + 'Y'
> ELSE
> SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
> SET @.Count = @.Count + 1
> END
> -- Write your update statement here or return @.NewCol if a UDF
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Replacement of functions by procedures for SQL 7.0

Hello to everybody. I have problems with porting my sql function to elder version of SQL server.
7.0 doesnt have functions at all

Here is fragment of function I used before:

SELECT @.MessageCount = COUNT(RecordID)
FROM Msgs
WHERE dbo.GetGroup(Type) = dbo.GetGroup(@.Type)
AND RecordID <> @.RecordID
AND Receiver = @.Receiver
AND StartDate IS not NULL
AND dbo.CheckDateTime(
dbo.ExtractDate(@.StartDate),
dbo.ExtractDate(@.EndDate),
dbo.ExtractTime(@.StartDate),
dbo.ExtractTime(@.EndDate),
dbo.ExtractDate(StartDate),
dbo.ExtractDate(EndDate),
dbo.ExtractTime(StartDate),
dbo.ExtractTime(EndDate),
dbo.UsesTime(@.Type),
dbo.UsesTime([Type])) = 1

RETURN @.MessageCount

I replaced most of functions used in query with procedures. But how to implement query in better way if I cannot use functions?You should use scalar UDFs sparingly especially in SELECT statements. It hurts performance, limits plan choices and complicates performance troubleshooting. And using scalar UDFs for simple expressions is even more problematic. So remove the UDFs and replace them with the expression directly so it will work in any version of SQL Server and you can get the best performance also.

Replacement of functions by procedures for SQL 7.0

Hello to everybody. I have problems with porting my sql function to elder version of SQL server.
7.0 doesnt have functions at all

Here is fragment of function I used before:

RETURN @.MessageCount

Replacement of C code with C# code

All - I am looking for a starting point on references for migrating a DLL
written in C (and the associated XP that goes with it) to C#. I would like
to be able to use the managed code that SQL has instead of keeping the XP (a
s
it has ran into some security issues, and there is always that possibility
that it could blow up and make me restart SQL). Any good web references or
books (well maybe the books are not out yet)? Many thanks.Why? C is portable, highly optimizable and C# is just proprietary
without much history for good compilation.
If you really need a secure system, then you need get away from MS
products and move to UNIX family products designed by that. .|||Better yet, just write it all in assembler. Be a man!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138242313.806455.76760@.f14g2000cwb.googlegroups.com...
> Why? C is portable, highly optimizable and C# is just proprietary
> without much history for good compilation.
>
> If you really need a secure system, then you need get away from MS
> products and move to UNIX family products designed by that. .
>|||Hi Al,
You probably need to write a stored procedure using CLR; its not that
difficult - significantly easier than extended stored procedures and you
don't have to worry about bringing SQL Server so long as you use SAFE or
EXTERNAL.
This topic area in MSDN has a lot of what you need to get started :
http://msdn2.microsoft.com/en-us/library/ms254498.aspx
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Al" <Al@.discussions.microsoft.com> wrote in message
news:B9E55A17-6D2F-4252-9637-5E8740F40988@.microsoft.com...
> All - I am looking for a starting point on references for migrating a DLL
> written in C (and the associated XP that goes with it) to C#. I would
> like
> to be able to use the managed code that SQL has instead of keeping the XP
> (as
> it has ran into some security issues, and there is always that possibility
> that it could blow up and make me restart SQL). Any good web references
> or
> books (well maybe the books are not out yet)? Many thanks.|||So you advocate using extended stored procedures?

> C# is just proprietary
> without much history for good compilation.
LOL - I guess you've only just started to realise that C# is a programming
language rather than a key on a piano.
C# has been around for a number of years and is extensively used by
programmers using the .NET platform.
Unlike C, C#, specifically the .NET platform has lots of security built-in
to prevent you coding buffer overflows, helps you by disposing memory that
in C you can forget to do, won't let you create pointers to memory you don't
own etc... etc...
As for security, I feel a lot more comfortable using the MS platform because
its the widest used platform on the planent and as such is the main hackers
target, I don't see many security holes coming out anymore - the majority
have been plugged. UNIX on the other hand, there are lots of holes, and when
one is found it takes forever to get a fix; with MS its usually days or at
the latest w

s.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138242313.806455.76760@.f14g2000cwb.googlegroups.com...
> Why? C is portable, highly optimizable and C# is just proprietary
> without much history for good compilation.
>
> If you really need a secure system, then you need get away from MS
> products and move to UNIX family products designed by that. .
>|||No, be a real man - write it directly in binary!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jeff" <A@.B.COM> wrote in message
news:e2Lu7MjIGHA.668@.TK2MSFTNGP11.phx.gbl...
> Better yet, just write it all in assembler. Be a man!
>
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1138242313.806455.76760@.f14g2000cwb.googlegroups.com...
>|||Real men don't even use any I/O devices, other than themselves. Punching in
+/- 3 Volts is only a pain for the first thousand or so ticks.
ML
http://milambda.blogspot.com/|||Here we go; propagating the myth the UNIX is more secure than Windows.
Let's see, UNIX gets it start in the 1960s, and has been hacked away at for
nearly half a century. Windows NT born around 1994. Getting the drift?
Go to www.cert.org and look at historical vulnerability data. Just as many
(if not more) items concerning UNIX/Linux as Windows in the past 10 years.
Mark Williams, MCSE: Security, MCDBA
"--CELKO--" wrote:

> Why? C is portable, highly optimizable and C# is just proprietary
> without much history for good compilation.
>
> If you really need a secure system, then you need get away from MS
> products and move to UNIX family products designed by that. .
>|||"Tony Rogerson":

> So you advocate using extended stored procedures?
>
> LOL - I guess you've only just started to realise that C# is a programming
> language rather than a key on a piano.
> C# has been around for a number of years and is extensively used by
> programmers using the .NET platform.
> Unlike C, C#, specifically the .NET platform has lots of security built-in
> to prevent you coding buffer overflows, helps you by disposing memory that
> in C you can forget to do, won't let you create pointers to memory you
> don't own etc... etc...
In addition (ignoring the fact that an XP for SQL Server written in C isn't
very portable), C#, C++/CLI, and the CLI are all ECMA standards. The ECMA
standards for C# and the CLI are alreeady ISO standards and C++/CLI is
supposed to be fast-tracked.
Craig|||Consider hooking the DLL into a client side application or on a designated
application server. Any executable that interacts with external services can
misbehave, regardless of the language, compiler or OS platform.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:B9E55A17-6D2F-4252-9637-5E8740F40988@.microsoft.com...
> All - I am looking for a starting point on references for migrating a DLL
> written in C (and the associated XP that goes with it) to C#. I would
> like
> to be able to use the managed code that SQL has instead of keeping the XP
> (as
> it has ran into some security issues, and there is always that possibility
> that it could blow up and make me restart SQL). Any good web references
> or
> books (well maybe the books are not out yet)? Many thanks.

replacement of *= in 2005

Hi,
We are migrating to 2005 and there are some old queries that use *= in the
sql. Is it no more used?
Whats the other way to get the same functionality?
ThanksCheck out LEFT OUTER JOIN in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"mavrick_101" <mavrick101@.discussions.microsoft.com> wrote in message
news:C3A7A6B0-2CCD-4EB3-B807-23A692DCC44F@.microsoft.com...
Hi,
We are migrating to 2005 and there are some old queries that use *= in the
sql. Is it no more used?
Whats the other way to get the same functionality?
Thanks|||Are you familiar with LEFT OUTER JOIN syntax? That's what you should be
using.
"mavrick_101" <mavrick101@.discussions.microsoft.com> wrote in message
news:C3A7A6B0-2CCD-4EB3-B807-23A692DCC44F@.microsoft.com...
> Hi,
> We are migrating to 2005 and there are some old queries that use *= in the
> sql. Is it no more used?
> Whats the other way to get the same functionality?
> Thanks|||> We are migrating to 2005 and there are some old queries that use *= in the
> sql. Is it no more used?
Set database compatibility level (sp_dbcmptlevel) other than 90 (70, 80)

> Whats the other way to get the same functionality?
Left outer join (* =)
Right outer join (= *)
Full outer join (* = *)
AMB
"mavrick_101" wrote:

> Hi,
> We are migrating to 2005 and there are some old queries that use *= in the
> sql. Is it no more used?
> Whats the other way to get the same functionality?
> Thanks|||use left outer join
"mavrick_101" wrote:

> Hi,
> We are migrating to 2005 and there are some old queries that use *= in the
> sql. Is it no more used?
> Whats the other way to get the same functionality?
> Thanks|||>> Full outer join (* = *)
' I doubt if it was there.
Anith|||Thanks Anith.
AMB
"Anith Sen" wrote:

> ' I doubt if it was there.
> --
> Anith
>
>

Replacement for xp_cmdshell in SQL2k

ISSUE (in SQL Server 2000): Some Client applicationsâ' stored procedures are
using â'xp_cmdshellâ'.
xp_cmdshell is an extended stored procedure in Master database. This command
takes a string of dos commands and executes them in the DOS command shell.
Now the Client wants a replacement for this xp_cmdshell . i.e., I need some
other function or procedure or something which I can place instead of this
xp_cmdshell in the respective stored procedures using this xp_cmdshell.
Note: The Client doesnâ't want to use this xp_cmdshell at all.The client probably heard that having xp_cmdShell enabled adds to your
security risk, and wants a safe way to do the same things they have been
doing.
What have they been doing with xp_cmdshell? There may be ways to replace
that functionality, there may not.
You could create jobs that execute cmdexec steps, and have the stored
procedures activate the jobs.
Please provide more info about what they do and why they want to discontinue
using xp_cmdshell.
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> are
> using "xp_cmdshell".
> xp_cmdshell is an extended stored procedure in Master database. This
> command
> takes a string of dos commands and executes them in the DOS command shell.
> Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> some
> other function or procedure or something which I can place instead of this
> xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> Note: The Client doesn't want to use this xp_cmdshell at all.
>|||The solution depends on what you are executing via xp_cmdshell. One
approach is to create a new extended stored procedure to encapsulate the
needed functionality.
Another method is to move the processing out of SQL Server and into
application code, either on the client or on a server. It is likely that
this is the better approach if your client is concerned about security.
Hope this helps.
Dan Guzman
SQL Server MVP
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> are
> using "xp_cmdshell".
> xp_cmdshell is an extended stored procedure in Master database. This
> command
> takes a string of dos commands and executes them in the DOS command shell.
> Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> some
> other function or procedure or something which I can place instead of this
> xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> Note: The Client doesn't want to use this xp_cmdshell at all.
>|||Thank You for your response.
It is because of some security reasons the Client does not want any non-DBA
to use "xp_cmdshell" command.
This command takes a string as input and executes the same in the DOS
command shell. The string is nothing but a valid DOS command.
Example: Exec xp_cmdshell 'dir *.*'
Similarly as above my requirement has FTP, move, jview, copy, del, bcp etc.,
DOS commands as inputs to this extended stored procedure in the form of a
string.
I hope I explained it more clearly to you now.
-Aizaz
"David Lundell" wrote:
> The client probably heard that having xp_cmdShell enabled adds to your
> security risk, and wants a safe way to do the same things they have been
> doing.
> What have they been doing with xp_cmdshell? There may be ways to replace
> that functionality, there may not.
> You could create jobs that execute cmdexec steps, and have the stored
> procedures activate the jobs.
> Please provide more info about what they do and why they want to discontinue
> using xp_cmdshell.
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> > ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> > are
> > using "xp_cmdshell".
> >
> > xp_cmdshell is an extended stored procedure in Master database. This
> > command
> > takes a string of dos commands and executes them in the DOS command shell.
> >
> > Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> > some
> > other function or procedure or something which I can place instead of this
> > xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> >
> > Note: The Client doesn't want to use this xp_cmdshell at all.
> >
>
>|||Thank You for your response.
A string of DOS commands is the input "xp_cmdshell".
Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
Which means we need some way in which we can take a string (which is a valid
DOS command) and execute it in DOS command shell (which is the functionality
of xp_cmdshell)
"Dan Guzman" wrote:
> The solution depends on what you are executing via xp_cmdshell. One
> approach is to create a new extended stored procedure to encapsulate the
> needed functionality.
> Another method is to move the processing out of SQL Server and into
> application code, either on the client or on a server. It is likely that
> this is the better approach if your client is concerned about security.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> > ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> > are
> > using "xp_cmdshell".
> >
> > xp_cmdshell is an extended stored procedure in Master database. This
> > command
> > takes a string of dos commands and executes them in the DOS command shell.
> >
> > Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> > some
> > other function or procedure or something which I can place instead of this
> > xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> >
> > Note: The Client doesn't want to use this xp_cmdshell at all.
> >
>
>|||> Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
So why are you using xp_cmdshell for this task? Is there some reason you
cannot perform file manipulation like this directly from you application
code?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:B6A188BB-55FD-48C7-9A0F-6451EC953F7F@.microsoft.com...
> Thank You for your response.
> A string of DOS commands is the input "xp_cmdshell".
> Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
> Which means we need some way in which we can take a string (which is a
> valid
> DOS command) and execute it in DOS command shell (which is the
> functionality
> of xp_cmdshell)
> "Dan Guzman" wrote:
>> The solution depends on what you are executing via xp_cmdshell. One
>> approach is to create a new extended stored procedure to encapsulate the
>> needed functionality.
>> Another method is to move the processing out of SQL Server and into
>> application code, either on the client or on a server. It is likely that
>> this is the better approach if your client is concerned about security.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
>> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
>> > ISSUE (in SQL Server 2000): Some Client applications' stored procedures
>> > are
>> > using "xp_cmdshell".
>> >
>> > xp_cmdshell is an extended stored procedure in Master database. This
>> > command
>> > takes a string of dos commands and executes them in the DOS command
>> > shell.
>> >
>> > Now the Client wants a replacement for this xp_cmdshell . i.e., I need
>> > some
>> > other function or procedure or something which I can place instead of
>> > this
>> > xp_cmdshell in the respective stored procedures using this xp_cmdshell.
>> >
>> > Note: The Client doesn't want to use this xp_cmdshell at all.
>> >
>>|||There is some processing done in a stored procedure and this extended stored
procedure is called after that. Once the xp completes its job, some more
processing done in the original stored procedure.
Also there are many stored procedures which internally call xp for different
tasks like bulk copy etc. Most of these stored procedures are scheduled SQL
tasks and are not called from any application. So replacing this
functionality in application code can be ruled out.
Thanks for your inputs/suggestions...
"Dan Guzman" wrote:
> > Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
> So why are you using xp_cmdshell for this task? Is there some reason you
> cannot perform file manipulation like this directly from you application
> code?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> news:B6A188BB-55FD-48C7-9A0F-6451EC953F7F@.microsoft.com...
> > Thank You for your response.
> >
> > A string of DOS commands is the input "xp_cmdshell".
> >
> > Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
> >
> > Which means we need some way in which we can take a string (which is a
> > valid
> > DOS command) and execute it in DOS command shell (which is the
> > functionality
> > of xp_cmdshell)
> >
> > "Dan Guzman" wrote:
> >
> >> The solution depends on what you are executing via xp_cmdshell. One
> >> approach is to create a new extended stored procedure to encapsulate the
> >> needed functionality.
> >>
> >> Another method is to move the processing out of SQL Server and into
> >> application code, either on the client or on a server. It is likely that
> >> this is the better approach if your client is concerned about security.
> >>
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> >> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> >> > ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> >> > are
> >> > using "xp_cmdshell".
> >> >
> >> > xp_cmdshell is an extended stored procedure in Master database. This
> >> > command
> >> > takes a string of dos commands and executes them in the DOS command
> >> > shell.
> >> >
> >> > Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> >> > some
> >> > other function or procedure or something which I can place instead of
> >> > this
> >> > xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> >> >
> >> > Note: The Client doesn't want to use this xp_cmdshell at all.
> >> >
> >>
> >>
> >>
>
>|||You might consider splitting the stored procedures that currently execute
xp_cmdshell into separate procs. This would allow you to replace
xp_cmdshell functionality with a CmdExec job step. For example:
1) Transact-SQL step: EXEC proc 1
2) CmdExec step: BCP
1) Transact-SQL step: EXEC proc 2
Your client might be more receptive to this method since the command runs
outside the SQL Server service process.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:0299E709-4A85-4158-B9A6-E62B1722765A@.microsoft.com...
>
> There is some processing done in a stored procedure and this extended
> stored
> procedure is called after that. Once the xp completes its job, some more
> processing done in the original stored procedure.
> Also there are many stored procedures which internally call xp for
> different
> tasks like bulk copy etc. Most of these stored procedures are scheduled
> SQL
> tasks and are not called from any application. So replacing this
> functionality in application code can be ruled out.
> Thanks for your inputs/suggestions...
> "Dan Guzman" wrote:
>> > Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
>> So why are you using xp_cmdshell for this task? Is there some reason you
>> cannot perform file manipulation like this directly from you application
>> code?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
>> news:B6A188BB-55FD-48C7-9A0F-6451EC953F7F@.microsoft.com...
>> > Thank You for your response.
>> >
>> > A string of DOS commands is the input "xp_cmdshell".
>> >
>> > Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
>> >
>> > Which means we need some way in which we can take a string (which is a
>> > valid
>> > DOS command) and execute it in DOS command shell (which is the
>> > functionality
>> > of xp_cmdshell)
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> The solution depends on what you are executing via xp_cmdshell. One
>> >> approach is to create a new extended stored procedure to encapsulate
>> >> the
>> >> needed functionality.
>> >>
>> >> Another method is to move the processing out of SQL Server and into
>> >> application code, either on the client or on a server. It is likely
>> >> that
>> >> this is the better approach if your client is concerned about
>> >> security.
>> >>
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
>> >> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
>> >> > ISSUE (in SQL Server 2000): Some Client applications' stored
>> >> > procedures
>> >> > are
>> >> > using "xp_cmdshell".
>> >> >
>> >> > xp_cmdshell is an extended stored procedure in Master database. This
>> >> > command
>> >> > takes a string of dos commands and executes them in the DOS command
>> >> > shell.
>> >> >
>> >> > Now the Client wants a replacement for this xp_cmdshell . i.e., I
>> >> > need
>> >> > some
>> >> > other function or procedure or something which I can place instead
>> >> > of
>> >> > this
>> >> > xp_cmdshell in the respective stored procedures using this
>> >> > xp_cmdshell.
>> >> >
>> >> > Note: The Client doesn't want to use this xp_cmdshell at all.
>> >> >
>> >>
>> >>
>> >>
>>

Replacement for xp_cmdshell in SQL2k

ISSUE (in SQL Server 2000): Some Client applications’ stored procedures ar
e
using “xp_cmdshell”.
xp_cmdshell is an extended stored procedure in Master database. This command
takes a string of dos commands and executes them in the DOS command shell.
Now the Client wants a replacement for this xp_cmdshell . i.e., I need some
other function or procedure or something which I can place instead of this
xp_cmdshell in the respective stored procedures using this xp_cmdshell.
Note: The Client doesn’t want to use this xp_cmdshell at all.The client probably heard that having xp_cmdShell enabled adds to your
security risk, and wants a safe way to do the same things they have been
doing.
What have they been doing with xp_cmdshell? There may be ways to replace
that functionality, there may not.
You could create jobs that execute cmdexec steps, and have the stored
procedures activate the jobs.
Please provide more info about what they do and why they want to discontinue
using xp_cmdshell.
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> are
> using "xp_cmdshell".
> xp_cmdshell is an extended stored procedure in Master database. This
> command
> takes a string of dos commands and executes them in the DOS command shell.
> Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> some
> other function or procedure or something which I can place instead of this
> xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> Note: The Client doesn't want to use this xp_cmdshell at all.
>|||The solution depends on what you are executing via xp_cmdshell. One
approach is to create a new extended stored procedure to encapsulate the
needed functionality.
Another method is to move the processing out of SQL Server and into
application code, either on the client or on a server. It is likely that
this is the better approach if your client is concerned about security.
Hope this helps.
Dan Guzman
SQL Server MVP
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> are
> using "xp_cmdshell".
> xp_cmdshell is an extended stored procedure in Master database. This
> command
> takes a string of dos commands and executes them in the DOS command shell.
> Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> some
> other function or procedure or something which I can place instead of this
> xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> Note: The Client doesn't want to use this xp_cmdshell at all.
>|||Thank You for your response.
It is because of some security reasons the Client does not want any non-DBA
to use "xp_cmdshell" command.
This command takes a string as input and executes the same in the DOS
command shell. The string is nothing but a valid DOS command.
Example: Exec xp_cmdshell 'dir *.*'
Similarly as above my requirement has FTP, move, jview, copy, del, bcp etc.,
DOS commands as inputs to this extended stored procedure in the form of a
string.
I hope I explained it more clearly to you now.
-Aizaz
"David Lundell" wrote:

> The client probably heard that having xp_cmdShell enabled adds to your
> security risk, and wants a safe way to do the same things they have been
> doing.
> What have they been doing with xp_cmdshell? There may be ways to replace
> that functionality, there may not.
> You could create jobs that execute cmdexec steps, and have the stored
> procedures activate the jobs.
> Please provide more info about what they do and why they want to discontin
ue
> using xp_cmdshell.
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
>
>|||Thank You for your response.
A string of DOS commands is the input "xp_cmdshell".
Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
Which means we need some way in which we can take a string (which is a valid
DOS command) and execute it in DOS command shell (which is the functionality
of xp_cmdshell)
"Dan Guzman" wrote:

> The solution depends on what you are executing via xp_cmdshell. One
> approach is to create a new extended stored procedure to encapsulate the
> needed functionality.
> Another method is to move the processing out of SQL Server and into
> application code, either on the client or on a server. It is likely that
> this is the better approach if your client is concerned about security.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
>
>|||> Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
So why are you using xp_cmdshell for this task? Is there some reason you
cannot perform file manipulation like this directly from you application
code?
Hope this helps.
Dan Guzman
SQL Server MVP
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:B6A188BB-55FD-48C7-9A0F-6451EC953F7F@.microsoft.com...[vbcol=seagreen]
> Thank You for your response.
> A string of DOS commands is the input "xp_cmdshell".
> Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
> Which means we need some way in which we can take a string (which is a
> valid
> DOS command) and execute it in DOS command shell (which is the
> functionality
> of xp_cmdshell)
> "Dan Guzman" wrote:
>|||There is some processing done in a stored procedure and this extended stored
procedure is called after that. Once the xp completes its job, some more
processing done in the original stored procedure.
Also there are many stored procedures which internally call xp for different
tasks like bulk copy etc. Most of these stored procedures are scheduled SQL
tasks and are not called from any application. So replacing this
functionality in application code can be ruled out.
Thanks for your inputs/suggestions...
"Dan Guzman" wrote:

> So why are you using xp_cmdshell for this task? Is there some reason you
> cannot perform file manipulation like this directly from you application
> code?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> news:B6A188BB-55FD-48C7-9A0F-6451EC953F7F@.microsoft.com...
>
>|||You might consider splitting the stored procedures that currently execute
xp_cmdshell into separate procs. This would allow you to replace
xp_cmdshell functionality with a CmdExec job step. For example:
1) Transact-SQL step: EXEC proc 1
2) CmdExec step: BCP
1) Transact-SQL step: EXEC proc 2
Your client might be more receptive to this method since the command runs
outside the SQL Server service process.
Hope this helps.
Dan Guzman
SQL Server MVP
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:0299E709-4A85-4158-B9A6-E62B1722765A@.microsoft.com...[vbcol=seagreen]
>
> There is some processing done in a stored procedure and this extended
> stored
> procedure is called after that. Once the xp completes its job, some more
> processing done in the original stored procedure.
> Also there are many stored procedures which internally call xp for
> different
> tasks like bulk copy etc. Most of these stored procedures are scheduled
> SQL
> tasks and are not called from any application. So replacing this
> functionality in application code can be ruled out.
> Thanks for your inputs/suggestions...
> "Dan Guzman" wrote:
>

Replacement for sysprocesses dbid?

In SQLServer 2005, how can you select just the processes that are
connected to a specific database? In 2000, I could do it with this
query:
select *
from sysprocesses
where dbid = 5
BOL recommends using any of the dynamic management views
sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
as a replacement for sysprocesses, but none of these has a database or
dbid column that I can find. Am I overlooking something? How do you
do this? Thanks.select * from sys.dm_exec_requests
where database_id = 5
to just see who is using a database, who might not be running any queries,
you can use sys.dm_tran_locks
select request_session_id from sys.dm_tran_locks
where resource_type = 'DATABASE'
and resource_database_id = 5
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<stavros@.mailinator.com> wrote in message
news:1161904818.742946.108590@.m7g2000cwm.googlegroups.com...
> In SQLServer 2005, how can you select just the processes that are
> connected to a specific database? In 2000, I could do it with this
> query:
> select *
> from sysprocesses
> where dbid = 5
> BOL recommends using any of the dynamic management views
> sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
> as a replacement for sysprocesses, but none of these has a database or
> dbid column that I can find. Am I overlooking something? How do you
> do this? Thanks.
>sql

Replacement for sysprocesses dbid?

In SQLServer 2005, how can you select just the processes that are
connected to a specific database? In 2000, I could do it with this
query:
select *
from sysprocesses
where dbid = 5
BOL recommends using any of the dynamic management views
sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
as a replacement for sysprocesses, but none of these has a database or
dbid column that I can find. Am I overlooking something? How do you
do this? Thanks.select * from sys.dm_exec_requests
where database_id = 5
to just see who is using a database, who might not be running any queries,
you can use sys.dm_tran_locks
select request_session_id from sys.dm_tran_locks
where resource_type = 'DATABASE'
and resource_database_id = 5
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<stavros@.mailinator.com> wrote in message
news:1161904818.742946.108590@.m7g2000cwm.googlegroups.com...
> In SQLServer 2005, how can you select just the processes that are
> connected to a specific database? In 2000, I could do it with this
> query:
> select *
> from sysprocesses
> where dbid = 5
> BOL recommends using any of the dynamic management views
> sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
> as a replacement for sysprocesses, but none of these has a database or
> dbid column that I can find. Am I overlooking something? How do you
> do this? Thanks.
>

Replacement for SQL Query Analyser

Does anybody know if there is one in 2005.
Sure I can run the sp's but can't see how I can step through them.
Thanks
"Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in
news:OdAWjDB8FHA.2576@.TK2MSFTNGP12.phx.gbl:

> Does anybody know if there is one in 2005.
SQL Server Management Studio (SSMS) is the replacement.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||But you cannot debug ..?
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns971772D96ED48olekristianbangaas@.207.46.248 .16...
> "Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in
> news:OdAWjDB8FHA.2576@.TK2MSFTNGP12.phx.gbl:
>
> SQL Server Management Studio (SSMS) is the replacement.
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||>From another thread answered few time ago:
Demystifying Debugging in SQL Server 2005
http://www.windowsitpro.com/SQLServe...754/SQLServ...
4.html
How to: Debug a SQL CLR Stored Procedure
http://msdn2.microsoft.com/en-us/library/ms165051.aspx
How to: Enable SQL Server 2005 Debugging
http://msdn2.microsoft.com/en-us/library/s0fk6z6e.aspx
Creating SQL Server 2005 Objects in Managed Code
http://msdn2.microsoft.com/en-us/library/6s0s2at1.aspx
SQL Server 2005 Debugging
http://www.sqljunkies.com/WebLog/nie...8/04/3758.aspx
HTH, Jens Suessmeyer.
|||Good point,
To debug in SQL 2005 you need Visual Studio.
I have a link on my website that points to the related MSDN article
http://www.dandyman.net/sql/articles.aspx
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
news:%230OTxiB8FHA.1416@.TK2MSFTNGP09.phx.gbl...
> But you cannot debug ..?
>
> "Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
> news:Xns971772D96ED48olekristianbangaas@.207.46.248 .16...
>
|||In VS 2005. New Query from the Server Explorer or within a DB project.
SSMS has the same 4 pane deal, but it works different. It requires, IIRC,
that you design the query and then paste that into a .sql tab/file and run
it.
William Stacey [MVP]
"Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
news:OdAWjDB8FHA.2576@.TK2MSFTNGP12.phx.gbl...
> Does anybody know if there is one in 2005.
> Sure I can run the sp's but can't see how I can step through them.
> Thanks
>

Monday, March 26, 2012

Replacement for SETUSER

Books Online says 'SetUser' is deprecated, but doesn't tell me what the
new way to change user contexts is. Am I not supposed to want to do
this, or is there some other way?
Thanks!
Ionit's execute as.
http://msdn2.microsoft.com/en-us/library/ms181362(SQL.90).aspx
-oj
<ionFreeman@.gmail.com> wrote in message
news:1141934114.006542.247290@.j52g2000cwj.googlegroups.com...
> Books Online says 'SetUser' is deprecated, but doesn't tell me what the
> new way to change user contexts is. Am I not supposed to want to do
> this, or is there some other way?
> Thanks!
> Ion
>|||Thanks, OJ. I'm using SQL Server 2000, however, for which Books online
doesn't mention an 'Execute As'. So, I'll just live with the fact that
it's deprecated in this version, but not replaced until the next.

replacement for report manager

Hi!
I am evaluating how far i can go in implementing repoting features of SRS in
my program.
I want to be able to write a Windows form (in C#) that has a grid/list
containing all the reports that I have made. The list may have been
previously enterd into the database along with other information needed to
run the particular report.
I want to be able to select in the grid/list(e.g. using a check box) which
reports to run, and what format to render each individual report, as well as
other parameters.
Is the Report Manager that extensible? Is it possible to create this sort of
replacement for Report Manager from C#?
I am using VS2005 and SRS 2005.
Thanks in advance!It is absolutely possible... I have written a report for a customer which
searches the reporting services met data database and returns a list of
reports which match the search criteria the customer wants.. It creates a
report which displays the report name, directory and description... Each
report name is a hot link to run the report... This was done in a report!~
Using a programming language you can do anything you wish... There is a
ReportViewer control ( Search for ReportViewer in Books on line.) that you
can use..
There are sample programs in the SQL directory /90 subdirectory which will
help get you started...
Reporting Services is also just a DotNet Web service, with an entire API
which can you call from your program as well.. It is the same API that MS
uses in the Report Manager..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"PacMan" wrote:
> Hi!
> I am evaluating how far i can go in implementing repoting features of SRS in
> my program.
> I want to be able to write a Windows form (in C#) that has a grid/list
> containing all the reports that I have made. The list may have been
> previously enterd into the database along with other information needed to
> run the particular report.
> I want to be able to select in the grid/list(e.g. using a check box) which
> reports to run, and what format to render each individual report, as well as
> other parameters.
> Is the Report Manager that extensible? Is it possible to create this sort of
> replacement for Report Manager from C#?
> I am using VS2005 and SRS 2005.
> Thanks in advance!

Replacement for my LIKE Clause

Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
[vbcol=seagreen]
Why did you avoid clear names?
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ?
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
Can two prefixes belong to the same SAN? No specs given.
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
[vbcol=seagreen]
No. Clear specs would have been nice, along with real DDL.
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
Etc.);
You will need error handling code for prefixes that do not match.
Roy,
Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..
Celko,
Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...
I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...
I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...
Thanks once again...
VM
--CELKO-- wrote:
> Why did you fail to post DDL, screw up the syntax and violate ISO-11179
> naming rules? Probably because you also confuse fields and columns.
> Let's start by cleaning up you code, so it looks like SQL.
> SQL uses single quotes for strings. A data element can be a location or
> an identifier, never both. A transaction is some kind of transaction.
> Etc. You need a data modeling course. Your sample data failed to give
> values of the improperly named 'EmployeeLocationID' - I hope to
> ghod you are not using IDENTITY and thinking that it is a key!!
> Don't you know about SAN and other industry standard address numbers?
>
> Why did you avoid clear names?
> CREATE TABLE LocationCodes
> (loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
> loc_code INTEGER NOT NULL); -- industry SAN ?
> -- put wildcards in the table for indexing
> INSERT INTO LocationCodes VALUES ('B%', 100);
> INSERT INTO LocationCodes VALUES ('BO%', 101);
> INSERT INTO LocationCodes VALUES ('BOM%', 102);
> Etc.
> Can two prefixes belong to the same SAN? No specs given.
> Without a key in that vague transactions table, you do not have a
> proper table at all. I had to make up one. Why do you have employee
> id and not find the employee name via a join to the Personnel table?
> Isn't the idea of RDBMS to get rid of redudant data?
> CREATE TABLE FoobarTrans
> (foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
> -- CHECK (<<needs validation rule here>>),
> emp_id INTEGER NOT NULL
> REFERENCES Personnel(emp_id)
> ON UPDATE CASCADE,
> loc_code INTEGER NOT NULL
> REFERENCES LocationCodes(loc_code)
> ON UPDATE CASCADE,
> Etc.);
> The prefix should have been used when you inserted the initial row (NOT
> field!!!) into the table. Because you are confusing fields and
> columns, files and tables, you are thinking in procedural *steps* with
> updates just like a punch card file, not in sets like an SQL
> programmer.
>
> No. Clear specs would have been nice, along with real DDL.
> Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
> in the VALUES list, but if you have SQL-2005, try this little untested
> statement:
> INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
> VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
> (WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
> FROM LocationCodes AS L1
> WHERE L1.loc_prefix LIKE @.my_loc_prefix)
> AS M(loc_code, fit)
> SELECT loc_code
> FROM M AS M1
> WHERE M1.fit
> = (SELECT MAX(M2.fit) FROM M AS M2)),
> Etc.);
> You will need error handling code for prefixes that do not match.
sql

Replacement for my LIKE Clause

Dear all,
I need some help from all my Transact SQL Guru friends out there..
Here is the scenario in its most simplified form.. ..
I have two tables.. A(Lookup table) and B(Transaction Table)
TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
"B","BO","BOM","C","CA","CALC") etc...
TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
first time)
EmployeeLocation (This could have values
"BA123","BOMBAY","BOTS123","BRACK"... etc)
I hope you get where I am leading this to, from my examples..
Requirement is to populate the EmployeeLocationID in Table B with
EmployeeLocationID from TableA by matching the field EmployeeLocation
in both tables.Please note that table B's EmployeeLocation could be A's
EmployeeLocation + some additionalcodes like "123","RACK" etc in the
above example...
Therefore, this is what I had wrote initially..
update B
set B.EmployeeLocationID =A.EmployeeLocationID
>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
'%'
where B.EmployeeLocationID is null
This works fine alright.. However the trouble is that it doesn't cater
to the complete requirement...
For example the row in Table B with EmployeeLocation as "BOMBAY" will
get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
are earlier rows in table A while comparing..The requirement is that we
should get the EmployeeLocationID of "BOM" in this case... That is,
the comparison should be done first for the maximum "maximum no of
characters" match, then for the next "no of characters" match, then for
the next "no of characters"match... etc...
Therefore this is the expected match for my examples based on
requirement..
"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
of Table A
"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
of Table A
"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
Can someone please help me with my query, or atleast direct me to the
right material so that I can take care of this requirement..
Looking forward to hearing from someone ASAP.. Please help..
Best regards,
VM...Interesting. Maybe this will give you and angle to try.
UPDATE B
SET EmployeeLocationID = (SELECT TOP 1 A.EmployeeLocationID
FROM A
WHERE B.EmployeeLocation LIKE A.EmployeeLocation + '%'
ORDER BY LEN(A.EmployeeLocation) DESC)
WHERE B.EmployeeLocationID IS NULL
Roy Harvey
Beacon Falls, CT
On 27 Dec 2006 15:24:44 -0800, varkey.mathew@.wipro.com wrote:
>Dear all,
>I need some help from all my Transact SQL Guru friends out there..
>Here is the scenario in its most simplified form.. ..
>I have two tables.. A(Lookup table) and B(Transaction Table)
>TableA Fields
> EmployeeLocationID
> EmployeeLocation (This could have values say
>"B","BO","BOM","C","CA","CALC") etc...
>
>TableB Fields
> EmployeeID
> EmployeeName......
> EmployeeLocationID (will have null initially when rows are populated
>first time)
> EmployeeLocation (This could have values
>"BA123","BOMBAY","BOTS123","BRACK"... etc)
>I hope you get where I am leading this to, from my examples..
>Requirement is to populate the EmployeeLocationID in Table B with
>EmployeeLocationID from TableA by matching the field EmployeeLocation
>in both tables.Please note that table B's EmployeeLocation could be A's
>EmployeeLocation + some additionalcodes like "123","RACK" etc in the
>above example...
>Therefore, this is what I had wrote initially..
>update B
>set B.EmployeeLocationID =A.EmployeeLocationID
>>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
>'%'
>where B.EmployeeLocationID is null
>This works fine alright.. However the trouble is that it doesn't cater
>to the complete requirement...
>For example the row in Table B with EmployeeLocation as "BOMBAY" will
>get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
>are earlier rows in table A while comparing..The requirement is that we
>should get the EmployeeLocationID of "BOM" in this case... That is,
>the comparison should be done first for the maximum "maximum no of
>characters" match, then for the next "no of characters" match, then for
>the next "no of characters"match... etc...
>Therefore this is the expected match for my examples based on
>requirement..
>"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
>of Table A
>"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
>of Table A
>"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>
>Can someone please help me with my query, or atleast direct me to the
>right material so that I can take care of this requirement..
>
>Looking forward to hearing from someone ASAP.. Please help..
>Best regards,
>VM...|||Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
>> A(Lookup table) and B(Transaction Table) <<
Why did you avoid clear names?
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN '
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
Can two prefixes belong to the same SAN? No specs given.
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
>> I hope you get where I am leading this to, from my examples.. <<
No. Clear specs would have been nice, along with real DDL.
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
Etc.);
You will need error handling code for prefixes that do not match.|||Roy,
Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..
Celko,
Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...
I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...
I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...
Thanks once again...
VM
--CELKO-- wrote:
> Why did you fail to post DDL, screw up the syntax and violate ISO-11179
> naming rules? Probably because you also confuse fields and columns.
> Let's start by cleaning up you code, so it looks like SQL.
> SQL uses single quotes for strings. A data element can be a location or
> an identifier, never both. A transaction is some kind of transaction.
> Etc. You need a data modeling course. Your sample data failed to give
> values of the improperly named 'EmployeeLocationID' - I hope to
> ghod you are not using IDENTITY and thinking that it is a key!!
> Don't you know about SAN and other industry standard address numbers?
>
> >> A(Lookup table) and B(Transaction Table) <<
> Why did you avoid clear names?
> CREATE TABLE LocationCodes
> (loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
> loc_code INTEGER NOT NULL); -- industry SAN '
> -- put wildcards in the table for indexing
> INSERT INTO LocationCodes VALUES ('B%', 100);
> INSERT INTO LocationCodes VALUES ('BO%', 101);
> INSERT INTO LocationCodes VALUES ('BOM%', 102);
> Etc.
> Can two prefixes belong to the same SAN? No specs given.
> Without a key in that vague transactions table, you do not have a
> proper table at all. I had to make up one. Why do you have employee
> id and not find the employee name via a join to the Personnel table?
> Isn't the idea of RDBMS to get rid of redudant data?
> CREATE TABLE FoobarTrans
> (foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
> -- CHECK (<<needs validation rule here>>),
> emp_id INTEGER NOT NULL
> REFERENCES Personnel(emp_id)
> ON UPDATE CASCADE,
> loc_code INTEGER NOT NULL
> REFERENCES LocationCodes(loc_code)
> ON UPDATE CASCADE,
> Etc.);
> The prefix should have been used when you inserted the initial row (NOT
> field!!!) into the table. Because you are confusing fields and
> columns, files and tables, you are thinking in procedural *steps* with
> updates just like a punch card file, not in sets like an SQL
> programmer.
> >> I hope you get where I am leading this to, from my examples.. <<
> No. Clear specs would have been nice, along with real DDL.
> Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
> in the VALUES list, but if you have SQL-2005, try this little untested
> statement:
> INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
> VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
> (WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
> FROM LocationCodes AS L1
> WHERE L1.loc_prefix LIKE @.my_loc_prefix)
> AS M(loc_code, fit)
> SELECT loc_code
> FROM M AS M1
> WHERE M1.fit
> = (SELECT MAX(M2.fit) FROM M AS M2)),
> Etc.);
> You will need error handling code for prefixes that do not match.

Wednesday, March 28, 2012

Replacement Root Query string

Replacement of values within a string

Replacement of values within a string

Replacement of values within a string

Replacement of functions by procedures for SQL 7.0

Replacement of functions by procedures for SQL 7.0

Replacement of C code with C# code

replacement of *= in 2005

Replacement for xp_cmdshell in SQL2k

Replacement for xp_cmdshell in SQL2k

Replacement for sysprocesses dbid?

Replacement for sysprocesses dbid?

Replacement for SQL Query Analyser

Monday, March 26, 2012

Replacement for SETUSER

replacement for report manager

Replacement for my LIKE Clause

Replacement for my LIKE Clause

Rendering report

Blog Archive

About Me