PredVariance for NN

Hi,

We're building a model using the NN algorithm, and had a question about the how the PredVariance value is computed. Our testing data set has ~28K cases, but for some reason when we run our prediction query there are only 10 unique PredVariance values generated. Why doesn't each case with a distinct predicted value have its own PredVariance value?

For e.g. here are 3 different PredValues that all have the same PredVariance (229985900) and each has a TrueVal of 0:

15307.6681537296
17759.1791905724
1843.85682442577

If you need more specific info please let me know.

Thanks.

Hello

PredictVariance outputs the error variance for the subnetwork used in executing the prediction. This is detected during training for each subnetwork and, for all predictions executed on that respective subnetwork, PredictVariance will return the same value (same applies to PredictStdev).

If your target variable is a continuous one, then there are at most 2 subnetworks built for the variable (one for the value, one for the probability of Missing state, assuming your training data contains missing values).

If your target variable is discrete or discretized, then one subnetwork is trained to predict the probability of each individual state (including Missing).

Assuming that a prediction returns TargetValue1, the associated PredictVariance should return always the same value (the error variance of the TargetValue1 subnetwork)

You mention that there are 10 unique PredVariance values generated -- it seems that your target variable is discrete with at least 10 distinct states, is this correct?

thanks

|||

Thanks Bogdan,

Our target variable is actually continuous. That's what initially prompted the question, as we expected a PredVariance to be computed for all distinct target variable values (of which there are as many as there are cases).

Does that clarify the question?

Thanks.

|||

The error variance is not computed for each distinct target variable value, but for each subnetwork.

Here are the steps for one continuous target:

- partition the training set in two blocks (training and holdout -- the HOLDOUT_PERCENTAGE and SAMPLE_SIZE parameters control the size of the partitions)

- Iteratively train one subnetwork based on the training partition, and estimate the error (at each step) based on the holdout partition

- at the end -- compute the error variance of the trained subnetwork over all the cases the holdout partition

Therefore, there is a single variance value for the whole subnetwork that predicts one continuous target.

At prediction time, Predict(Target) runs the subnetwork for the target and returns the result, PredictVariance simply returns the (pre-computed) variance for the respective subnetwork (mapped from the normalized training space to the original input space). Therefore, the value returned by PredictVariance should always be the same.

You mentioned that there are 10 distinct variance values being returned?

|||i'll read your feedback more thoroughly this evening, but I wanted to answer your question...yes, there are 10 distinct PredVariance values.|||

Hi,

It's now clear, based on your response, why we have 10 distinct PredVariance values. We were using 10-fold cross-validation and each fold has a corresponding PredVariance.

Going to follow-up with one additional post/question before we close it out....stay tuned.

Thanks,

mike

PredictSupport

Hi,

Can anyone explain what this function returns? What does the support value represent?

Thanks,

Dave

PredictSupport(<attribute>, <state>) returns the number of cases in the training set that support the predicted state for this attribute. If the state is not specified, the state with the highest predict probability is used. The general idea is that a high probability prediction with a larger support value may be more reliable.|||

Ah right, that makes sense.

Thanks!

sql

prediction in ms sql server 2005

Hey

Does anyone know if the following is possible:

I want to add a column to a table that contains the predicted value according to a decision tree mining model. (I know that this is possible). But now I would like that when a new row is added to this table, and every column except the prediction column is filled in manually, can ms sql server add the predicted value automatically for this row?
I know it is possible to execute a Singleton query for this kind of single prediction, but I would like to integrate this in my data table, because for now my steps would be:
- Create the table with one prediction column
- Add the known values of all columns for one row
- Use singleton query in Mining model prediction tab to know the predicted value
- Fill in the predicted value manually in my table.

I hope my question is clear.

Thanks in advance for the help.

SmileykeYou could probably do this with an INSERT trigger on your SQL Server database table that makes a singleton prediction query via a linked server to the AS server that holds your mining model.|||And how would this query look like?
I mean, you put me in the right direction I think, but I can't make it work.

Thnx|||Please see this article I just posted for details on how to do this: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx|||Ok, I tried this, and it was very helpful, but it still doesn't work here.

Do you have any idea why the first query here works, but the second one doesn't? The error is given at the end:

1st working query:
SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings from [Abalone Training Half]')

2nd not working query:
SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT I AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T')

The error is:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "DMServer" reported an error. The provider did not give any information about the error.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT I AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T" against OLE DB provider "MSOLAP" for linked server "DMServer".

As you notice, the predicted class is here Rings, and the input attributes are sex, length diameter and height.

I really hope you can still help me.

Smileyke|||

I may be wrong, but the Sex column seems TEXT. In this case, shouldn't "SELECT I AS Sex" be actually "SELECT 'I' AS Sex" ?

In this case, your OPENQUERY should look like below (2 single quotes around I )

SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT ''I'' AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T')

|||Thank you. That was indeed the problem.

Now the complete trigger works, so thank you all.

smileyke

Powerbuilder : how to get height value

how can i get height value on autosize column but not RowHeight() 'couse RowHeight() return a row height value not a column height value.
i already try use dw_report.object.MyColumn.height , but its retrun a height value before it resize...
pleasee help
sory my english not goodWrong Thread
Please Delete And Post In Correct Thread

Power/Factorial not working

I am trying to get the value, 3 to the 2.2 power (3^2.222).
Can someone tell me how to do it? The literature says to use:
POWER(3,2.222)
however ths is rounding my values for some reason.Power (X, Y)

GOT IT!

and the key was x.

Power (1.0000, 1) will return 1.0000
Power (1.00, 1) will return 1.00

Hooah!

`Le

Pound Sign Problem

Hi

I have come across a bit of problem with my Web Form. I have standard textbox which inserts a value into a SQL 2000 database. However if I put a pound sign anywhere inside the text box and do the insert it disappears when I try and reshow the value.

I have checked the database field to see if it has been inputted but its no where to be seen.

So what has happened to my pound sign? any ideas?

I am using VS.Net 2003.

Thanks in advanceThat is strange. SQL 2000 does not reject the pound sign. Neither does the text box control. I did several tests, cannot seem to produce your problem. Do you do any kind of massage to your input in your code before it is insert into the database table?

Please post your code.|||Hiya

Sorry for the delay in getting back to you, please find below my code. This problem really does have me miffed

Cmd As New SqlCommand

Conn.Open()
With objCmd
.Connection = Conn
.CommandType = CommandType.Text
.CommandText = "INSERT INTO PhoneList (FirstName, Surname) " & _
" VALUES (@.FirstName, @.Surname)"

.Parameters.Clear()
.Parameters.Add("@.FirstName", SqlDbType.VarChar).Value = txtFirstName.Text
.Parameters.Add("@.Surname", SqlDbType.VarChar).Value = txtSurname.Text
.ExecuteNonQuery()
End With
Conn.Close()

Field types in theSQL Server 2000 DB are Varchar's. Just thought could this be one of the rasons for the roblem

Thanks in advance|||I have seen this problem before and I think the DataType on the column needs to beText to store ASCII characters.|||Hi PD

I have changed the data type to Text in my SQL DB but I still get the same problem

It just seems really odd, every other character works including !"$%^&*()

but the £ just does not get stored|||I was sleeping when I read your post, I thought you meant the # sign.

Themonetary data types MONEY and SMALLMONEY can accept many national currency symbols like the British pound, the Euro and others.|||How about if you use NVarChar instead?
Although you shouldn't have to go unicode in order to save the pound sign...

Regards
Fredrik|||Hi Fredrik

I have tried changing it to NvarChar and still didnt have any joy. I have even searched around on teh web and cant seem to find anybody else who has had that problem

Just wondering can anybody else replicate this problem there end?

I dont seem to have any hair left..... i think ive pulled it all out|||OK the only way I can seem to get round the problem is by inputting the HTML equvilant of a £ sign which is £

So when this is rendered it appears as a £

But im sure this cant be a solution.... can it?

I even tried to set validateRequest="false", but did not have any joy|||I am sorry. I thought it was # as well.
I did a test. Cannot re-produce the problem. I am using Varchar for the data field.

Did you try to debug and see whether the pound value was passed in correctly?|||Here is my test code in C#. I just have a textbox at the front end.

conn.Open ();
string strInsert = "Insert Into Chun (Test) Values (@.chun)";
SqlCommand cmd = new SqlCommand (strInsert, conn);
SqlParameter p = new SqlParameter("@.chun",SqlDbType.VarChar,50);
p.Value = txtPound.Text;
p.Direction = ParameterDirection.Input;
cmd.Parameters.Add (p);
cmd.ExecuteNonQuery ();
conn.Close ();

Here is the value I entered in the text box:

£5432

The value is stored and retrieve displayed as it is.|||I have a similar issue when I pass the # as part of a query string! It chops everything off after the pound sign!|||I did eventually manage to solve my pound sign problem, it was a problem with the globalization setting in the web.config.

Try reading the below article and see if it helps

http://www.howtodothings.com/showarticle.asp?article=755

Good luck

Potential Concurrency Issue?

Hi,
I want to know if the below mentioned pseudo code can pose a
concurrency problem.
declare @.string
CREATE TABLE #temp
(
name VARCHAR(100),
value VARCHAR(100)
)
--INSERT some value
--Call another stored procedure that takes the table name and a string
EXEC usp_replace '#temp',@.string OUTPUT
// the usp_replace sp replaces occurence of 'name' with 'values' as per
entries in #temp
Now the question is that since im passing the table name and using it
to query in the usp_replace could this mess up the values if multiple
concurrent users execute it?
Would really appreciate any thoughts on this. Thanks.
Hi
If you have created the temporary table at the outer level it will be
available to the inner level procedure, therefore it should not be necessary
to pass the table name. For more on temporary tables read Books Online or at
http://msdn.microsoft.com/library/de...eate2_8g9x.asp
If many processes are creating/dropping temporary tables you can get
contention on tempdb, this can be limited to some degree by making sure that
tempdb is on it's own discs. Without knowing more about what you are trying
to achieve it is not possible to suggest an alternative approach.
John
"Rishi" wrote:

> Hi,
> I want to know if the below mentioned pseudo code can pose a
> concurrency problem.
> declare @.string
> CREATE TABLE #temp
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> --INSERT some value
> --Call another stored procedure that takes the table name and a string
> EXEC usp_replace '#temp',@.string OUTPUT
> // the usp_replace sp replaces occurence of 'name' with 'values' as per
> entries in #temp
>
> Now the question is that since im passing the table name and using it
> to query in the usp_replace could this mess up the values if multiple
> concurrent users execute it?
> Would really appreciate any thoughts on this. Thanks.
>
|||Thanks John!
to be more precise on what im tryin to achieve is :
To create a generic stored procedure that replace the occurence of
certain "place holders" in a string.
The placeholders' values are stored in a table as a name value pair.
This table can be either generates on the fly as a temp table or be a
permanent table.
e.g. the string is '<name> who is <age> years old lives in <city>'
the temp table will be
name | value
<name> | jack
<age> | 23
<city> | NY
appreciate your help, thanks!
John Bell wrote:[vbcol=seagreen]
> Hi
> If you have created the temporary table at the outer level it will be
> available to the inner level procedure, therefore it should not be necessary
> to pass the table name. For more on temporary tables read Books Online or at
> http://msdn.microsoft.com/library/de...eate2_8g9x.asp
> If many processes are creating/dropping temporary tables you can get
> contention on tempdb, this can be limited to some degree by making sure that
> tempdb is on it's own discs. Without knowing more about what you are trying
> to achieve it is not possible to suggest an alternative approach.
> John
> "Rishi" wrote:

Potential Concurrency Issue?

Hi,
I want to know if the below mentioned pseudo code can pose a
concurrency problem.
declare @.string
CREATE TABLE #temp
(
name VARCHAR(100),
value VARCHAR(100)
)
--INSERT some value
--Call another stored procedure that takes the table name and a string
EXEC usp_replace '#temp',@.string OUTPUT
// the usp_replace sp replaces occurence of 'name' with 'values' as per
entries in #temp
Now the question is that since im passing the table name and using it
to query in the usp_replace could this mess up the values if multiple
concurrent users execute it?
Would really appreciate any thoughts on this. Thanks.Hi
If you have created the temporary table at the outer level it will be
available to the inner level procedure, therefore it should not be necessary
to pass the table name. For more on temporary tables read Books Online or at
http://msdn.microsoft.com/library/d... />
2_8g9x.asp
If many processes are creating/dropping temporary tables you can get
contention on tempdb, this can be limited to some degree by making sure that
tempdb is on it's own discs. Without knowing more about what you are trying
to achieve it is not possible to suggest an alternative approach.
John
"Rishi" wrote:

> Hi,
> I want to know if the below mentioned pseudo code can pose a
> concurrency problem.
> declare @.string
> CREATE TABLE #temp
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> --INSERT some value
> --Call another stored procedure that takes the table name and a string
> EXEC usp_replace '#temp',@.string OUTPUT
> // the usp_replace sp replaces occurence of 'name' with 'values' as per
> entries in #temp
>
> Now the question is that since im passing the table name and using it
> to query in the usp_replace could this mess up the values if multiple
> concurrent users execute it?
> Would really appreciate any thoughts on this. Thanks.
>|||Thanks John!
to be more precise on what im tryin to achieve is :
To create a generic stored procedure that replace the occurence of
certain "place holders" in a string.
The placeholders' values are stored in a table as a name value pair.
This table can be either generates on the fly as a temp table or be a
permanent table.
e.g. the string is '<name> who is <age> years old lives in <city>'
the temp table will be
name | value
--
<name> | jack
<age> | 23
<city> | NY
appreciate your help, thanks!
John Bell wrote:[vbcol=seagreen]
> Hi
> If you have created the temporary table at the outer level it will be
> available to the inner level procedure, therefore it should not be necessa
ry
> to pass the table name. For more on temporary tables read Books Online or
at
> http://msdn.microsoft.com/library/d...>
te2_8g9x.asp
> If many processes are creating/dropping temporary tables you can get
> contention on tempdb, this can be limited to some degree by making sure th
at
> tempdb is on it's own discs. Without knowing more about what you are tryin
g
> to achieve it is not possible to suggest an alternative approach.
> John
> "Rishi" wrote:
>|||Hi
If these are just values then you should be able to join to this table and
not need to resort to dynamic SQL. If you wish to change the SQL Statement
e.g. column names then
see
http://msdn.microsoft.com/library/d.../>
ez_2h7w.asp
but still pass variables for the values. You may want to look at
http://www.sommarskog.se/dynamic_sql.html and
http://www.sommarskog.se/dyn-search.html
John
"Rishi" wrote:

> Thanks John!
> to be more precise on what im tryin to achieve is :
> To create a generic stored procedure that replace the occurence of
> certain "place holders" in a string.
> The placeholders' values are stored in a table as a name value pair.
> This table can be either generates on the fly as a temp table or be a
> permanent table.
> e.g. the string is '<name> who is <age> years old lives in <city>'
> the temp table will be
> name | value
> --
> <name> | jack
> <age> | 23
> <city> | NY
> appreciate your help, thanks!
>
> John Bell wrote:
>|||Hi,
the dynamic sql thing looks great, but i dont know how it will
rescue me as a n aternative in this case. could you please throw some
more light on this.
probably my last post wasnt very clear, so the deal is:
i have a table which has name value pairs.
i have a string which has names as place holders for the values
in the string i need to replace those names with the values as found in
the table.
i want a general reusable stored proc to acheive this.
the way i am doing it as desribed in the first post works, but i want
to know if that will pose any concurrency problems.
since i want the sp to general and reusable i dont want to hard code
and use the table name in the usp_replace sp.
Please let me know your thoughts.
Thanks,
Rishi.
John Bell wrote:[vbcol=seagreen]
> Hi
> If these are just values then you should be able to join to this table and
> not need to resort to dynamic SQL. If you wish to change the SQL Statement
> e.g. column names then
> see
> http://msdn.microsoft.com/library/d...
a-ez_2h7w.asp
> but still pass variables for the values. You may want to look at
> http://www.sommarskog.se/dynamic_sql.html and
> http://www.sommarskog.se/dyn-search.html
> John
> "Rishi" wrote:
>|||Hi
Can you give an example SQL Statement and the values in the table that will
be used and the code to usp_replace?
John
"Rishi" wrote:

> Hi,
> the dynamic sql thing looks great, but i dont know how it will
> rescue me as a n aternative in this case. could you please throw some
> more light on this.
> probably my last post wasnt very clear, so the deal is:
> i have a table which has name value pairs.
> i have a string which has names as place holders for the values
> in the string i need to replace those names with the values as found in
> the table.
> i want a general reusable stored proc to acheive this.
> the way i am doing it as desribed in the first post works, but i want
> to know if that will pose any concurrency problems.
> since i want the sp to general and reusable i dont want to hard code
> and use the table name in the usp_replace sp.
> Please let me know your thoughts.
> Thanks,
> Rishi.
>
> John Bell wrote:
>|||Sure John! here it is:
--- in main stored proc
----
--Some code goes here
SET @.string = ' <attribute1> some text here <attribute2> some text here
<property1>'
CREATE TABLE #prop
(
name VARCHAR(100),
value VARCHAR(100)
)
-- Get Standard Attributes
INSERT INTO #prop
--some dynamic name value pairs returned from
some other calculation / query
-- Get user defined properties
INSERT INTO #prop
--some dynamic name value pairs returned from
some other calculation / query
--
--
--
EXEC usp_replace '#prop',@.string output
----
----
---
usp_replace----
ALTER PROCEDURE [dbo].[usp_replace]
@.tablename VARCHAR(100) ,
@.string VARCHAR(1000) OUTPUT
AS
BEGIN
--IF(nullif(@.string,'') is null) RETURN
DECLARE @.tableqry VARCHAR(100)
DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
SET @.tableqry = 'SELECT * FROM '+@.tablename
INSERT INTO @.temp
EXEC (@.tableqry)
DECLARE c CURSOR FOR
SELECT * FROM @.temp
DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
OPEN c
FETCH next FROM c INTO @.name,@.value
WHILE @.@.fetch_status =0 BEGIN
SET @.string = replace(@.string,@.name,coalesce(@.value,'N
ULL'))
FETCH next FROM c INTO @.name,@.value
END
CLOSE c
DEALLOCATE c
END
---
usp_replace----
the table #prop looks like this after values are inserted in it in the
main sp.
name | value
---
<attribute1> | attributvalue
<attribute2 > | attribute2value
----
--
finallly the string looks like this after returned from sp_replace
' attributvalue some text here attribute2value some text here
<property1>'
----
--
John Bell wrote:[vbcol=seagreen]
> Hi
> Can you give an example SQL Statement and the values in the table that wil
l
> be used and the code to usp_replace?
> John
> "Rishi" wrote:
>|||Hi
As your strings with the placeholders are not SQL Statements then don't
think I can suggest a different alternative.
I am not sure why you need to dynamic SQL for returning values (or to pass
the t able name) from your temporary table, unless the substitutions will be
embeded it should not be necessary! You may want to try ordering the
processing by the length of the value to try and avoid replacing already
substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
table variable? For you string size you will may have truncation if you have
more than 9 substitutions.
If this is just going to be sent to the client you may just want to do the
replacement on the client.
John
"Rishi" wrote:

> Sure John! here it is:
>
> --- in main stored proc
> ----
> --Some code goes here
> SET @.string = ' <attribute1> some text here <attribute2> some text here
> <property1>'
> CREATE TABLE #prop
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> -- Get Standard Attributes
> INSERT INTO #prop
> --some dynamic name value pairs returned from
> some other calculation / query
> -- Get user defined properties
> INSERT INTO #prop
> --some dynamic name value pairs returned from
> some other calculation / query
> --
> --
> --
> EXEC usp_replace '#prop',@.string output
>
>
> ----
----
> ---
> usp_replace----
> ALTER PROCEDURE [dbo].[usp_replace]
> @.tablename VARCHAR(100) ,
> @.string VARCHAR(1000) OUTPUT
> AS
> BEGIN
> --IF(nullif(@.string,'') is null) RETURN
> DECLARE @.tableqry VARCHAR(100)
> DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
> SET @.tableqry = 'SELECT * FROM '+@.tablename
> INSERT INTO @.temp
> EXEC (@.tableqry)
> DECLARE c CURSOR FOR
> SELECT * FROM @.temp
> DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
> OPEN c
> FETCH next FROM c INTO @.name,@.value
> WHILE @.@.fetch_status =0 BEGIN
> SET @.string = replace(@.string,@.name,coalesce(@.value,'N
ULL'))
> FETCH next FROM c INTO @.name,@.value
> END
> CLOSE c
> DEALLOCATE c
>
> END
> ---
> usp_replace----
>
> the table #prop looks like this after values are inserted in it in the
> main sp.
> name | value
> ---
> <attribute1> | attributvalue
> <attribute2 > | attribute2value
> ----
--
>
> finallly the string looks like this after returned from sp_replace
> ' attributvalue some text here attribute2value some text here
> <property1>'
> ----
--
>
>
> John Bell wrote:
>|||Thanks a lot John!
Yes this is sql 2005 and your other assumptions are right too. the
reason for a saperate sp is just to be able to reuse it in other sps
where ever i might need substitution, whcih btw i would bcoz of the
crazy app we are dev .
However i didnt quite understand why would the string be truncated for
more than 9 substitutions?
And back to the very first question, could this - sending the table
name - cause a concurrency issue?
Rishi.
John Bell wrote:[vbcol=seagreen]
> Hi
> As your strings with the placeholders are not SQL Statements then don't
> think I can suggest a different alternative.
> I am not sure why you need to dynamic SQL for returning values (or to pass
> the t able name) from your temporary table, unless the substitutions will
be
> embeded it should not be necessary! You may want to try ordering the
> processing by the length of the value to try and avoid replacing already
> substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
> table variable? For you string size you will may have truncation if you ha
ve
> more than 9 substitutions.
> If this is just going to be sent to the client you may just want to do the
> replacement on the client.
> John
> "Rishi" wrote:
>|||Hi Rishi
As the value of the substitute string can be 100 characters and your string
is only 1000 characters you will potentially start loosing information if yo
u
do more than 9 substitutions (i.e > 900 characters). Look at using
varchar(MAX) for your string and possibly reducing the value size.
Will there always be a fixed number of substitutions?
John
"Rishi" wrote:

> Thanks a lot John!
> Yes this is sql 2005 and your other assumptions are right too. the
> reason for a saperate sp is just to be able to reuse it in other sps
> where ever i might need substitution, whcih btw i would bcoz of the
> crazy app we are dev .
> However i didnt quite understand why would the string be truncated for
> more than 9 substitutions?
> And back to the very first question, could this - sending the table
> name - cause a concurrency issue?
> Rishi.
>
> John Bell wrote:
>

Potential Concurrency Issue?

Hi,
I want to know if the below mentioned pseudo code can pose a
concurrency problem.
declare @.string
CREATE TABLE #temp
(
name VARCHAR(100),
value VARCHAR(100)
)
--INSERT some value
--Call another stored procedure that takes the table name and a string
EXEC usp_replace '#temp',@.string OUTPUT
// the usp_replace sp replaces occurence of 'name' with 'values' as per
entries in #temp
Now the question is that since im passing the table name and using it
to query in the usp_replace could this mess up the values if multiple
concurrent users execute it?
Would really appreciate any thoughts on this. Thanks.Hi
If you have created the temporary table at the outer level it will be
available to the inner level procedure, therefore it should not be necessary
to pass the table name. For more on temporary tables read Books Online or at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
If many processes are creating/dropping temporary tables you can get
contention on tempdb, this can be limited to some degree by making sure that
tempdb is on it's own discs. Without knowing more about what you are trying
to achieve it is not possible to suggest an alternative approach.
John
"Rishi" wrote:
> Hi,
> I want to know if the below mentioned pseudo code can pose a
> concurrency problem.
> declare @.string
> CREATE TABLE #temp
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> --INSERT some value
> --Call another stored procedure that takes the table name and a string
> EXEC usp_replace '#temp',@.string OUTPUT
> // the usp_replace sp replaces occurence of 'name' with 'values' as per
> entries in #temp
>
> Now the question is that since im passing the table name and using it
> to query in the usp_replace could this mess up the values if multiple
> concurrent users execute it?
> Would really appreciate any thoughts on this. Thanks.
>|||Thanks John!
to be more precise on what im tryin to achieve is :
To create a generic stored procedure that replace the occurence of
certain "place holders" in a string.
The placeholders' values are stored in a table as a name value pair.
This table can be either generates on the fly as a temp table or be a
permanent table.
e.g. the string is '<name> who is <age> years old lives in <city>'
the temp table will be
name | value
--
<name> | jack
<age> | 23
<city> | NY
appreciate your help, thanks!
John Bell wrote:
> Hi
> If you have created the temporary table at the outer level it will be
> available to the inner level procedure, therefore it should not be necessary
> to pass the table name. For more on temporary tables read Books Online or at
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> If many processes are creating/dropping temporary tables you can get
> contention on tempdb, this can be limited to some degree by making sure that
> tempdb is on it's own discs. Without knowing more about what you are trying
> to achieve it is not possible to suggest an alternative approach.
> John
> "Rishi" wrote:
> > Hi,
> > I want to know if the below mentioned pseudo code can pose a
> > concurrency problem.
> >
> > declare @.string
> >
> > CREATE TABLE #temp
> > (
> > name VARCHAR(100),
> > value VARCHAR(100)
> > )
> > --INSERT some value
> >
> > --Call another stored procedure that takes the table name and a string
> >
> > EXEC usp_replace '#temp',@.string OUTPUT
> >
> > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > entries in #temp
> >
> >
> >
> > Now the question is that since im passing the table name and using it
> > to query in the usp_replace could this mess up the values if multiple
> > concurrent users execute it?
> >
> > Would really appreciate any thoughts on this. Thanks.
> >
> >|||Hi
If these are just values then you should be able to join to this table and
not need to resort to dynamic SQL. If you wish to change the SQL Statement
e.g. column names then
see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
but still pass variables for the values. You may want to look at
http://www.sommarskog.se/dynamic_sql.html and
http://www.sommarskog.se/dyn-search.html
John
"Rishi" wrote:
> Thanks John!
> to be more precise on what im tryin to achieve is :
> To create a generic stored procedure that replace the occurence of
> certain "place holders" in a string.
> The placeholders' values are stored in a table as a name value pair.
> This table can be either generates on the fly as a temp table or be a
> permanent table.
> e.g. the string is '<name> who is <age> years old lives in <city>'
> the temp table will be
> name | value
> --
> <name> | jack
> <age> | 23
> <city> | NY
> appreciate your help, thanks!
>
> John Bell wrote:
> > Hi
> >
> > If you have created the temporary table at the outer level it will be
> > available to the inner level procedure, therefore it should not be necessary
> > to pass the table name. For more on temporary tables read Books Online or at
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> >
> > If many processes are creating/dropping temporary tables you can get
> > contention on tempdb, this can be limited to some degree by making sure that
> > tempdb is on it's own discs. Without knowing more about what you are trying
> > to achieve it is not possible to suggest an alternative approach.
> >
> > John
> >
> > "Rishi" wrote:
> >
> > > Hi,
> > > I want to know if the below mentioned pseudo code can pose a
> > > concurrency problem.
> > >
> > > declare @.string
> > >
> > > CREATE TABLE #temp
> > > (
> > > name VARCHAR(100),
> > > value VARCHAR(100)
> > > )
> > > --INSERT some value
> > >
> > > --Call another stored procedure that takes the table name and a string
> > >
> > > EXEC usp_replace '#temp',@.string OUTPUT
> > >
> > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > entries in #temp
> > >
> > >
> > >
> > > Now the question is that since im passing the table name and using it
> > > to query in the usp_replace could this mess up the values if multiple
> > > concurrent users execute it?
> > >
> > > Would really appreciate any thoughts on this. Thanks.
> > >
> > >
>|||Hi,
the dynamic sql thing looks great, but i dont know how it will
rescue me as a n aternative in this case. could you please throw some
more light on this.
probably my last post wasnt very clear, so the deal is:
i have a table which has name value pairs.
i have a string which has names as place holders for the values
in the string i need to replace those names with the values as found in
the table.
i want a general reusable stored proc to acheive this.
the way i am doing it as desribed in the first post works, but i want
to know if that will pose any concurrency problems.
since i want the sp to general and reusable i dont want to hard code
and use the table name in the usp_replace sp.
Please let me know your thoughts.
Thanks,
Rishi.
John Bell wrote:
> Hi
> If these are just values then you should be able to join to this table and
> not need to resort to dynamic SQL. If you wish to change the SQL Statement
> e.g. column names then
> see
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> but still pass variables for the values. You may want to look at
> http://www.sommarskog.se/dynamic_sql.html and
> http://www.sommarskog.se/dyn-search.html
> John
> "Rishi" wrote:
> > Thanks John!
> > to be more precise on what im tryin to achieve is :
> >
> > To create a generic stored procedure that replace the occurence of
> > certain "place holders" in a string.
> > The placeholders' values are stored in a table as a name value pair.
> > This table can be either generates on the fly as a temp table or be a
> > permanent table.
> >
> > e.g. the string is '<name> who is <age> years old lives in <city>'
> >
> > the temp table will be
> > name | value
> > --
> > <name> | jack
> > <age> | 23
> > <city> | NY
> >
> > appreciate your help, thanks!
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > If you have created the temporary table at the outer level it will be
> > > available to the inner level procedure, therefore it should not be necessary
> > > to pass the table name. For more on temporary tables read Books Online or at
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > >
> > > If many processes are creating/dropping temporary tables you can get
> > > contention on tempdb, this can be limited to some degree by making sure that
> > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > to achieve it is not possible to suggest an alternative approach.
> > >
> > > John
> > >
> > > "Rishi" wrote:
> > >
> > > > Hi,
> > > > I want to know if the below mentioned pseudo code can pose a
> > > > concurrency problem.
> > > >
> > > > declare @.string
> > > >
> > > > CREATE TABLE #temp
> > > > (
> > > > name VARCHAR(100),
> > > > value VARCHAR(100)
> > > > )
> > > > --INSERT some value
> > > >
> > > > --Call another stored procedure that takes the table name and a string
> > > >
> > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > >
> > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > entries in #temp
> > > >
> > > >
> > > >
> > > > Now the question is that since im passing the table name and using it
> > > > to query in the usp_replace could this mess up the values if multiple
> > > > concurrent users execute it?
> > > >
> > > > Would really appreciate any thoughts on this. Thanks.
> > > >
> > > >
> >
> >|||Hi
Can you give an example SQL Statement and the values in the table that will
be used and the code to usp_replace?
John
"Rishi" wrote:
> Hi,
> the dynamic sql thing looks great, but i dont know how it will
> rescue me as a n aternative in this case. could you please throw some
> more light on this.
> probably my last post wasnt very clear, so the deal is:
> i have a table which has name value pairs.
> i have a string which has names as place holders for the values
> in the string i need to replace those names with the values as found in
> the table.
> i want a general reusable stored proc to acheive this.
> the way i am doing it as desribed in the first post works, but i want
> to know if that will pose any concurrency problems.
> since i want the sp to general and reusable i dont want to hard code
> and use the table name in the usp_replace sp.
> Please let me know your thoughts.
> Thanks,
> Rishi.
>
> John Bell wrote:
> > Hi
> >
> > If these are just values then you should be able to join to this table and
> > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > e.g. column names then
> > see
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > but still pass variables for the values. You may want to look at
> > http://www.sommarskog.se/dynamic_sql.html and
> > http://www.sommarskog.se/dyn-search.html
> >
> > John
> >
> > "Rishi" wrote:
> >
> > > Thanks John!
> > > to be more precise on what im tryin to achieve is :
> > >
> > > To create a generic stored procedure that replace the occurence of
> > > certain "place holders" in a string.
> > > The placeholders' values are stored in a table as a name value pair.
> > > This table can be either generates on the fly as a temp table or be a
> > > permanent table.
> > >
> > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > >
> > > the temp table will be
> > > name | value
> > > --
> > > <name> | jack
> > > <age> | 23
> > > <city> | NY
> > >
> > > appreciate your help, thanks!
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > If you have created the temporary table at the outer level it will be
> > > > available to the inner level procedure, therefore it should not be necessary
> > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > >
> > > > If many processes are creating/dropping temporary tables you can get
> > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > to achieve it is not possible to suggest an alternative approach.
> > > >
> > > > John
> > > >
> > > > "Rishi" wrote:
> > > >
> > > > > Hi,
> > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > concurrency problem.
> > > > >
> > > > > declare @.string
> > > > >
> > > > > CREATE TABLE #temp
> > > > > (
> > > > > name VARCHAR(100),
> > > > > value VARCHAR(100)
> > > > > )
> > > > > --INSERT some value
> > > > >
> > > > > --Call another stored procedure that takes the table name and a string
> > > > >
> > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > >
> > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > entries in #temp
> > > > >
> > > > >
> > > > >
> > > > > Now the question is that since im passing the table name and using it
> > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > concurrent users execute it?
> > > > >
> > > > > Would really appreciate any thoughts on this. Thanks.
> > > > >
> > > > >
> > >
> > >
>|||Sure John! here it is:
--- in main stored proc
----
--Some code goes here
SET @.string = ' <attribute1> some text here <attribute2> some text here
<property1>'
CREATE TABLE #prop
(
name VARCHAR(100),
value VARCHAR(100)
)
-- Get Standard Attributes
INSERT INTO #prop
--some dynamic name value pairs returned from
some other calculation / query
-- Get user defined properties
INSERT INTO #prop
--some dynamic name value pairs returned from
some other calculation / query
--
--
--
EXEC usp_replace '#prop',@.string output
------
---
usp_replace----
ALTER PROCEDURE [dbo].[usp_replace]
@.tablename VARCHAR(100) ,
@.string VARCHAR(1000) OUTPUT
AS
BEGIN
--IF(nullif(@.string,'') is null) RETURN
DECLARE @.tableqry VARCHAR(100)
DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
SET @.tableqry = 'SELECT * FROM '+@.tablename
INSERT INTO @.temp
EXEC (@.tableqry)
DECLARE c CURSOR FOR
SELECT * FROM @.temp
DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
OPEN c
FETCH next FROM c INTO @.name,@.value
WHILE @.@.fetch_status =0 BEGIN
SET @.string = replace(@.string,@.name,coalesce(@.value,'NULL'))
FETCH next FROM c INTO @.name,@.value
END
CLOSE c
DEALLOCATE c
END
---
usp_replace----
the table #prop looks like this after values are inserted in it in the
main sp.
name | value
---
<attribute1> | attributvalue
<attribute2 > | attribute2value
----
finallly the string looks like this after returned from sp_replace
' attributvalue some text here attribute2value some text here
<property1>'
----
John Bell wrote:
> Hi
> Can you give an example SQL Statement and the values in the table that will
> be used and the code to usp_replace?
> John
> "Rishi" wrote:
> > Hi,
> > the dynamic sql thing looks great, but i dont know how it will
> > rescue me as a n aternative in this case. could you please throw some
> > more light on this.
> > probably my last post wasnt very clear, so the deal is:
> >
> > i have a table which has name value pairs.
> > i have a string which has names as place holders for the values
> > in the string i need to replace those names with the values as found in
> > the table.
> > i want a general reusable stored proc to acheive this.
> > the way i am doing it as desribed in the first post works, but i want
> > to know if that will pose any concurrency problems.
> > since i want the sp to general and reusable i dont want to hard code
> > and use the table name in the usp_replace sp.
> >
> > Please let me know your thoughts.
> > Thanks,
> > Rishi.
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > If these are just values then you should be able to join to this table and
> > > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > > e.g. column names then
> > > see
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > > but still pass variables for the values. You may want to look at
> > > http://www.sommarskog.se/dynamic_sql.html and
> > > http://www.sommarskog.se/dyn-search.html
> > >
> > > John
> > >
> > > "Rishi" wrote:
> > >
> > > > Thanks John!
> > > > to be more precise on what im tryin to achieve is :
> > > >
> > > > To create a generic stored procedure that replace the occurence of
> > > > certain "place holders" in a string.
> > > > The placeholders' values are stored in a table as a name value pair.
> > > > This table can be either generates on the fly as a temp table or be a
> > > > permanent table.
> > > >
> > > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > > >
> > > > the temp table will be
> > > > name | value
> > > > --
> > > > <name> | jack
> > > > <age> | 23
> > > > <city> | NY
> > > >
> > > > appreciate your help, thanks!
> > > >
> > > >
> > > >
> > > > John Bell wrote:
> > > > > Hi
> > > > >
> > > > > If you have created the temporary table at the outer level it will be
> > > > > available to the inner level procedure, therefore it should not be necessary
> > > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > > >
> > > > > If many processes are creating/dropping temporary tables you can get
> > > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > > to achieve it is not possible to suggest an alternative approach.
> > > > >
> > > > > John
> > > > >
> > > > > "Rishi" wrote:
> > > > >
> > > > > > Hi,
> > > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > > concurrency problem.
> > > > > >
> > > > > > declare @.string
> > > > > >
> > > > > > CREATE TABLE #temp
> > > > > > (
> > > > > > name VARCHAR(100),
> > > > > > value VARCHAR(100)
> > > > > > )
> > > > > > --INSERT some value
> > > > > >
> > > > > > --Call another stored procedure that takes the table name and a string
> > > > > >
> > > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > > >
> > > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > > entries in #temp
> > > > > >
> > > > > >
> > > > > >
> > > > > > Now the question is that since im passing the table name and using it
> > > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > > concurrent users execute it?
> > > > > >
> > > > > > Would really appreciate any thoughts on this. Thanks.
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
As your strings with the placeholders are not SQL Statements then don't
think I can suggest a different alternative.
I am not sure why you need to dynamic SQL for returning values (or to pass
the t able name) from your temporary table, unless the substitutions will be
embeded it should not be necessary! You may want to try ordering the
processing by the length of the value to try and avoid replacing already
substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
table variable? For you string size you will may have truncation if you have
more than 9 substitutions.
If this is just going to be sent to the client you may just want to do the
replacement on the client.
John
"Rishi" wrote:
> Sure John! here it is:
>
> --- in main stored proc
> ----
> --Some code goes here
> SET @.string = ' <attribute1> some text here <attribute2> some text here
> <property1>'
> CREATE TABLE #prop
> (
> name VARCHAR(100),
> value VARCHAR(100)
> )
> -- Get Standard Attributes
> INSERT INTO #prop
> --some dynamic name value pairs returned from
> some other calculation / query
> -- Get user defined properties
> INSERT INTO #prop
> --some dynamic name value pairs returned from
> some other calculation / query
> --
> --
> --
> EXEC usp_replace '#prop',@.string output
>
>
> ------
> ---
> usp_replace----
> ALTER PROCEDURE [dbo].[usp_replace]
> @.tablename VARCHAR(100) ,
> @.string VARCHAR(1000) OUTPUT
> AS
> BEGIN
> --IF(nullif(@.string,'') is null) RETURN
> DECLARE @.tableqry VARCHAR(100)
> DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
> SET @.tableqry = 'SELECT * FROM '+@.tablename
> INSERT INTO @.temp
> EXEC (@.tableqry)
> DECLARE c CURSOR FOR
> SELECT * FROM @.temp
> DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
> OPEN c
> FETCH next FROM c INTO @.name,@.value
> WHILE @.@.fetch_status =0 BEGIN
> SET @.string = replace(@.string,@.name,coalesce(@.value,'NULL'))
> FETCH next FROM c INTO @.name,@.value
> END
> CLOSE c
> DEALLOCATE c
>
> END
> ---
> usp_replace----
>
> the table #prop looks like this after values are inserted in it in the
> main sp.
> name | value
> ---
> <attribute1> | attributvalue
> <attribute2 > | attribute2value
> ----
>
> finallly the string looks like this after returned from sp_replace
> ' attributvalue some text here attribute2value some text here
> <property1>'
> ----
>
>
> John Bell wrote:
> > Hi
> >
> > Can you give an example SQL Statement and the values in the table that will
> > be used and the code to usp_replace?
> >
> > John
> >
> > "Rishi" wrote:
> >
> > > Hi,
> > > the dynamic sql thing looks great, but i dont know how it will
> > > rescue me as a n aternative in this case. could you please throw some
> > > more light on this.
> > > probably my last post wasnt very clear, so the deal is:
> > >
> > > i have a table which has name value pairs.
> > > i have a string which has names as place holders for the values
> > > in the string i need to replace those names with the values as found in
> > > the table.
> > > i want a general reusable stored proc to acheive this.
> > > the way i am doing it as desribed in the first post works, but i want
> > > to know if that will pose any concurrency problems.
> > > since i want the sp to general and reusable i dont want to hard code
> > > and use the table name in the usp_replace sp.
> > >
> > > Please let me know your thoughts.
> > > Thanks,
> > > Rishi.
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > If these are just values then you should be able to join to this table and
> > > > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > > > e.g. column names then
> > > > see
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > > > but still pass variables for the values. You may want to look at
> > > > http://www.sommarskog.se/dynamic_sql.html and
> > > > http://www.sommarskog.se/dyn-search.html
> > > >
> > > > John
> > > >
> > > > "Rishi" wrote:
> > > >
> > > > > Thanks John!
> > > > > to be more precise on what im tryin to achieve is :
> > > > >
> > > > > To create a generic stored procedure that replace the occurence of
> > > > > certain "place holders" in a string.
> > > > > The placeholders' values are stored in a table as a name value pair.
> > > > > This table can be either generates on the fly as a temp table or be a
> > > > > permanent table.
> > > > >
> > > > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > > > >
> > > > > the temp table will be
> > > > > name | value
> > > > > --
> > > > > <name> | jack
> > > > > <age> | 23
> > > > > <city> | NY
> > > > >
> > > > > appreciate your help, thanks!
> > > > >
> > > > >
> > > > >
> > > > > John Bell wrote:
> > > > > > Hi
> > > > > >
> > > > > > If you have created the temporary table at the outer level it will be
> > > > > > available to the inner level procedure, therefore it should not be necessary
> > > > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > > > >
> > > > > > If many processes are creating/dropping temporary tables you can get
> > > > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > > > to achieve it is not possible to suggest an alternative approach.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "Rishi" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > > > concurrency problem.
> > > > > > >
> > > > > > > declare @.string
> > > > > > >
> > > > > > > CREATE TABLE #temp
> > > > > > > (
> > > > > > > name VARCHAR(100),
> > > > > > > value VARCHAR(100)
> > > > > > > )
> > > > > > > --INSERT some value
> > > > > > >
> > > > > > > --Call another stored procedure that takes the table name and a string
> > > > > > >
> > > > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > > > >
> > > > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > > > entries in #temp
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Now the question is that since im passing the table name and using it
> > > > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > > > concurrent users execute it?
> > > > > > >
> > > > > > > Would really appreciate any thoughts on this. Thanks.
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>|||Thanks a lot John!
Yes this is sql 2005 and your other assumptions are right too. the
reason for a saperate sp is just to be able to reuse it in other sps
where ever i might need substitution, whcih btw i would bcoz of the
crazy app we are dev .
However i didnt quite understand why would the string be truncated for
more than 9 substitutions?
And back to the very first question, could this - sending the table
name - cause a concurrency issue?
Rishi.
John Bell wrote:
> Hi
> As your strings with the placeholders are not SQL Statements then don't
> think I can suggest a different alternative.
> I am not sure why you need to dynamic SQL for returning values (or to pass
> the t able name) from your temporary table, unless the substitutions will be
> embeded it should not be necessary! You may want to try ordering the
> processing by the length of the value to try and avoid replacing already
> substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
> table variable? For you string size you will may have truncation if you have
> more than 9 substitutions.
> If this is just going to be sent to the client you may just want to do the
> replacement on the client.
> John
> "Rishi" wrote:
> > Sure John! here it is:
> >
> >
> > --- in main stored proc
> > ----
> >
> > --Some code goes here
> >
> > SET @.string = ' <attribute1> some text here <attribute2> some text here
> > <property1>'
> >
> > CREATE TABLE #prop
> > (
> > name VARCHAR(100),
> > value VARCHAR(100)
> > )
> >
> > -- Get Standard Attributes
> > INSERT INTO #prop
> > --some dynamic name value pairs returned from
> > some other calculation / query
> >
> > -- Get user defined properties
> > INSERT INTO #prop
> > --some dynamic name value pairs returned from
> > some other calculation / query
> > --
> > --
> > --
> >
> > EXEC usp_replace '#prop',@.string output
> >
> >
> >
> >
> > ------
> >
> > ---
> > usp_replace----
> >
> > ALTER PROCEDURE [dbo].[usp_replace]
> >
> > @.tablename VARCHAR(100) ,
> > @.string VARCHAR(1000) OUTPUT
> > AS
> > BEGIN
> > --IF(nullif(@.string,'') is null) RETURN
> >
> > DECLARE @.tableqry VARCHAR(100)
> > DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
> >
> > SET @.tableqry = 'SELECT * FROM '+@.tablename
> >
> > INSERT INTO @.temp
> > EXEC (@.tableqry)
> >
> > DECLARE c CURSOR FOR
> > SELECT * FROM @.temp
> >
> > DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
> > OPEN c
> > FETCH next FROM c INTO @.name,@.value
> > WHILE @.@.fetch_status =0 BEGIN
> > SET @.string = replace(@.string,@.name,coalesce(@.value,'NULL'))
> >
> > FETCH next FROM c INTO @.name,@.value
> >
> > END
> > CLOSE c
> > DEALLOCATE c
> >
> >
> > END
> >
> > ---
> > usp_replace----
> >
> >
> > the table #prop looks like this after values are inserted in it in the
> > main sp.
> >
> > name | value
> > ---
> > <attribute1> | attributvalue
> > <attribute2 > | attribute2value
> > ----
> >
> >
> > finallly the string looks like this after returned from sp_replace
> >
> > ' attributvalue some text here attribute2value some text here
> > <property1>'
> > ----
> >
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Can you give an example SQL Statement and the values in the table that will
> > > be used and the code to usp_replace?
> > >
> > > John
> > >
> > > "Rishi" wrote:
> > >
> > > > Hi,
> > > > the dynamic sql thing looks great, but i dont know how it will
> > > > rescue me as a n aternative in this case. could you please throw some
> > > > more light on this.
> > > > probably my last post wasnt very clear, so the deal is:
> > > >
> > > > i have a table which has name value pairs.
> > > > i have a string which has names as place holders for the values
> > > > in the string i need to replace those names with the values as found in
> > > > the table.
> > > > i want a general reusable stored proc to acheive this.
> > > > the way i am doing it as desribed in the first post works, but i want
> > > > to know if that will pose any concurrency problems.
> > > > since i want the sp to general and reusable i dont want to hard code
> > > > and use the table name in the usp_replace sp.
> > > >
> > > > Please let me know your thoughts.
> > > > Thanks,
> > > > Rishi.
> > > >
> > > >
> > > >
> > > > John Bell wrote:
> > > > > Hi
> > > > >
> > > > > If these are just values then you should be able to join to this table and
> > > > > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > > > > e.g. column names then
> > > > > see
> > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > > > > but still pass variables for the values. You may want to look at
> > > > > http://www.sommarskog.se/dynamic_sql.html and
> > > > > http://www.sommarskog.se/dyn-search.html
> > > > >
> > > > > John
> > > > >
> > > > > "Rishi" wrote:
> > > > >
> > > > > > Thanks John!
> > > > > > to be more precise on what im tryin to achieve is :
> > > > > >
> > > > > > To create a generic stored procedure that replace the occurence of
> > > > > > certain "place holders" in a string.
> > > > > > The placeholders' values are stored in a table as a name value pair.
> > > > > > This table can be either generates on the fly as a temp table or be a
> > > > > > permanent table.
> > > > > >
> > > > > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > > > > >
> > > > > > the temp table will be
> > > > > > name | value
> > > > > > --
> > > > > > <name> | jack
> > > > > > <age> | 23
> > > > > > <city> | NY
> > > > > >
> > > > > > appreciate your help, thanks!
> > > > > >
> > > > > >
> > > > > >
> > > > > > John Bell wrote:
> > > > > > > Hi
> > > > > > >
> > > > > > > If you have created the temporary table at the outer level it will be
> > > > > > > available to the inner level procedure, therefore it should not be necessary
> > > > > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > > > > >
> > > > > > > If many processes are creating/dropping temporary tables you can get
> > > > > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > > > > to achieve it is not possible to suggest an alternative approach.
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > > "Rishi" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > > > > concurrency problem.
> > > > > > > >
> > > > > > > > declare @.string
> > > > > > > >
> > > > > > > > CREATE TABLE #temp
> > > > > > > > (
> > > > > > > > name VARCHAR(100),
> > > > > > > > value VARCHAR(100)
> > > > > > > > )
> > > > > > > > --INSERT some value
> > > > > > > >
> > > > > > > > --Call another stored procedure that takes the table name and a string
> > > > > > > >
> > > > > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > > > > >
> > > > > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > > > > entries in #temp
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Now the question is that since im passing the table name and using it
> > > > > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > > > > concurrent users execute it?
> > > > > > > >
> > > > > > > > Would really appreciate any thoughts on this. Thanks.
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi Rishi
As the value of the substitute string can be 100 characters and your string
is only 1000 characters you will potentially start loosing information if you
do more than 9 substitutions (i.e > 900 characters). Look at using
varchar(MAX) for your string and possibly reducing the value size.
Will there always be a fixed number of substitutions?
John
"Rishi" wrote:
> Thanks a lot John!
> Yes this is sql 2005 and your other assumptions are right too. the
> reason for a saperate sp is just to be able to reuse it in other sps
> where ever i might need substitution, whcih btw i would bcoz of the
> crazy app we are dev .
> However i didnt quite understand why would the string be truncated for
> more than 9 substitutions?
> And back to the very first question, could this - sending the table
> name - cause a concurrency issue?
> Rishi.
>
> John Bell wrote:
> > Hi
> >
> > As your strings with the placeholders are not SQL Statements then don't
> > think I can suggest a different alternative.
> >
> > I am not sure why you need to dynamic SQL for returning values (or to pass
> > the t able name) from your temporary table, unless the substitutions will be
> > embeded it should not be necessary! You may want to try ordering the
> > processing by the length of the value to try and avoid replacing already
> > substituted values. I assume this is SQL 2005 to use INSERT EXEC for the
> > table variable? For you string size you will may have truncation if you have
> > more than 9 substitutions.
> >
> > If this is just going to be sent to the client you may just want to do the
> > replacement on the client.
> >
> > John
> >
> > "Rishi" wrote:
> >
> > > Sure John! here it is:
> > >
> > >
> > > --- in main stored proc
> > > ----
> > >
> > > --Some code goes here
> > >
> > > SET @.string = ' <attribute1> some text here <attribute2> some text here
> > > <property1>'
> > >
> > > CREATE TABLE #prop
> > > (
> > > name VARCHAR(100),
> > > value VARCHAR(100)
> > > )
> > >
> > > -- Get Standard Attributes
> > > INSERT INTO #prop
> > > --some dynamic name value pairs returned from
> > > some other calculation / query
> > >
> > > -- Get user defined properties
> > > INSERT INTO #prop
> > > --some dynamic name value pairs returned from
> > > some other calculation / query
> > > --
> > > --
> > > --
> > >
> > > EXEC usp_replace '#prop',@.string output
> > >
> > >
> > >
> > >
> > > ------
> > >
> > > ---
> > > usp_replace----
> > >
> > > ALTER PROCEDURE [dbo].[usp_replace]
> > >
> > > @.tablename VARCHAR(100) ,
> > > @.string VARCHAR(1000) OUTPUT
> > > AS
> > > BEGIN
> > > --IF(nullif(@.string,'') is null) RETURN
> > >
> > > DECLARE @.tableqry VARCHAR(100)
> > > DECLARE @.temp TABLE(name VARCHAR(100),value VARCHAR(1000))
> > >
> > > SET @.tableqry = 'SELECT * FROM '+@.tablename
> > >
> > > INSERT INTO @.temp
> > > EXEC (@.tableqry)
> > >
> > > DECLARE c CURSOR FOR
> > > SELECT * FROM @.temp
> > >
> > > DECLARE @.name VARCHAR(100),@.value VARCHAR(1000)
> > > OPEN c
> > > FETCH next FROM c INTO @.name,@.value
> > > WHILE @.@.fetch_status =0 BEGIN
> > > SET @.string = replace(@.string,@.name,coalesce(@.value,'NULL'))
> > >
> > > FETCH next FROM c INTO @.name,@.value
> > >
> > > END
> > > CLOSE c
> > > DEALLOCATE c
> > >
> > >
> > > END
> > >
> > > ---
> > > usp_replace----
> > >
> > >
> > > the table #prop looks like this after values are inserted in it in the
> > > main sp.
> > >
> > > name | value
> > > ---
> > > <attribute1> | attributvalue
> > > <attribute2 > | attribute2value
> > > ----
> > >
> > >
> > > finallly the string looks like this after returned from sp_replace
> > >
> > > ' attributvalue some text here attribute2value some text here
> > > <property1>'
> > > ----
> > >
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > Can you give an example SQL Statement and the values in the table that will
> > > > be used and the code to usp_replace?
> > > >
> > > > John
> > > >
> > > > "Rishi" wrote:
> > > >
> > > > > Hi,
> > > > > the dynamic sql thing looks great, but i dont know how it will
> > > > > rescue me as a n aternative in this case. could you please throw some
> > > > > more light on this.
> > > > > probably my last post wasnt very clear, so the deal is:
> > > > >
> > > > > i have a table which has name value pairs.
> > > > > i have a string which has names as place holders for the values
> > > > > in the string i need to replace those names with the values as found in
> > > > > the table.
> > > > > i want a general reusable stored proc to acheive this.
> > > > > the way i am doing it as desribed in the first post works, but i want
> > > > > to know if that will pose any concurrency problems.
> > > > > since i want the sp to general and reusable i dont want to hard code
> > > > > and use the table name in the usp_replace sp.
> > > > >
> > > > > Please let me know your thoughts.
> > > > > Thanks,
> > > > > Rishi.
> > > > >
> > > > >
> > > > >
> > > > > John Bell wrote:
> > > > > > Hi
> > > > > >
> > > > > > If these are just values then you should be able to join to this table and
> > > > > > not need to resort to dynamic SQL. If you wish to change the SQL Statement
> > > > > > e.g. column names then
> > > > > > see
> > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
> > > > > > but still pass variables for the values. You may want to look at
> > > > > > http://www.sommarskog.se/dynamic_sql.html and
> > > > > > http://www.sommarskog.se/dyn-search.html
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "Rishi" wrote:
> > > > > >
> > > > > > > Thanks John!
> > > > > > > to be more precise on what im tryin to achieve is :
> > > > > > >
> > > > > > > To create a generic stored procedure that replace the occurence of
> > > > > > > certain "place holders" in a string.
> > > > > > > The placeholders' values are stored in a table as a name value pair.
> > > > > > > This table can be either generates on the fly as a temp table or be a
> > > > > > > permanent table.
> > > > > > >
> > > > > > > e.g. the string is '<name> who is <age> years old lives in <city>'
> > > > > > >
> > > > > > > the temp table will be
> > > > > > > name | value
> > > > > > > --
> > > > > > > <name> | jack
> > > > > > > <age> | 23
> > > > > > > <city> | NY
> > > > > > >
> > > > > > > appreciate your help, thanks!
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > John Bell wrote:
> > > > > > > > Hi
> > > > > > > >
> > > > > > > > If you have created the temporary table at the outer level it will be
> > > > > > > > available to the inner level procedure, therefore it should not be necessary
> > > > > > > > to pass the table name. For more on temporary tables read Books Online or at
> > > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
> > > > > > > >
> > > > > > > > If many processes are creating/dropping temporary tables you can get
> > > > > > > > contention on tempdb, this can be limited to some degree by making sure that
> > > > > > > > tempdb is on it's own discs. Without knowing more about what you are trying
> > > > > > > > to achieve it is not possible to suggest an alternative approach.
> > > > > > > >
> > > > > > > > John
> > > > > > > >
> > > > > > > > "Rishi" wrote:
> > > > > > > >
> > > > > > > > > Hi,
> > > > > > > > > I want to know if the below mentioned pseudo code can pose a
> > > > > > > > > concurrency problem.
> > > > > > > > >
> > > > > > > > > declare @.string
> > > > > > > > >
> > > > > > > > > CREATE TABLE #temp
> > > > > > > > > (
> > > > > > > > > name VARCHAR(100),
> > > > > > > > > value VARCHAR(100)
> > > > > > > > > )
> > > > > > > > > --INSERT some value
> > > > > > > > >
> > > > > > > > > --Call another stored procedure that takes the table name and a string
> > > > > > > > >
> > > > > > > > > EXEC usp_replace '#temp',@.string OUTPUT
> > > > > > > > >
> > > > > > > > > // the usp_replace sp replaces occurence of 'name' with 'values' as per
> > > > > > > > > entries in #temp
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Now the question is that since im passing the table name and using it
> > > > > > > > > to query in the usp_replace could this mess up the values if multiple
> > > > > > > > > concurrent users execute it?
> > > > > > > > >
> > > > > > > > > Would really appreciate any thoughts on this. Thanks.
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>

Posting Multi-Value Parameter

Hi,

I tried Posting the values for a multi value parameter from my application to the reporting services, But the query string is not being hidden. Why is that ? since the the data is sent through the browser address bar, I am not able to send values more than the allowed lenght. Is there any work around ?

Thanks In Advance

Regards

Raja Annamalai S

Hi Raja-

Yes, you will be limited to the URL length restriction. If possible I might suggest using the Web Service to render reports rather than crafting the URL. This will not be limited by URL length.

Otherwise you can perform a POST instead of a GET on the http call. The post will send the parameter values in the body rather than appended to the URL. However, all parameters need to be in the body if you perform a POST.

Thanks, Jon

Postback takes a LONG time!

I have one other issue, possibly related to my last post, that I thought I
should post in a separate question.
When I specify a default value for a parameter using an expression, the form
postback process seems to get scrambled. If I enter a value in a text
parameter, then click "View Report", the form will do a postback, but NOT run
the query. If I click View Report again, the report runs fine.
This delayed postback occurs on any text field parameter, and can be
observed by entering text in a parameter field, then clicking in another
parameter field. This effect is very disconcerting.
As in my other question, I'm thinking this is a RS bug, but is there a
workaround?
Thanks in advance,
MikeThe server can not determine the dependencies between parameters when some
of them are expressions, so it is conservative and assumes that a dependency
exists. When you navigate away from the text box (to another text box or to
the view report button), the page posts back to get new defaults and valid
values for downstream parameters. If there is no dependency, you can
reorder the parameters to prevent the post back. A parmeter can only depend
on parameters that came before it. So if you make your expression based
parameter the last one, there will be no auto post back.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"rdcpro" <rdcpro@.discussions.microsoft.com> wrote in message
news:691D2719-81E7-46B8-9F26-E3515DD8CDB2@.microsoft.com...
> I have one other issue, possibly related to my last post, that I thought I
> should post in a separate question.
> When I specify a default value for a parameter using an expression, the
form
> postback process seems to get scrambled. If I enter a value in a text
> parameter, then click "View Report", the form will do a postback, but NOT
run
> the query. If I click View Report again, the report runs fine.
> This delayed postback occurs on any text field parameter, and can be
> observed by entering text in a parameter field, then clicking in another
> parameter field. This effect is very disconcerting.
> As in my other question, I'm thinking this is a RS bug, but is there a
> workaround?
> Thanks in advance,
> Mike|||Thanks Brian,
This particular control is the last parameter, but there is also a select
box that is the second-to-last parameter. This one uses a default value, but
not an expression. In particular, it has a set of values that look like
Label Value
foo 0
bar 1
snafu 2
and the default value is specified as:
0
and not
=0
In this case, a postback shouldn't occur, because this is not an expression
but a scalar value, right?
And even if it were an expression, while the server doesn't know if there is
a dependancy, the Designer (me) knows. It seems to me that when I design a
report with parameters that use expressions, I should have a checkbox to
disable postback or declaratively state there is no dependancy.
Any thoughts?
Thanks again,
Mike
"Brian Hartman [MSFT]" wrote:
> The server can not determine the dependencies between parameters when some
> of them are expressions, so it is conservative and assumes that a dependency
> exists. When you navigate away from the text box (to another text box or to
> the view report button), the page posts back to get new defaults and valid
> values for downstream parameters. If there is no dependency, you can
> reorder the parameters to prevent the post back. A parmeter can only depend
> on parameters that came before it. So if you make your expression based
> parameter the last one, there will be no auto post back.
>|||In case others run into this issue, I wanted to post my resolution.
At issue is the fact that a report parameter with an expression is assumed
to be dependant on another report parameter, even when in fact it's only
dependant on a global value or even if it simply uses an expression to return
a scalar result. So, when you set a default value using an expression, the
control is "disabled" and there is no way for the user to physically set the
control without first entering a fake value in another field, then causing a
postback to "refresh" the parameter. Only then can the user set the value
for the parameter using an expression. Of course, before the user runs the
query he/she must remove the "fake" value.
In my case, a user might enter a date in my "End Date" field, then click
"View Report". Instead of actually seeing the report, the screen blanks, a
postback occurs, and it appears to the user that either nothing happened, or
the report returned no results. They have to click "View Report" again to
get the report to run.
Evidently, the behavior is "by design" inasmuch as RS isn't smart enough to
realize there is no dependancy for this parameter, and there is no way in the
report designer to declaratively state as much.
In my case, I simply wanted to set the default value of the parameter to
"true" in one folder, and "false" in another. My solution was to use the
same approach that creating a linked report would use. Upload the report
with no default value on the control, then in the Report Manager, manually
specify the default value I want (either true or false) in the Properties >
Parameters page.
It would be really nice if there was a similar option in the Report
Designer, or if you could set the default to a scalar value directly the same
way you can for a drop-down parameter!
Hope this helps someone,
Mike
"rdcpro" wrote:
> Thanks Brian,
> This particular control is the last parameter, but there is also a select
> box that is the second-to-last parameter. This one uses a default value, but
> not an expression. In particular, it has a set of values that look like
> Label Value
> foo 0
> bar 1
> snafu 2
> and the default value is specified as:
> 0
> and not
> =0
> In this case, a postback shouldn't occur, because this is not an expression
> but a scalar value, right?
> And even if it were an expression, while the server doesn't know if there is
> a dependancy, the Designer (me) knows. It seems to me that when I design a
> report with parameters that use expressions, I should have a checkbox to
> disable postback or declaratively state there is no dependancy.
> Any thoughts?
> Thanks again,
> Mike
> "Brian Hartman [MSFT]" wrote:
> > The server can not determine the dependencies between parameters when some
> > of them are expressions, so it is conservative and assumes that a dependency
> > exists. When you navigate away from the text box (to another text box or to
> > the view report button), the page posts back to get new defaults and valid
> > values for downstream parameters. If there is no dependency, you can
> > reorder the parameters to prevent the post back. A parmeter can only depend
> > on parameters that came before it. So if you make your expression based
> > parameter the last one, there will be no auto post back.
> >
>

Possible todo some form of DISTINCT filtering?

Hi guys, I have a dataset that returns rows that look like the following
field | value
--
1 | 7
1 | 7
2 | 4
8 | 90
Is it possible to remove the duplicate (1 | 7) row directly within a LIST
control (or similar?)? I cannot use a DISTINCT directly in the SQL as
elsewhere I need to display the duplicate row.
Thanks for any help
TazSilly me, thats what grouping is for lol.
Thanks anyways
Taz

Possible to reference the value of a textbox in page header or footer?

Is it possible to reference the value of a textbox in a report page header
or footer? I've tried to create a textbox in my report page footer, and make
it show the PageNumber. My textbox is called txtPageNum.
In my report body, I've created another textbox. I've tried to set this
textbox's value to =ReportItems!txtPageNum.value, but that didn't work out.
This is the error message:
"The value expression for the textbox 'textbox2' refers to the report item
'txtPageNum'. Report item expressions can only refer to other report items
within the same grouping scope or a containing grouping scope."
So, is it even possible to reference it, or should I just give up on the
whole idea?
Kaisa M. LindahlSorry, meant that to be a post. Yes, in theory you can reference a
textbox field from a Header or Footer. However, for page numbers, I'd
reference the Report Services global variables. There are two of them
that apply here.
For example:
="Page " & Globals.PageNumber & " of " & Globals.TotalPages
This will show "Page 1 of 2", etc.
MikeN|||Funny, as each time I try to use Globals!PageNumber in a text box in the
body, I get this error message:
The value expression for the textbox 'textbox20' refers to the global
variable PageNumber or TotalPages. These global variables can be used only
in the page header and page footer.
I'm very interested in how you suggest I can use the globals in the report
body section.
I'm also rather interested in how to reference a textbox field in the header
or footer, even if it's in theory. I've tried referencing it with
ReportItems!myTextBox.value, but no good. Suggestions on syntax are very
welcome.
Kaisa M. Lindahl
"MikeN" <mnorman07@.yahoo.com> wrote in message
news:1113574896.932765.202500@.o13g2000cwo.googlegroups.com...
> Sorry, meant that to be a post. Yes, in theory you can reference a
> textbox field from a Header or Footer. However, for page numbers, I'd
> reference the Report Services global variables. There are two of them
> that apply here.
> For example:
> ="Page " & Globals.PageNumber & " of " & Globals.TotalPages
> This will show "Page 1 of 2", etc.
> MikeN
>