Prediction Join to MDX with nested table

If your prediction join is to a SQL datasource, you can easily write a SQL query which returns a nested table like:

SELECT
Predict([Subcategories],2) as [Subcategories]
FROM
[SubcategoryAssociations]
NATURAL PREDICTION JOIN
(SELECT
(SELECT 'Road Bikes' AS Subcategory
UNION SELECT 'Jerseys' AS Subcategory
) AS Subcategories
) AS t

What about if your datasource is a cube? Is there some special MDX syntax similar to the SQL syntax above? Or do you have to utilize the SHAPE/APPEND syntax as follows?

SELECT t.*, $Cluster as ClusterName
FROM [MyModel]
PREDICTION JOIN
SHAPE {
select [Measures].[My Measure] on 0,
[My Dimension].[My Attribute].[My Attribute].Members on 1
from MyCube
}
APPEND (
{
select [Measures].[Another Measure] on 0,
NON EMPTY [My Dimension].[My Attribute].[My Attribute].Members
*[Product].[Product].[Product].Members on 1
from MyCube
}
RELATE [[My Dimension]].[My Attribute]].[My Attribute]].[MEMBER_CAPTION]]]
TO [[My Dimension]].[My Attribute]].[My Attribute]].[MEMBER_CAPTION]]]
)
AS [My Nested Table] AS t
ON [MyModel].[Product].[Product] = t.[My Nested Table].[[Product]].[Product]].[Product]].[MEMBER_CAPTION]]]

Typically, for building models on top of cubes, it is much easier to use the tools (BI Dev Studio). This way you can define your model directly on top of the cube and lots of optimizations occur. With such models, you can even use the MDXPredict function to get prediction results inside MDX queries over the source cube.

The DMX SELECT statement supports as input rowset-returning Analysis Services statements (MDX or DMX). That means that dataset-returning statements are not supported. But many MDX queries can be flattened. Have you tried something like SELECT FLATTENED in the MDX query?

|||

Bogdan-

Thanks for the reply. Yes, BIDS worked great for building the model. I've got it trained. Now I want to do a prediction based upon data from a cube. From what I can tell, you can't do prediction queries off a cube using BIDS because it only lets you predict off a relational table source. Right?

I've been researching the MDX function "Predict" which you mentioned. But I'm having terrible trouble finding example queries using that function...

Here's what I'm looking for... we've built a clustering model to cluster our stores. Some of the attributes are just Store dimension attributes... some are from a nested table (stats about the sales volume from each product category). We trained the model with all the stores. Now we want to extract the cluster name for each store and save that to a table. So is there a straight MDX query using the Predict MDX function which will get me the cluster name for every store? I was having trouble seeing how the Predict MDX function was able to know how to do a prediction join to the Store dimension.

As a side note, we could almost do a natural prediction join back to (select * from Model.CASES) except that we don't want the Store Key to influence the clustering model so we didn't add that as an input to the model. (And marking Store Key as Ignore excludes it from the Model.CASES resultset.)

By the way, we're only talking about a couple hundred rows, so the performance of the SHAPE/APPEND syntax below is fine for my purposes... just seeing if there's a more elegant way to do it.

Thanks!

|||

Oh... and to answer your other question about trying "SELECT FLATTENED"...

It's my understanding that "SELECT FLATTENED" is DMX. I'm not sure how to write an MDX statement that starts with "SELECT FLATTENED". And I'm struggling to see how using the DMX "SELECT FLATTENED" would help me. The output of DMX prediction query I used in the examples at the beginning of the thread work fine. I suppose I could flatten the output, but that wouldn't help me much. It's the input to the prediction join that I'm concerned with.

Or did you mean that you can use an MDX query which is written to be flat and use that as input to a prediction query which expects nested tables? I just tried that but may not have been using the right syntax cause I couldn't get it to work. Suggestions?

|||

You kind of need to do it brute force -we use the flattening semantics of MDX when executing the query, so you have to reshape using SHAPE.

There is a little trick to help you out in building the queries. You can use DMX to examine the flattened structure of the MDX query. Just issue a query like this:

SELECT t.* FROM AnyModel NATURAL PREDICTION JOIN <My MDX Query> AS t

then you will be able to see how the DMX processor sees your MDX results.

|||

Jamie-

That trick is helpful for seeing how it refers to the results of an MDX query.

But how do I take a flat MDX query and shape it so it can be consumed by a prediction join which expects a nested table. See the MDX example at the top of this post. Is that the only way (tying two separate MDX queries together with SHAPE/APPEND)?

|||

Yes your original SHAPE/APPEND would be the way to go.

The implementation of SHAPE in the AS engine will cause the MDX query results to be automatically returned in a flattened manner without requiring any explicit flattening syntax in the query itself (in fact, there is no such syntax - flattening is requested as either a command property in XMLA or by requesting a rowset interface in OLE DB)..

Precedence Constraint Expressions

Hi all

I've got a package that checks the length of chars in a flatfile and if it's correct it executes the dataflow task.

How can i write an expression in the precedense constraint editor to skip the dataflow task if there is an error in the script task?

Thank you

Set a variable in the script task and then test the variable in the precedence constraint.|||

Something similar to:

Code Snippet

If [File is Valid condition] Then

Dts.Variables.Item("User::ValidFile").Value = 1

Else

Dts.Variables.Item("User::ValidFile").Value = 0

End If

in the script task (mark the variable as ReadWrite in the script task properties) and

Code Snippet

@.ValidFile==1

in the precedence constraint. You can set the expression by double-clicking the precendence constraint and choosing Expression and Constraint in the Evaluation operation property.

|||

Thanks for all your help Phil and JWelch.

Power Regression

I need to write some SQL to do a power regression for a trendline. I have 2 columns of data which represent my X, Y data and all I'm after is the a and the b for the function y=ax^b. Has anyone ran into this before? I know SSAS has a linear regression function but my data really only fits the power model.

I solved this guy by writing a CLR stored procedure that does the regression. I must say I have been immensely happy with both CTE's and CLR stored procedures.

|||You can do a least squares log-log regression on your data. Here's an example that adapts the least squares example at http://www.users.drew.edu/skass/sql/LeastSquares.sql.txt to do the log-log regression. Note that this may not be the precise definition of "best fit" you need, if a linear fit of the log-log data is not your objective. data. create table Bob ( i int identity(1,1) primary key, x float, y float ) declare @.j float set @.j = 1 while @.j < 100 begin set @.j = @.j + rand(checksum(newid())) insert into Bob select @.j, (3.27+rand())*power(@.j,5+rand()/3) end; declare @.b float, @.log_a float select @.b = (count(x)*sum(x*y)-sum(x)*sum(y)) /(count(x)*sum(x*x) - sum(x)*sum(x)), @.log_a = (sum(y)*sum(x*x) - sum(x)*sum(x*y)) /(count(x)*sum(x*x) - sum(x)*sum(x)) from ( select log(x) as x, log(y) as y from Bob ) as Bob select 'y = '+convert(varchar,exp(@.log_a))+'*x^'+convert(varchar,@.b) select x, y, exp(@.log_a)*power(x,@.b) as y_predicted from Bob go drop table Bob akula@.discussions.microsoft.com wrote:
> I need to write some SQL to do a power regression for a trendline. I
> have 2 columns of data which represent my X, Y data and all I'm after is
> the a and the b for the function y=ax^b. Has anyone ran into this
> before? I know SSAS has a linear regression function but my data
> really only fits the power model.
>
>|||Sorry - this is a better repro: create table Bob ( i int identity(1,1) primary key, x float, y float ) declare @.j float set @.j = 1 while @.j < 100 begin set @.j = @.j + rand(checksum(newid())) insert into Bob select @.j, (3.27+rand())*power(@.j,5+rand()/3) end; declare @.b float, @.log_a float select @.b = (count(x)*sum(x*y)-sum(x)*sum(y)) /(count(x)*sum(x*x) - sum(x)*sum(x)), @.log_a = (sum(y)*sum(x*x) - sum(x)*sum(x*y)) /(count(x)*sum(x*x) - sum(x)*sum(x)) from ( select log(x) as x, log(y) as y from Bob ) as Bob select 'y = '+convert(varchar,exp(@.log_a))+'*x^'+convert(varchar,@.b) select x, y, exp(@.log_a)*power(x,@.b) as y_predicted from Bob go drop table Bob Steve Kass Drew University akula@.discussions.microsoft.com wrote:
> I need to write some SQL to do a power regression for a trendline. I
> have 2 columns of data which represent my X, Y data and all I'm after is
> the a and the b for the function y=ax^b. Has anyone ran into this
> before? I know SSAS has a linear regression function but my data
> really only fits the power model.
>
>sql

Posting an Image to SQL Server 2005

Hello Everyone I am trying to write image files to sql server using the following code. I have recieved the following error message

Failed to convert parameter value from a String to a Byte[]. Please help. I am sure it is a problem gathering the iostream. I am not very familiar. Any help is gretaly appreciated.

Thanks in advance

Here is the code:

'Dim User As MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)

'Dim t As TextBox = DirectCast(FormView1.FindControl("aspnet_UserID"), TextBox)

Dim objConnAs SqlConnection

Dim objComAs SqlCommand

IfMe.FileUpload1.HasFileThen

Dim fileExtensionAsString

Dim fileOKAsBoolean =False

fileExtension = System.IO.Path. _

GetExtension(FileUpload1.FileName).ToLower()

Dim allowedExtensionsAsString() = _

{".jpg",".jpeg",".png",".gif",".mwv"}

For iAsInteger = 0To allowedExtensions.Length - 1

If fileExtension = allowedExtensions(i)Then

fileOK =True

EndIf

Next

'Try

Dim imagestreamAs System.IO.Stream = FileUpload1.FileContent

Dim data()AsByte

ReDim data(imagestream.Length - 1)

imagestream.Read(data, 0, imagestream.Length)

imagestream.Close()

objConn =New SqlConnection(strNewConnection)

objCom =New SqlCommand("insert into ProfileImagesAndDocs(aspnet_userid,img_name,img_data,img_contenttype)values(@.aspnet_userid,@.imagename,@.Picture,@.CategoryName)", objConn)

'--------------

'this is the aspnet_userid

Dim useridparameterAs SqlParameter =New SqlParameter("@.aspnet_userid", SqlDbType.VarChar)useridparameter.Value =Me.aspnet_userid.Text

objCom.Parameters.Add(useridparameter)

'--------------

'This is the image name

Dim imagenameparameterAs SqlParameter =New SqlParameter("@.imagename", SqlDbType.VarChar)imagenameparameter.Value =Me.FileUpload1.FileName

objCom.Parameters.Add(imagenameparameter)

'--------------

'this is the picture data

Dim pictureParameterAs SqlParameter =New SqlParameter("@.Picture", SqlDbType.Image)

pictureParameter.Value = data

objCom.Parameters.Add(pictureParameter)

'--------------

'this is the profile area or category i.e. video intro

Dim categorynameParameterAs SqlParameter =New SqlParameter("@.CategoryName", SqlDbType.VarChar)

pictureParameter.Value ="IntroVideo"

objCom.Parameters.Add(categorynameParameter)

objConn.Open()

objCom.ExecuteNonQuery()

objConn.Close()

'bookmark

Label1.Text ="File uploaded!"

'Catch ex As Exception

Label1.Text ="File could not be uploaded."

EndIf

Hi,

You may use FileStream to read the image content and save it as SqlDbType.Image into your database. See the following sample.

Private Function GetFile()As String''' GetFileDim fileAs HttpPostedFile = File1.PostedFilefileName = file.FileNameReturn fileNameEnd Function''' ReadFilePrivate Function ReadFile()As Byte()Dim fileAs FileStream = File.OpenRead(GetFile())Dim contentAs Byte() =New Byte(file.Length - 1) {}file.Read(content, 0, content.Length)file.Close()Return contentEnd FunctionPrivate Sub WriteImage()Dim commAs SqlCommand = conn.CreateCommand()comm.CommandText ="insert into images(image,type) values(@.image,@.type)"comm.CommandType = CommandType.TextDim paramAs SqlParameter = comm.Parameters.Add("@.image", SqlDbType.Image)param.Value = ReadFile()param = comm.Parameters.Add("@.type", SqlDbType.NVarChar)param.Value = GetContentType(New FileInfo(fileName).Extension.Remove(0, 1))If comm.ExecuteNonQuery() = 1ThenResponse.Write("Successful")ElseResponse.Write("Fail")End Ifconn.Close()End SubPrivate Function GetContentType(ByVal extensionAs String)As StringDim typeAs String =""If extension.Equals("jpg")OrElse extension.Equals("JPG")Thentype ="jpeg"Elsetype = extensionEnd IfReturn"image/" + typeEnd Function
 
Thanks.

|||

Thanks the code above worked very well. I am having one other difficulty when I select a file with a *.wmv extension the file is not read and the script halts. Any Ideas?! and thanks again for the solution.

post from text box to SQL insert

Hello,

I'm trying to update a single field of a record and i want to do it using a standard multi line text box but I'm not sure how to write the c# command to process the sql update. I would also like the entry to be added into the database with line breaks.

Thanks for your help

There is nothing to ask..... you can do it using the same way u r updating the others.... you do not need any c# command.... its the query on which all this depends...

you can use query... : update <table name> set <feild name>= textbox1.text where <condition>....

and about multi lines.... you do not need to worry about line breaks..... the data would be stored in the database just as the way it is in the multiline textbox.... and would be fetched in the same manner...

and do tell me if its any worthy 4 u or not.

|||

Ok - In theroy I get what your saying but then I have VisStudio post out the code I need to make the text box and the supporting data source and I get all of this:

----------------

<asp:TextBox ID="txtNotes" runat="server"></asp:TextBox>
<asp:SqlDataSource ID="sqlUpdateNotes" runat="server" ConnectionString="<%$ ConnectionStrings:dbNetOps %>" DeleteCommand="DELETE FROM [MasterServerlist] WHERE [ID] = ?" InsertCommand="INSERT INTO [MasterServerlist] ([ID], [Notes]) VALUES (?, ?)" ProviderName="<%$ ConnectionStrings:dbNetOps.ProviderName %>" SelectCommand="SELECT [ID], [Notes] FROM [MasterServerlist] WHERE ([ID] = ?)" UpdateCommand="UPDATE [MasterServerlist] SET [Notes] = ? WHERE [ID] = ?">
<DeleteParameters>
<asp:Parameter Name="ID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="Notes" Type="String" />
<asp:Parameter Name="ID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="gvServers" Name="ID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="ID" Type="Int32" />
<asp:Parameter Name="Notes" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
------------------

What I'm trying to do is :

1: display the contents of the notes field in the text box where the record matches the record selected in a gridview element

2: have the onchange event of the notes field then post back an update to the database and redisplay the new notes in the text field

I'm very new to ASP and appriciate your help.

Thank you

|||

I cannot find anything wrong in your code... could you please describe your problem in details...... or what kind of errors you are receiving(if any).....

And also you wanted to store all the data in the textbox to the database with the line breaks.... you should set the textmode attribute of textbox to multiline...

POST BACK TO SERVER

hi all,

i want to filter data from a database using parameters supplied by the user via textboxes. i've been able to write the select statement. my problem now is, the code behind for the "view data" button. do i do "sqldatasource1.select" orpost the databack to theserver? if i'm topostback to theserver, whats the code i should use?

protected void button1_Click(object sender, Eventargs e)

{

????

}

---

I guess it depends. Are you simply displaying data within something like a GridView? If so, then just use GridView.DataBind() and set your Parameters within the SqlDataSource.Selecting event. You could also set up your Parameters to be ControlParameters and point them directly to your TextBoxes.

|||

well i had done that already. it was just the code behind i needed. i didnt put any code and at runtime i clicked the button and it posted to the server. so i guess thats all i need. thanks for the input though

Possible to make a procedure "sleep" or run periodically?

Is it possible to put a "sleep" command in a stored procedure.
I would like to write a procedure that periodically checks some things in my
database, but I don't want them to suck up a lot of resources. If I could
write a procedure that runs in a loop with a 5 minute sleep command, that
would do it.
Or is this the wrong approach in SQL Server? I realize I could set up a
separate scheduled task that executes a procedure using ISQL. Is this a
better method?
Rick Harrison.I don't know enough about the particual need to know if there is a better
way or is this is a good way... I've done things like this in the past using
the WAITFOR command. You'll find info in BOL...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Rick Harrrison" <rick@.knowware.com> wrote in message
news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Is it possible to put a "sleep" command in a stored procedure.
> I would like to write a procedure that periodically checks some things in
my
> database, but I don't want them to suck up a lot of resources. If I could
> write a procedure that runs in a loop with a 5 minute sleep command, that
> would do it.
> Or is this the wrong approach in SQL Server? I realize I could set up a
> separate scheduled task that executes a procedure using ISQL. Is this a
> better method?
> Rick Harrison.
>|||Use SQL Server Agent to schedule the stored procedure?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rick Harrrison" <rick@.knowware.com> wrote in message
news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Is it possible to put a "sleep" command in a stored procedure.
> I would like to write a procedure that periodically checks some things in
my
> database, but I don't want them to suck up a lot of resources. If I could
> write a procedure that runs in a loop with a 5 minute sleep command, that
> would do it.
> Or is this the wrong approach in SQL Server? I realize I could set up a
> separate scheduled task that executes a procedure using ISQL. Is this a
> better method?
> Rick Harrison.
>|||Hi Rick,
Thank you for using MSDN Newsgroup! I think Aaron and Brian have point out ways to run the
stored procedure (SP) on a schedule basis.
Based on my experience, it's better to use scheduled job IF you want to run your SP in a 5
minutes loop (it does handle that gracefully). The WAITFOR statement is also a good method
to implement this, however, it can only work after a specified time interval has passed or a
specified time is reached, unless you use an explicit loop in your SP.
Additionally, the stored procedure will remain suspended until the WAITFOR completes, this
may affect the performance. However, it can easily be embedded in your script and is not
Agent related. So it depends on your scenario and the needs you want to meet.
Rick, does this answer your question? If there is anything more I can do to assist you, please
feel free to post it in the group.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks! WAITFOR is what I was looking for. I did searches using "sleep" and
"pause", but I didn't think of "wait" or "delay".
Rick.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:Osu%23o1QxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I don't know enough about the particual need to know if there is a better
> way or is this is a good way... I've done things like this in the past
using
> the WAITFOR command. You'll find info in BOL...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Rick Harrrison" <rick@.knowware.com> wrote in message
> news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> > Is it possible to put a "sleep" command in a stored procedure.
> >
> > I would like to write a procedure that periodically checks some things
in
> my
> > database, but I don't want them to suck up a lot of resources. If I
could
> > write a procedure that runs in a loop with a 5 minute sleep command,
that
> > would do it.
> >
> > Or is this the wrong approach in SQL Server? I realize I could set up a
> > separate scheduled task that executes a procedure using ISQL. Is this a
> > better method?
> >
> > Rick Harrison.
> >
> >
>|||Thanks! Somehow I had overlooked this feature. I knew it was possible to
schedule maintenance plans, which I do, but I didn't know you could run any
procedure on a schedule.
Rick.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OEAHM7QxDHA.1088@.tk2msftngp13.phx.gbl...
> Use SQL Server Agent to schedule the stored procedure?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Rick Harrrison" <rick@.knowware.com> wrote in message
> news:uGKLBEQxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> > Is it possible to put a "sleep" command in a stored procedure.
> >
> > I would like to write a procedure that periodically checks some things
in
> my
> > database, but I don't want them to suck up a lot of resources. If I
could
> > write a procedure that runs in a loop with a 5 minute sleep command,
that
> > would do it.
> >
> > Or is this the wrong approach in SQL Server? I realize I could set up a
> > separate scheduled task that executes a procedure using ISQL. Is this a
> > better method?
> >
> > Rick Harrison.
> >
> >
>|||Both techniques will be useful for me. Thank you.
""Billy Yao [MSFT]"" <v-binyao@.online.microsoft.com> wrote in message
news:FTG3OcWxDHA.424@.cpmsftngxa07.phx.gbl...
> Hi Rick,
> Thank you for using MSDN Newsgroup! I think Aaron and Brian have point out
ways to run the
> stored procedure (SP) on a schedule basis.
> Based on my experience, it's better to use scheduled job IF you want to
run your SP in a 5
> minutes loop (it does handle that gracefully). The WAITFOR statement is
also a good method
> to implement this, however, it can only work after a specified time
interval has passed or a
> specified time is reached, unless you use an explicit loop in your SP.
> Additionally, the stored procedure will remain suspended until the WAITFOR
completes, this
> may affect the performance. However, it can easily be embedded in your
script and is not
> Agent related. So it depends on your scenario and the needs you want to
meet.
> Rick, does this answer your question? If there is anything more I can do
to assist you, please
> feel free to post it in the group.
> Best regards,
> Billy Yao
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>

possible SQL query for this function?

i am trying to write an SQL query for an Access database. in this database i have a table with inventory data. what i want is to select distinct item numbers and sum up all of the inventory quantity for those item numbers. i know how to select distinct item numbers OK from this table, but how can i go through the table and sum the quantities for those item numbers and dump all the results into a new table? that would mean for each distinct item number, there would be one resulting sum for that item. so the table would be 2 columns, one with distinct item numbers and one with their sums. thanks for any help!GROUP BY

-PatP|||Since it is F-F-F-Friday and I'm seeing out the last 30 mins...


SELECT MyDisinctCol, Sum(QuantityCol) AS TheTotal
FROM MyTable
GROUP BY MyDisinctCol

Another 20 secs gone!|||that did it! thanks for the help...

Possible Network Error: Write to SQL Server Failed

Any idea on what can cause this error message? "Possible
Network Error: Write to SQL Server Failed"
Is it due to a network disconnect? We are running the
application thru Metaframe XP to SQL Server 2000 with SP 3.
Thanks
Hi
Googling for "Write to SQL Server Failed" turns up lots of hits including:
http://support.microsoft.com/default...b;en-us;109787
http://support.microsoft.com/?id=259775
http://support.microsoft.com/?id=199105
http://support.microsoft.com/?id=315938
It would be useful to know the protocols being used.
John
"mikef" <anonymous@.discussions.microsoft.com> wrote in message
news:1d41c01c453b4$0fcd2da0$a501280a@.phx.gbl...
> Any idea on what can cause this error message? "Possible
> Network Error: Write to SQL Server Failed"
> Is it due to a network disconnect? We are running the
> application thru Metaframe XP to SQL Server 2000 with SP 3.
> Thanks

Possible Network Error: Write to SQL Server Failed

Any idea on what can cause this error message? "Possible
Network Error: Write to SQL Server Failed"
Is it due to a network disconnect? We are running the
application thru Metaframe XP to SQL Server 2000 with SP 3.
ThanksHi
Googling for "Write to SQL Server Failed" turns up lots of hits including:
http://support.microsoft.com/defaul...kb;en-us;109787
http://support.microsoft.com/?id=259775
http://support.microsoft.com/?id=199105
http://support.microsoft.com/?id=315938
It would be useful to know the protocols being used.
John
"mikef" <anonymous@.discussions.microsoft.com> wrote in message
news:1d41c01c453b4$0fcd2da0$a501280a@.phx
.gbl...
> Any idea on what can cause this error message? "Possible
> Network Error: Write to SQL Server Failed"
> Is it due to a network disconnect? We are running the
> application thru Metaframe XP to SQL Server 2000 with SP 3.
> Thanks

Positioning of Stored Procedures

Hi all,

I have been facing this dilemma since when I started coding in asp.net 2.0. I can have Data Access Layer wherein I can write stored procedures to access the data from database. I can create data access object, data table and all other stuff. Also I can create stored procedure in SQL 2000 server, and then access them from the Data Access layer.

Which of the two method is preferable, and why. i have been searching net for answers to this question since long, but could not find anything.

All answers can contribute may be little but invaluable knowledge.

Thanks.

If security is critical, it's best to use stored procedures always because it lowers the attackable area of your database. I think this is what you are asking.

|||

I wanted to know, which is better:

1) Creating stored procedures in SQL Server 2000 and calling them in the Data access Layer, or may be in the code behind straight away.

2) Creating Table Adapters in Data Access Layer, and creating Table Adapter queries and accessing database or may be stored procedures within the Data Access Layer.

I have been informed that if you create the Table Adapter queries, they are equally secure as stored procedures; though i am not pretty confident about it.

Thanks again.

|||

Tell the truth, the issue is depending on your situation.

If you can connect database and your database permittion contol well, you need to do that on db.

if not, don't do that.

|||

Read this an argument against using SP. This will clarify your question as well

http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html

Hope that helps

|||

hello.

well, to be honest, sps aren't really something i'd advocate for crud behavior. in my opinion, using parametrized sql is the way to go. the performance/security bla bla that's has been used for several years is a myth and there are some posts out there that just show it. for instance, there's an old discussion between frans bouma and rob howard that started with a post from rob and a very well answer by frans. i'm putting only frans' post here since it is linked to rob's post.

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

having said that, i'm not saying that there really isn't a place for sps; just saying that kmost of the time the argument for using them are pure myths!

|||

Hi,

I feel creating stored procedures in SQL Server 2000 and calling them in the Data access Layer is better choice. The book Titled :

"Database programming using C#, VB 2005 and SQL Server 2005", Chapter 10:Developing Components for three-tier applications explains this concept.

Let us say we have developed an three-tier application using SQL server 2000. In future, the same application should able to access/insert to Oracle database. In this situation, writing stored procedures at the server level is better.

I will find out further info on this matter.

|||

With the technologies ASP.NET 2 provide, you r always free to choose the way you like (depending upon your handy side). With the nearly the same amount of effort or even less you can still handle the shift to Oracle database.

But that said, your approach of choosing store procedure suppose to be a little faster in most cases.

|||

hello.

ask4jm:

But that said, your approach of choosing store procedure suppose to be a little faster in most cases

again, this is a known myth. read frans' post to see what i'm speaking about.

|||

You r absolutely rite Luis. Unless the database developers wants to give the data through specific routines hiding rest of the infra, store procedures can be completely avoided.

|||

db2Command cmd= db2Commant();

cmd.Connection=con;
param = new DB2Parameter("@.ClientId", DB2Type.Decimal, 8);
((DbCommand)base.dbSelectCommand[0]).Parameters.Add(param);

//Insert Command and parameters
string sqlInserCommand = "ProcCon";

base.dbInsertCommand = new DbCommand[1];
base.dbInsertCommand[0] = new DB2Command(sqlInserCommand);

param = new DB2Parameter("@.Name", DB2Type.VarChar, 100, "ClientName");
((DbCommand)base.dbInsertCommand[0]).Parameters.Add(param);

param = new DB2Parameter("@.Cid", DB2Type.Int);
((DbCommand)base.dbInsertCommand[0]).Parameters.Add(param);