Hello ,All
I am new to the reporting services and my question maybe stupid .
I created some report and published it on the server.
Now my question is : does it possible at all to run this report from my
client (in C# )and to export the result to CSV format also from client
Is there any web service that I can use which is calling for my report ?
Please ,help me to understand this .
Thanks a lot.
SmugliyYes, Reporting Services provide a set of Web Services API that you can call
to generate report. Form your client app project (Win Form App), set web
reference to the reporting services and look into its Render() methods. You
can also learn more aboubt this from SQL Server2005 Book on Line.
"Smugliy" <Smugliy@.discussions.microsoft.com> wrote in message
news:184E2D63-E618-4C35-A0C5-5F3A647B3803@.microsoft.com...
> Hello ,All
> I am new to the reporting services and my question maybe stupid .
> I created some report and published it on the server.
> Now my question is : does it possible at all to run this report from my
> client (in C# )and to export the result to CSV format also from client
> Is there any web service that I can use which is calling for my report
> ?
> Please ,help me to understand this .
> Thanks a lot.
> Smugliy
>
>|||Thanks a lot ,Norman
"Norman Yuan" wrote:
> Yes, Reporting Services provide a set of Web Services API that you can call
> to generate report. Form your client app project (Win Form App), set web
> reference to the reporting services and look into its Render() methods. You
> can also learn more aboubt this from SQL Server2005 Book on Line.
>
> "Smugliy" <Smugliy@.discussions.microsoft.com> wrote in message
> news:184E2D63-E618-4C35-A0C5-5F3A647B3803@.microsoft.com...
> > Hello ,All
> > I am new to the reporting services and my question maybe stupid .
> >
> > I created some report and published it on the server.
> > Now my question is : does it possible at all to run this report from my
> > client (in C# )and to export the result to CSV format also from client
> > Is there any web service that I can use which is calling for my report
> > ?
> > Please ,help me to understand this .
> > Thanks a lot.
> > Smugliy
> >
> >
> >
>|||Your other option is URL integration. It too will work. It allows you to
pick the rendering format. One point with either URL or web services. By
default CSV is in unicode format, if you don't want this then you can also
tell Reporting Services that you want it in ASCII format.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Smugliy" <Smugliy@.discussions.microsoft.com> wrote in message
news:184E2D63-E618-4C35-A0C5-5F3A647B3803@.microsoft.com...
> Hello ,All
> I am new to the reporting services and my question maybe stupid .
> I created some report and published it on the server.
> Now my question is : does it possible at all to run this report from my
> client (in C# )and to export the result to CSV format also from client
> Is there any web service that I can use which is calling for my report
> ?
> Please ,help me to understand this .
> Thanks a lot.
> Smugliy
>
>sql
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Running a stored procedure from VBScript
Googled around and found this for VBScript code to run a SP:
dim conn
set conn=createobject("adodb.connection")
conn.connectionstring="provider=sqloledb.1;persist security info=false;user
id=xx;password=xx;initial catalog=xx;data source=xx"
conn.open
conn.execute "test_sp"
conn.close
set conn=nothing
test_sp:
RAISERROR ('Test message',20,1) WITH LOG
When I run the VBScript, it basically outputs 'test message' as an error
from what I can tell.
I want the VBScript to run quietly, and not output anything.
How can this be done or is there a better VBScript script that I can use?
I'm hoping I can also catch errors with the same script if the SP fails...
Thanks,
MarcoObviously, your SP raises an error and pass it to the calling process (your
VBScript's ADODB.Connection.Execute() call). You simply need to handle the
error in your VBScript code (unfortunately, VBScript has poor exception
handling method):
Public Sub DoSQLStuff()
...
On Error Resume Next
cn.Open
If Err.Number<>0 Then
'Do something on error, if you want to
Exit Sub
End If
cn.Execute "test_sp"
If Err.Number<>0 Then
'Do something of your choice
Exit Sub
End If
...
End Sub
The point is, after each line of code, if the code could cause runtime
error, you need to check Err object to see if there is error or not.
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message
news:OM2G86oqGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Googled around and found this for VBScript code to run a SP:
> dim conn
> set conn=createobject("adodb.connection")
> conn.connectionstring="provider=sqloledb.1;persist security
> info=false;user
> id=xx;password=xx;initial catalog=xx;data source=xx"
> conn.open
> conn.execute "test_sp"
> conn.close
> set conn=nothing
> test_sp:
> RAISERROR ('Test message',20,1) WITH LOG
> When I run the VBScript, it basically outputs 'test message' as an error
> from what I can tell.
> I want the VBScript to run quietly, and not output anything.
> How can this be done or is there a better VBScript script that I can use?
> I'm hoping I can also catch errors with the same script if the SP fails...
> Thanks,
> Marco
>sql
dim conn
set conn=createobject("adodb.connection")
conn.connectionstring="provider=sqloledb.1;persist security info=false;user
id=xx;password=xx;initial catalog=xx;data source=xx"
conn.open
conn.execute "test_sp"
conn.close
set conn=nothing
test_sp:
RAISERROR ('Test message',20,1) WITH LOG
When I run the VBScript, it basically outputs 'test message' as an error
from what I can tell.
I want the VBScript to run quietly, and not output anything.
How can this be done or is there a better VBScript script that I can use?
I'm hoping I can also catch errors with the same script if the SP fails...
Thanks,
MarcoObviously, your SP raises an error and pass it to the calling process (your
VBScript's ADODB.Connection.Execute() call). You simply need to handle the
error in your VBScript code (unfortunately, VBScript has poor exception
handling method):
Public Sub DoSQLStuff()
...
On Error Resume Next
cn.Open
If Err.Number<>0 Then
'Do something on error, if you want to
Exit Sub
End If
cn.Execute "test_sp"
If Err.Number<>0 Then
'Do something of your choice
Exit Sub
End If
...
End Sub
The point is, after each line of code, if the code could cause runtime
error, you need to check Err object to see if there is error or not.
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message
news:OM2G86oqGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Googled around and found this for VBScript code to run a SP:
> dim conn
> set conn=createobject("adodb.connection")
> conn.connectionstring="provider=sqloledb.1;persist security
> info=false;user
> id=xx;password=xx;initial catalog=xx;data source=xx"
> conn.open
> conn.execute "test_sp"
> conn.close
> set conn=nothing
> test_sp:
> RAISERROR ('Test message',20,1) WITH LOG
> When I run the VBScript, it basically outputs 'test message' as an error
> from what I can tell.
> I want the VBScript to run quietly, and not output anything.
> How can this be done or is there a better VBScript script that I can use?
> I'm hoping I can also catch errors with the same script if the SP fails...
> Thanks,
> Marco
>sql
Running a stored procedure from VBScript
Googled around and found this for VBScript code to run a SP:
dim conn
set conn=createobject("adodb.connection")
conn.connectionstring="provider=sqloledb.1;persist security info=false;user
id=xx;password=xx;initial catalog=xx;data source=xx"
conn.open
conn.execute "test_sp"
conn.close
set conn=nothing
test_sp:
RAISERROR ('Test message',20,1) WITH LOG
When I run the VBScript, it basically outputs 'test message' as an error
from what I can tell.
I want the VBScript to run quietly, and not output anything.
How can this be done or is there a better VBScript script that I can use?
I'm hoping I can also catch errors with the same script if the SP fails...
Thanks,
MarcoObviously, your SP raises an error and pass it to the calling process (your
VBScript's ADODB.Connection.Execute() call). You simply need to handle the
error in your VBScript code (unfortunately, VBScript has poor exception
handling method):
Public Sub DoSQLStuff()
...
On Error Resume Next
cn.Open
If Err.Number<>0 Then
'Do something on error, if you want to
Exit Sub
End If
cn.Execute "test_sp"
If Err.Number<>0 Then
'Do something of your choice
Exit Sub
End If
...
End Sub
The point is, after each line of code, if the code could cause runtime
error, you need to check Err object to see if there is error or not.
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message
news:OM2G86oqGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Googled around and found this for VBScript code to run a SP:
> dim conn
> set conn=createobject("adodb.connection")
> conn.connectionstring="provider=sqloledb.1;persist security
> info=false;user
> id=xx;password=xx;initial catalog=xx;data source=xx"
> conn.open
> conn.execute "test_sp"
> conn.close
> set conn=nothing
> test_sp:
> RAISERROR ('Test message',20,1) WITH LOG
> When I run the VBScript, it basically outputs 'test message' as an error
> from what I can tell.
> I want the VBScript to run quietly, and not output anything.
> How can this be done or is there a better VBScript script that I can use?
> I'm hoping I can also catch errors with the same script if the SP fails...
> Thanks,
> Marco
>
dim conn
set conn=createobject("adodb.connection")
conn.connectionstring="provider=sqloledb.1;persist security info=false;user
id=xx;password=xx;initial catalog=xx;data source=xx"
conn.open
conn.execute "test_sp"
conn.close
set conn=nothing
test_sp:
RAISERROR ('Test message',20,1) WITH LOG
When I run the VBScript, it basically outputs 'test message' as an error
from what I can tell.
I want the VBScript to run quietly, and not output anything.
How can this be done or is there a better VBScript script that I can use?
I'm hoping I can also catch errors with the same script if the SP fails...
Thanks,
MarcoObviously, your SP raises an error and pass it to the calling process (your
VBScript's ADODB.Connection.Execute() call). You simply need to handle the
error in your VBScript code (unfortunately, VBScript has poor exception
handling method):
Public Sub DoSQLStuff()
...
On Error Resume Next
cn.Open
If Err.Number<>0 Then
'Do something on error, if you want to
Exit Sub
End If
cn.Execute "test_sp"
If Err.Number<>0 Then
'Do something of your choice
Exit Sub
End If
...
End Sub
The point is, after each line of code, if the code could cause runtime
error, you need to check Err object to see if there is error or not.
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message
news:OM2G86oqGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Googled around and found this for VBScript code to run a SP:
> dim conn
> set conn=createobject("adodb.connection")
> conn.connectionstring="provider=sqloledb.1;persist security
> info=false;user
> id=xx;password=xx;initial catalog=xx;data source=xx"
> conn.open
> conn.execute "test_sp"
> conn.close
> set conn=nothing
> test_sp:
> RAISERROR ('Test message',20,1) WITH LOG
> When I run the VBScript, it basically outputs 'test message' as an error
> from what I can tell.
> I want the VBScript to run quietly, and not output anything.
> How can this be done or is there a better VBScript script that I can use?
> I'm hoping I can also catch errors with the same script if the SP fails...
> Thanks,
> Marco
>
Monday, March 26, 2012
Running a DTS Package from vb.net
Can anyone point me to some sample code ord give me an example. I'm trying to run a DTS package from my asp.net/vb.net application. I don't need to pass it any variable, just need it to run. I can't seem to find anything on the net for this.
Thanks in advance,
Ryan
There are two ways to do it, through VB.NET code, or through a stored procedure using master..xp_cmdshell. Here's one:
http://www.realworldasp.net/article.asp?article=61
http://www.msnewsgroups.net/group/microsoft.public.dotnet.languages.csharp/topic7332.aspx
bmains wrote:
There are two ways to do it, through VB.NET code, or through a stored procedure using master..xp_cmdshell. Here's one:http://www.realworldasp.net/article.asp?article=61
http://www.msnewsgroups.net/group/microsoft.public.dotnet.languages.csharp/topic7332.aspx
The links you provided are not working solutions because the creators have not addressed the SQL Server Agent permissions needed to run DTS with dtsrun.exe and xp_cmdshell.
To run DTS package through a stored proc you either use DTSRUN.exe or XP_CMDSHELL which is SQL Server Agent dependent. Try the links below for DTSRUN.exe sample code and XP_CMDSHELL configurations with permissions. What I am saying is to run DTS with SQL Server Agent dependent service like xp_CMDSHELL you must give the account used to install SQL Server Agent Admin permissions in Windows and SQL Server. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
http://www.sqlteam.com/item.asp?ItemID=19595
like this. spexecuteDTSPackage('mypackage')
This would be wonderful.|||I don't know of an easier way because it is SQL Server Agent that runs your DTS package through xp_cmdshell.|||That is the DTSrun utility, which in a stored procedure, you have to use master..xp_cmdShell, and get the rights to do so. See, to run it, it needs to know what server to run it on, what is the name, what are the parameters being passed into it (which is cool because you can dynamically assign the server variables in the package with values passed into the stored procedure), what is the package password if there is one|||
I finally got it working. I think I'm going to write a tutorial since there aren't any good ones out there.
I generated the code for the dtsrun.exe with the dtsgui tool then created a stored procedure.
Thanks for all your help.
|||You ever write a tutorial for this? I have the same problem and need major help getting a dts to run from .net.
Cordell
Wednesday, March 21, 2012
Run SQL Server Report from .Net WIndows App...
Does a report have to be run from the Report Manager (IIS), or can rdl files
be executed from .Net code in a Windows Application?
I quess what I am asking is, with Crystal and VB 6, I can lauch a report
through a control embedded in the exe file.
Is there a control to place in a Windows Form that can view a report without
haing a IIS Report Manager?
--
Thanks,
ThomasLL, MCDBAI'm pretty sure SQL Reporting Services 2005 will have a WinForms and a
WebForms viewer.
"Thomas.LeBlanc@.NoSpam.Com" wrote:
> Does a report have to be run from the Report Manager (IIS), or can rdl files
> be executed from .Net code in a Windows Application?
> I quess what I am asking is, with Crystal and VB 6, I can lauch a report
> through a control embedded in the exe file.
> Is there a control to place in a Windows Form that can view a report without
> haing a IIS Report Manager?
> --
> Thanks,
> ThomasLL, MCDBAsql
be executed from .Net code in a Windows Application?
I quess what I am asking is, with Crystal and VB 6, I can lauch a report
through a control embedded in the exe file.
Is there a control to place in a Windows Form that can view a report without
haing a IIS Report Manager?
--
Thanks,
ThomasLL, MCDBAI'm pretty sure SQL Reporting Services 2005 will have a WinForms and a
WebForms viewer.
"Thomas.LeBlanc@.NoSpam.Com" wrote:
> Does a report have to be run from the Report Manager (IIS), or can rdl files
> be executed from .Net code in a Windows Application?
> I quess what I am asking is, with Crystal and VB 6, I can lauch a report
> through a control embedded in the exe file.
> Is there a control to place in a Windows Form that can view a report without
> haing a IIS Report Manager?
> --
> Thanks,
> ThomasLL, MCDBAsql
Monday, March 12, 2012
run packages as part of procedure
I have created SQL code that is contained in different procedures. I have a procedure that then runs all of the pieces that I wish to run. My last step in my process is to export the data to my c: drive. During this process I created a package to be rerun. How do I include the rerunning of my package automatically as part of the procedural process?
How about creating a job for the package, and calling sp_start_job from your procedure. Note this will not wait for the package to complete.
A less attactive option is to enable xp_cmdshell, and call DTEXEC through xp_cmdshell, which itself can be called from your procedure.
Friday, March 9, 2012
Run DTS Package from Visual Basic 6.3
Anyone have any code i can use to call a DTS package from Visual Basic. I would like to have an outlook macro that will run a dts package when i receive an email. All i need is the code that will call a dts package for a sql server that is not local to the box. I've tried this code but i get an error
run_tmie error '-2147217900 (80040e14)' automation error
Public Sub main()
Dim oPkg As DTS.Package2
Set oPkg = New DTS.Package2
oPkg.LoadFromSQLServer MyServerName, MyUserName, MyPassword, , MyDTSPackageName
oPkg.Execute
oPkg.UnInitialize
Set oPkg = Nothing
End Sub
Thanks in advance.I use the following in a VBScript. Will work in VB also.
strDTSRun_Command = "dtsrun.exe /Sservername /E /Ndtspackagename"
iWindowStyle = 10
bWaitOnReturn = true
Return = WshShell.Run(strDTSRun_Command, iWindowStyle, bWaitOnReturn)
run_tmie error '-2147217900 (80040e14)' automation error
Public Sub main()
Dim oPkg As DTS.Package2
Set oPkg = New DTS.Package2
oPkg.LoadFromSQLServer MyServerName, MyUserName, MyPassword, , MyDTSPackageName
oPkg.Execute
oPkg.UnInitialize
Set oPkg = Nothing
End Sub
Thanks in advance.I use the following in a VBScript. Will work in VB also.
strDTSRun_Command = "dtsrun.exe /Sservername /E /Ndtspackagename"
iWindowStyle = 10
bWaitOnReturn = true
Return = WshShell.Run(strDTSRun_Command, iWindowStyle, bWaitOnReturn)
Wednesday, March 7, 2012
Run a SQL Query From VBS?
It Seems I've solved my previous doubt about the ADODB
case. I've used this code to launch the query:
SQLString = "SELECT COD_NOTARIO FROM dbo.tblNOTARIO"
Dim oCn, oFSO
Set oCn = CreateObject("ADODB.Connection")
Set oFSO = CreateObject("Scripting.FileSystemObject")
oCn.Open
"PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=FinalMultiple;"
oCn.Execute SQLString
Set oCn = Nothing
However I've run into a much more amusing issue given that
I can't figure out how to access to the query results from
the ActiveX script (a column and many fields).
Could anybody be so kind to tell me how can I see from VBS
the query results?
TIA
Greetings,
David Grant> Could anybody be so kind to tell me how can I see from VBS
> the query results?
You need a recordset object. Code snippet:
Set oRs = oCn.Execute(SQLString)
Do While oRs.EOF = False
'process each row here
cod = oRs.Fields("COD").Value
oRs.MoveNext
Loop
Hope this helps.
Dan Guzman
SQL Server MVP
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:80c501c528bc$b4592170$a601280a@.phx.gbl...
> It Seems I've solved my previous doubt about the ADODB
> case. I've used this code to launch the query:
>
> SQLString = "SELECT COD_NOTARIO FROM dbo.tblNOTARIO"
>
> Dim oCn, oFSO
> Set oCn = CreateObject("ADODB.Connection")
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> oCn.Open
> "PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=FinalMultiple;"
> oCn.Execute SQLString
> Set oCn = Nothing
> However I've run into a much more amusing issue given that
> I can't figure out how to access to the query results from
> the ActiveX script (a column and many fields).
> Could anybody be so kind to tell me how can I see from VBS
> the query results?
> TIA
> Greetings,
> David Grant|||Thank you very much Dan, your code has really helped me a
lot!! :)
Greetings,
David Grant
>--Original Message--
>You need a recordset object. Code snippet:
>Set oRs = oCn.Execute(SQLString)
>Do While oRs.EOF = False
> 'process each row here
> cod = oRs.Fields("COD").Value
> oRs.MoveNext
>Loop
>
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>|||I'm glad it help you out.
Dan Guzman
SQL Server MVP
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:03e901c52934$6045c2f0$a401280a@.phx.gbl...
> Thank you very much Dan, your code has really helped me a
> lot!! :)
>
> Greetings,
> David Grant
>
case. I've used this code to launch the query:
SQLString = "SELECT COD_NOTARIO FROM dbo.tblNOTARIO"
Dim oCn, oFSO
Set oCn = CreateObject("ADODB.Connection")
Set oFSO = CreateObject("Scripting.FileSystemObject")
oCn.Open
"PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=FinalMultiple;"
oCn.Execute SQLString
Set oCn = Nothing
However I've run into a much more amusing issue given that
I can't figure out how to access to the query results from
the ActiveX script (a column and many fields).
Could anybody be so kind to tell me how can I see from VBS
the query results?
TIA
Greetings,
David Grant> Could anybody be so kind to tell me how can I see from VBS
> the query results?
You need a recordset object. Code snippet:
Set oRs = oCn.Execute(SQLString)
Do While oRs.EOF = False
'process each row here
cod = oRs.Fields("COD").Value
oRs.MoveNext
Loop
Hope this helps.
Dan Guzman
SQL Server MVP
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:80c501c528bc$b4592170$a601280a@.phx.gbl...
> It Seems I've solved my previous doubt about the ADODB
> case. I've used this code to launch the query:
>
> SQLString = "SELECT COD_NOTARIO FROM dbo.tblNOTARIO"
>
> Dim oCn, oFSO
> Set oCn = CreateObject("ADODB.Connection")
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> oCn.Open
> "PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=FinalMultiple;"
> oCn.Execute SQLString
> Set oCn = Nothing
> However I've run into a much more amusing issue given that
> I can't figure out how to access to the query results from
> the ActiveX script (a column and many fields).
> Could anybody be so kind to tell me how can I see from VBS
> the query results?
> TIA
> Greetings,
> David Grant|||Thank you very much Dan, your code has really helped me a
lot!! :)
Greetings,
David Grant
>--Original Message--
>You need a recordset object. Code snippet:
>Set oRs = oCn.Execute(SQLString)
>Do While oRs.EOF = False
> 'process each row here
> cod = oRs.Fields("COD").Value
> oRs.MoveNext
>Loop
>
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>|||I'm glad it help you out.
Dan Guzman
SQL Server MVP
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:03e901c52934$6045c2f0$a401280a@.phx.gbl...
> Thank you very much Dan, your code has really helped me a
> lot!! :)
>
> Greetings,
> David Grant
>
Saturday, February 25, 2012
Rule: Max Number of Lines Per SP
A client believes that "a stored procedure should never have more than about
50 lines of code in it."
No rationalle behind that thought.
When pressed - she stated, "I heard that in a class once a long time ago."
I think this person is an idiot.
Is there or was there ever a time when a such a rule made sense for
technical reasons?
I'm just trying to see how such a belief might possibly make sense.
Thanks.= u r idiot
Message posted via http://www.webservertalk.com|||>> A client believes that "a stored procedure should never have more
than about 50 lines of code in it." No rationalle behind that thought.
<<
Actually, there is both a rationale and a history. Return with me now
to the 1970's when Software Engineering was being born. We would put
bugs into code and give them to people debug, and measure them.
Fifty lines is about one page of hardcopy print out. We found that
code modules that can be seen in toto are measurably easier to maintain
than those that were split across pages or screens. The longer the
module got, the harder the bugs were to find (I cannot remember the
formula, but it was greater than linear). University of Maryland and
SEI had some of the studies, as I recall.
This pattern held across languages, which meant COBOL, FORTRAN, Jovial
and Assembler in those days, then later Pascal and C.
Notice that I said "module", so subroutines, paragraphs or procedures
count as modules depending on your language.
In the case of SQL Server, this was the rule because T-SQL was never
meant to an application language. It is a very simple one-pass
compiler that was used to "fill in the holes" in the early product by
giving you triggers for DRI and other thigns we do declarative today.
.|||Joe,
<<Actually, there is both a rationale and a history>>
YOU have an interesting rationalle. So does everyone else who responded to
my OP, and I agree with all of them to date.
The idiot manager had no rationalle floating around inside her head beyond
"I heard it in a class once." We talked at length... she really had nothing
more than that.
I just wanted to see if I was missing something... apparently not.
Thanks Everyone!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1114720755.550812.61610@.g14g2000cwa.googlegroups.com...
> than about 50 lines of code in it." No rationalle behind that thought.
> <<
> Actually, there is both a rationale and a history. Return with me now
> to the 1970's when Software Engineering was being born. We would put
> bugs into code and give them to people debug, and measure them.
> Fifty lines is about one page of hardcopy print out. We found that
> code modules that can be seen in toto are measurably easier to maintain
> than those that were split across pages or screens. The longer the
> module got, the harder the bugs were to find (I cannot remember the
> formula, but it was greater than linear). University of Maryland and
> SEI had some of the studies, as I recall.
> This pattern held across languages, which meant COBOL, FORTRAN, Jovial
> and Assembler in those days, then later Pascal and C.
> Notice that I said "module", so subroutines, paragraphs or procedures
> count as modules depending on your language.
> In the case of SQL Server, this was the rule because T-SQL was never
> meant to an application language. It is a very simple one-pass
> compiler that was used to "fill in the holes" in the early product by
> giving you triggers for DRI and other thigns we do declarative today.
> .
>|||Guadala Harry wrote:
<nothing of importance>
Your choice of words and tone are troublesome...
David G.|||On Thu, 28 Apr 2005 13:03:19 -0700, Guadala Harry wrote:
>A client believes that "a stored procedure should never have more than abou
t
>50 lines of code in it."
>No rationalle behind that thought.
>When pressed - she stated, "I heard that in a class once a long time ago."
>I think this person is an idiot.
>Is there or was there ever a time when a such a rule made sense for
>technical reasons?
>I'm just trying to see how such a belief might possibly make sense.
>Thanks.
>
Hi Guadala,
As Joe indicated, there is some rationale for this rule. But it should
be treated as a rule of thumb. The point is to reduce complexity.
Suppose you have a query like this:
SELECT a.Col1,
b.Col2,
..
e.ColN
FROM Table1 AS a
-- Some useful comments here
INNER JOIN Table2 AS b
ON b.Col8 = a.Col4
INNER JOIN Table3 AS c
ON ...
...
WHERE d.Col18 = 'Asdf'
AND e.Col4 < b.Col8
....
ORDER BY a.Col1,
b.Col2,
...
c.Col8
It's very readable, but probably more than 50 lines. But yoou coould
squeeze it in a lot less lines:
SELECT a.Col1, b.Col2, ..., e.ColN FROM Table1 AS a
INNER JOIN Table2 AS b ON b.Col8 = a.Col4 INNER JOIN Table3 AS c
ON ... WHERE d.Col18 = 'Asdf' AND e.Col4 < b.Col8 ....
ORDER BY a.Col1, b.Col2, ..., c.Col8
But would it be easier to read'
A reverse example: back in my mainframe / Cobol days, creating a report
(or an external interface) required you to move all fields in the
records from the read file to the corresponding field in the records to
be written. Code like this:
OUTREC.FIELD1 = INREC.FIELD1;
OUTREC.FIELD2 = INREC.FIELD2;
...
OUTREC.FIELD138 = INREC.FIELD138;
WRITE FILE (OUTFILE) FROM (OUTREC);
I had a colleague who took the 50-lines rule way too literally - he
would break this routine in three subroutines, and call them after each
other:
CALL FILL_OUTREC_PART1;
CALL FILL_OUTREC_PART2;
CALL FILL_OUTREC_PART3;
WRITE FILE (OUTFILE) FROM (OUTREC);
Senseless overhead, IMO.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||<<Your choice of words and tone are troublesome>>
And ?|||And there are some general rules of respect and courtesy that everyone
should follow when posting. I think everyone understands your frustration -
we've all been there in one form or another (and perhaps even been the
source of frustration at times), but this isn't the forum for venting your
anger especially with insults, deserved or not.
Mike
"Guadala Harry" <GMan@.NoSpam.net> wrote in message
news:O7$gY2ETFHA.2124@.TK2MSFTNGP14.phx.gbl...
> <<Your choice of words and tone are troublesome>>
> And ?
>
50 lines of code in it."
No rationalle behind that thought.
When pressed - she stated, "I heard that in a class once a long time ago."
I think this person is an idiot.
Is there or was there ever a time when a such a rule made sense for
technical reasons?
I'm just trying to see how such a belief might possibly make sense.
Thanks.= u r idiot
Message posted via http://www.webservertalk.com|||>> A client believes that "a stored procedure should never have more
than about 50 lines of code in it." No rationalle behind that thought.
<<
Actually, there is both a rationale and a history. Return with me now
to the 1970's when Software Engineering was being born. We would put
bugs into code and give them to people debug, and measure them.
Fifty lines is about one page of hardcopy print out. We found that
code modules that can be seen in toto are measurably easier to maintain
than those that were split across pages or screens. The longer the
module got, the harder the bugs were to find (I cannot remember the
formula, but it was greater than linear). University of Maryland and
SEI had some of the studies, as I recall.
This pattern held across languages, which meant COBOL, FORTRAN, Jovial
and Assembler in those days, then later Pascal and C.
Notice that I said "module", so subroutines, paragraphs or procedures
count as modules depending on your language.
In the case of SQL Server, this was the rule because T-SQL was never
meant to an application language. It is a very simple one-pass
compiler that was used to "fill in the holes" in the early product by
giving you triggers for DRI and other thigns we do declarative today.
.|||Joe,
<<Actually, there is both a rationale and a history>>
YOU have an interesting rationalle. So does everyone else who responded to
my OP, and I agree with all of them to date.
The idiot manager had no rationalle floating around inside her head beyond
"I heard it in a class once." We talked at length... she really had nothing
more than that.
I just wanted to see if I was missing something... apparently not.
Thanks Everyone!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1114720755.550812.61610@.g14g2000cwa.googlegroups.com...
> than about 50 lines of code in it." No rationalle behind that thought.
> <<
> Actually, there is both a rationale and a history. Return with me now
> to the 1970's when Software Engineering was being born. We would put
> bugs into code and give them to people debug, and measure them.
> Fifty lines is about one page of hardcopy print out. We found that
> code modules that can be seen in toto are measurably easier to maintain
> than those that were split across pages or screens. The longer the
> module got, the harder the bugs were to find (I cannot remember the
> formula, but it was greater than linear). University of Maryland and
> SEI had some of the studies, as I recall.
> This pattern held across languages, which meant COBOL, FORTRAN, Jovial
> and Assembler in those days, then later Pascal and C.
> Notice that I said "module", so subroutines, paragraphs or procedures
> count as modules depending on your language.
> In the case of SQL Server, this was the rule because T-SQL was never
> meant to an application language. It is a very simple one-pass
> compiler that was used to "fill in the holes" in the early product by
> giving you triggers for DRI and other thigns we do declarative today.
> .
>|||Guadala Harry wrote:
<nothing of importance>
Your choice of words and tone are troublesome...
David G.|||On Thu, 28 Apr 2005 13:03:19 -0700, Guadala Harry wrote:
>A client believes that "a stored procedure should never have more than abou
t
>50 lines of code in it."
>No rationalle behind that thought.
>When pressed - she stated, "I heard that in a class once a long time ago."
>I think this person is an idiot.
>Is there or was there ever a time when a such a rule made sense for
>technical reasons?
>I'm just trying to see how such a belief might possibly make sense.
>Thanks.
>
Hi Guadala,
As Joe indicated, there is some rationale for this rule. But it should
be treated as a rule of thumb. The point is to reduce complexity.
Suppose you have a query like this:
SELECT a.Col1,
b.Col2,
..
e.ColN
FROM Table1 AS a
-- Some useful comments here
INNER JOIN Table2 AS b
ON b.Col8 = a.Col4
INNER JOIN Table3 AS c
ON ...
...
WHERE d.Col18 = 'Asdf'
AND e.Col4 < b.Col8
....
ORDER BY a.Col1,
b.Col2,
...
c.Col8
It's very readable, but probably more than 50 lines. But yoou coould
squeeze it in a lot less lines:
SELECT a.Col1, b.Col2, ..., e.ColN FROM Table1 AS a
INNER JOIN Table2 AS b ON b.Col8 = a.Col4 INNER JOIN Table3 AS c
ON ... WHERE d.Col18 = 'Asdf' AND e.Col4 < b.Col8 ....
ORDER BY a.Col1, b.Col2, ..., c.Col8
But would it be easier to read'
A reverse example: back in my mainframe / Cobol days, creating a report
(or an external interface) required you to move all fields in the
records from the read file to the corresponding field in the records to
be written. Code like this:
OUTREC.FIELD1 = INREC.FIELD1;
OUTREC.FIELD2 = INREC.FIELD2;
...
OUTREC.FIELD138 = INREC.FIELD138;
WRITE FILE (OUTFILE) FROM (OUTREC);
I had a colleague who took the 50-lines rule way too literally - he
would break this routine in three subroutines, and call them after each
other:
CALL FILL_OUTREC_PART1;
CALL FILL_OUTREC_PART2;
CALL FILL_OUTREC_PART3;
WRITE FILE (OUTFILE) FROM (OUTREC);
Senseless overhead, IMO.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||<<Your choice of words and tone are troublesome>>
And ?|||And there are some general rules of respect and courtesy that everyone
should follow when posting. I think everyone understands your frustration -
we've all been there in one form or another (and perhaps even been the
source of frustration at times), but this isn't the forum for venting your
anger especially with insults, deserved or not.
Mike
"Guadala Harry" <GMan@.NoSpam.net> wrote in message
news:O7$gY2ETFHA.2124@.TK2MSFTNGP14.phx.gbl...
> <<Your choice of words and tone are troublesome>>
> And ?
>
Subscribe to:
Posts (Atom)