Predicting player win over a period of time

I would like to create a simple regression equation to predict player win on their next trip. I have tried to create the model using a linear regression tree based on two players (as a test). The result gives me a single node (expected) with only a coefficient instead of a regression equation. I can do this math by hand to get a regression equation and predicted value for the next trip for each player.

The dataset I used for a simple test is.....

Trip #PlayerWin110011,2501100250210011,4502100275310011,60031002100410012,00041002175

I also tried to predict next trip worth using a forecasting model. I was able to process the model but I was not able to browse the model content in the viewer.

Ultimately, I want to predict next trip worth for individual players off of a cube. The cube has about 1.5- 6M records (multiple records per player) depending on the datasource.

FYI - I have created a working linear regression and a forecasting model off of a cube I think I am setting it up correctly.

Can you provide how the mining structure/model column for the linear regression are set up? More specifically,

1. The datatype for each column in the structure

2. The attribute (Predict, PredictOnly, Input, Ignore) set on each of the model column

Thanks

Shuvro

|||

The variables are all set with a numeric datatype in the table

The attribute for each column:

actual continuous, predict

trip -- continuous, input

win continuous, predict

player account number -- discrete, key

Basically, I want to run predict worth at the player level (return a regression equation for each player) over a large list of players. I am not trying to get one regression equation for the whole universe of players.

In the time series model - is there a limit to the number of unique cases that can be inputed into the model?

Thanks so much for your help.

|||

You can have pretty much any number of series for a time series model, I'm not sure it will help you in this circumstance.

For example, you likely don't want to cross-predict between players. For example you may want a model such as

CREATE MINING MODEL PlayerModel
(
Player TEXT KEY,
Trip LONG KEY TIME,
Win LONG CONTINUOUS PREDICT,
Actual LONG CONTINUOUS PREDICT
) using Microsoft_Time_Series

This would create a time series model that contained models for each player for Win and Actual. However, since they are marked "Predict" rather than "Predict Only" Player A's "Win" values could influence Player B's "Actual" values if the data happened to line up that way.

You would think that you could simply make a model like this

CREATE MINING MODEL PlayerModel
(
Player TEXT KEY,
Trip LONG KEY TIME,
Win LONG CONTINUOUS PREDICT ONLY,
Actual LONG CONTINUOUS PREDICT ONLY
) using Microsoft_Time_Series

This causes the time series to only be based on their own valus, and not of others so this cross-predict thing is not an issue. However, you probably want Actual to influence Win for an individual player, just not other players. The modeling scheme for this situation is to make a seperate model for each player.

Similar things would happen if you tried other regression models such as decision trees, logistic, or linear regression. Your model structure would look like

CREATE MINING MODEL PlayerModel
(
Trip LONG KEY,
Players TABLE
(
Player TEXT KEY,
Actual LONG CONTINUOUS PREDICT ONLY,
Win LONG CONTINUOUS PREDICT ONLY
)
) using Microsoft_Linear_Regression

This model wouldn't do anything (probalby return an error) because there simply are no inputs whatsoever. Again, if you changed a column to Predict (or just left it as Input), the values for different players could influence the regressions for other players. Again, the solution is to create unique models/player.

Predicting player win over a period of time

I would like to create a simple regression equation to predict player win on their next trip. I have tried to create the model using a linear regression tree based on two players (as a test). The result gives me a single node (expected) with only a coefficient instead of a regression equation. I can do this math by hand to get a regression equation and predicted value for the next trip for each player.

The dataset I used for a simple test is.....

Trip #

Player

Win

1

1001

1,250

1

1002

50

2

1001

1,450

2

1002

75

3

1001

1,600

3

1002

100

4

1001

2,000

4

1002

175

I also tried to predict next trip worth using a forecasting model. I was able to process the model but I was not able to browse the model content in the viewer.

Ultimately, I want to predict next trip worth for individual players off of a cube. The cube has about 1.5- 6M records (multiple records per player) depending on the datasource.

FYI - I have created a working linear regression and a forecasting model off of a cube I think I am setting it up correctly.

Can you provide how the mining structure/model column for the linear regression are set up? More specifically,

1. The datatype for each column in the structure

2. The attribute (Predict, PredictOnly, Input, Ignore) set on each of the model column

Thanks

Shuvro

|||

The variables are all set with a numeric datatype in the table

The attribute for each column:

actual continuous, predict

trip -- continuous, input

win continuous, predict

player account number -- discrete, key

Basically, I want to run predict worth at the player level (return a regression equation for each player) over a large list of players. I am not trying to get one regression equation for the whole universe of players.

In the time series model - is there a limit to the number of unique cases that can be inputed into the model?

Thanks so much for your help.

|||

You can have pretty much any number of series for a time series model, I'm not sure it will help you in this circumstance.

For example, you likely don't want to cross-predict between players. For example you may want a model such as

CREATE MINING MODEL PlayerModel
(
Player TEXT KEY,
Trip LONG KEY TIME,
Win LONG CONTINUOUS PREDICT,
Actual LONG CONTINUOUS PREDICT
) using Microsoft_Time_Series

This would create a time series model that contained models for each player for Win and Actual. However, since they are marked "Predict" rather than "Predict Only" Player A's "Win" values could influence Player B's "Actual" values if the data happened to line up that way.

You would think that you could simply make a model like this

CREATE MINING MODEL PlayerModel
(
Player TEXT KEY,
Trip LONG KEY TIME,
Win LONG CONTINUOUS PREDICT ONLY,
Actual LONG CONTINUOUS PREDICT ONLY
) using Microsoft_Time_Series

This causes the time series to only be based on their own valus, and not of others so this cross-predict thing is not an issue. However, you probably want Actual to influence Win for an individual player, just not other players. The modeling scheme for this situation is to make a seperate model for each player.

Similar things would happen if you tried other regression models such as decision trees, logistic, or linear regression. Your model structure would look like

CREATE MINING MODEL PlayerModel
(
Trip LONG KEY,
Players TABLE
(
Player TEXT KEY,
Actual LONG CONTINUOUS PREDICT ONLY,
Win LONG CONTINUOUS PREDICT ONLY
)
) using Microsoft_Linear_Regression

This model wouldn't do anything (probalby return an error) because there simply are no inputs whatsoever. Again, if you changed a column to Predict (or just left it as Input), the values for different players could influence the regressions for other players. Again, the solution is to create unique models/player.

Precompiling checking problem

i have a stored procedure i want to save
the commands all execute fine if i execute them one at a time, but when i
try to create a sp out of the script, it fails
problem... my sp adds a coulmn to a table in the begining, does some
processing and uses that column, then drops it.
the process that saves the sp tries to validate each command individually
instead of as a progression so obviously a select statement with the new
column is not valid on its own and it wont process the script.
is their any directive in QA that can turn off this checking?
i have a work around, build the query dynamically, but this is a pain as far
as im concerned.
any suggestions?
not sure I understand completely, BUT I think your problem may be resolved
by placing GO statements after each step in the sproc.
try that (if you havent already) and script the thing out and paste it in
here if that still does not work..
that we we can all have a look at it and get through the guesswork.
Greg Jackson
PDX, Oregon
|||example:
table1 has column1, column2, column3
i create a script to make a stored procedure in query analyzer:
create proc test1 as
alter table to add column4
update table1 to set column4 = to something
update table1 to set other columns to something
alter table to drop column4
go
1) execute each internal line seperately, each runs without error
2) execute script to create the proc, ERROR cause it checks each command
before creating the proc and since the second line sets column4 to
something, and column4 is not there right now, its an invalid command and
the proc isnt created
3) cant put go's between the commands in a stored procedure and even if you
could, the system still wont save the procedure cause line2 is still invalid
at this moment in time.
|||Claude Hebert wrote:
> example:
> table1 has column1, column2, column3
> i create a script to make a stored procedure in query analyzer:
> create proc test1 as
> alter table to add column4
> update table1 to set column4 = to something
> update table1 to set other columns to something
> alter table to drop column4
>
Without trying to understand what you are doing, the problem is that
each of those statements must be in its own batch. You have to use
dynamic sql to do what you want:
create table dbo.test1 (col1 int)
go
insert into dbo.test1 values (1)
create proc dbo.test2 as
begin
EXEC ('alter table dbo.test1 add column4 int')
select * from dbo.test1
EXEC ('update dbo.test1 set column4 = 5')
select * from dbo.test1
EXEC ('alter table dbo.test1 drop column column4')
select * from dbo.test1
end
go
exec test2
drop proc dbo.test2
create table dbo.test1
David Gugick
Imceda Software
www.imceda.com
|||i know that works, and this is a very simple case...
because of the size of our project, the number of procedures, the number of
lines in those procedures that would have to be moved into EXEC statements,
i was trying to find another way...
i was just hopping there was a set something that i could do in QA to allow
the code to run without all the EXEC's
im guessing at this point, the answer is NO
|||Hi Greg
GO is a batch separator for the client, it is not a SQL statement. GO tells
the CLIENT to send a set of commands to SQL Server separately (that is what
a batch is).
GO is not possible inside a stored procedure, which always executes within a
single batch. In fact, if you are trying to create a stored proc in the
Query Analyzer, as soon as a GO is entered, that is the end of the
procedure.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eG51le%23VFHA.2616@.TK2MSFTNGP14.phx.gbl...
> not sure I understand completely, BUT I think your problem may be resolved
> by placing GO statements after each step in the sproc.
> try that (if you havent already) and script the thing out and paste it in
> here if that still does not work..
> that we we can all have a look at it and get through the guesswork.
>
> Greg Jackson
> PDX, Oregon
>
|||yep my bad.
I didnt read carefully enough. I though he was talking about a SQL Script.
GAJ

Precision Problem

Hi,

I can't seem to get a stored procedure to return decimal places. Here are the steps to re-create the problem:

--first create the following procedure
CREATE PROCEDURE test_precision
AS
BEGIN
RETURN 5.2
END

--then run the following code
DECLARE @.x DECIMAL(18,2)
EXECUTE @.x = test_precision
SELECT @.x

I would like this code to return 5.20, but instead it returns 5.00. Any assistance would be greatly appreciated. Thanks.if you are using SQL 2005 that is the problem. Your stored Procedure would work correctly in SQL 2000.

The work around
declare @.x FLOAT(18,2)

give that a try.|||ooops!! allow me to take that back - wrong thought process. sorry.

you need to edit your proc to read 5.20sql

Precedence of MAX and WHERE

Hi
I'd like to create a query which returns the MAX of a group of dates so long
as the number is less than a given date. For example :
SELECT MAX(date), username
FROM mydatatable
WHERE date < '01/01/2005'
GROUP BY username
Will this do what I expect and return the username and date which is the
most recent before 01/01/2005 ?
Thanks
AndrewHi,
Your query looks good.
Thanks
Hari
SQL Server MVP
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:OTlNYqfuFHA.1572@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'd like to create a query which returns the MAX of a group of dates so
> long as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>|||You could compare these queries and see which one yields the results you
want. Word problems are tough to solve, usually better to provide specs as
described in http://www.aspfaq.com/5006 . Also, "date" is a really bad name
for a column. Not only is it a reserved word, it is also very tough to
decipher it... date of WHAT? Finally, do not use m/d/y or d/m/y date
formats when hard-coding date strings. The safest approach here is to use
YYYYMMDD format, then this can't be by software or humans.
CREATE TABLE dbo.myDataTable
(
username VARCHAR(32),
eventDate SMALLDATETIME
)
GO
SET NOCOUNT ON
INSERT myDataTable SELECT 'bob','20040101'
INSERT myDataTable SELECT 'bob','20050201'
INSERT myDataTable SELECT 'frank','20040101'
INSERT myDataTable SELECT 'frank','20040725'
GO
SELECT username, MAX(eventDate)
FROM dbo.myDataTable
WHERE eventDate < '20050101'
GROUP BY username
SELECT username, MAX(eventDate)
FROM dbo.myDataTable
GROUP BY username
HAVING MAX(eventDate) < '20050101'
GO
DROP TABLE dbo.myDataTable
GO
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:OTlNYqfuFHA.1572@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'd like to create a query which returns the MAX of a group of dates so
> long as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>|||Andrew,

> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
It is correct, but it could be more than one. It will select each username
and the max date for those username with date values less than '20050101'. I
f
a username does not have date values in this range then it will not appear i
n
the result.
AMB
"Andrew Webb" wrote:

> Hi
> I'd like to create a query which returns the MAX of a group of dates so lo
ng
> as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>
>

PRB: "use database" not working after "create database"

PRB: "use database" not working after "create database"
Please help,
I have the following query:
set XACT_ABORT on
begin transaction
create database MY_DB
use MY_DB
commit transaction
The "use" statement fails saying the database does not exists, but I get no
error on the "create". And when I go to the server, sure enough the DB is no
t
there, which it should not be if the TX rolled back. So what is wrong? If th
e
"create" is bad, why do I not see an error on it?You will not see it until you commit the transaction.
set XACT_ABORT on
begin transaction
create database MY_DB
commit transaction
use MY_DB
AMB
"ATS" wrote:

> PRB: "use database" not working after "create database"
> Please help,
> I have the following query:
> set XACT_ABORT on
> begin transaction
> create database MY_DB
> use MY_DB
> commit transaction
> The "use" statement fails saying the database does not exists, but I get n
o
> error on the "create". And when I go to the server, sure enough the DB is
not
> there, which it should not be if the TX rolled back. So what is wrong? If
the
> "create" is bad, why do I not see an error on it?|||Thanks for the reply, but that doesn't work in Query Analyzer.|||Try,
use master
go
create database MY_DB
go
select
*
from
sysdatabases
where
[name] = 'MY_DB'
go
drop database MY_DB
go
AMB
"ATS" wrote:

> Thanks for the reply, but that doesn't work in Query Analyzer.|||One other thing I've noticed. Even with or without TX handling it fails in
Query Analyzer.sql

Power user question

I want to create a user on SQL Server 2005 that needs to be able to
create databases, logins, and users, as well as create stored procs,
triggers, tables, etc. This user will be accessed from a vb.net
application. Is there anything less powerful than the 'sa' user that
will do the trick, or some kind of roll-my-own permissions? I really
haven't played around too much with assigning SQL Server permissions
before.
Cheers,
MarcusYou can grant the server role Database Creators to the user, and then grant
the fixed database role db_ddladmin for each existing database.
"Marcus" wrote:

> I want to create a user on SQL Server 2005 that needs to be able to
> create databases, logins, and users, as well as create stored procs,
> triggers, tables, etc. This user will be accessed from a vb.net
> application. Is there anything less powerful than the 'sa' user that
> will do the trick, or some kind of roll-my-own permissions? I really
> haven't played around too much with assigning SQL Server permissions
> before.
> Cheers,
> Marcus
>

Power user question

I want to create a user on SQL Server 2005 that needs to be able to
create databases, logins, and users, as well as create stored procs,
triggers, tables, etc. This user will be accessed from a vb.net
application. Is there anything less powerful than the 'sa' user that
will do the trick, or some kind of roll-my-own permissions? I really
haven't played around too much with assigning SQL Server permissions
before.
Cheers,
MarcusYou can grant the server role Database Creators to the user, and then grant
the fixed database role db_ddladmin for each existing database.
"Marcus" wrote:
> I want to create a user on SQL Server 2005 that needs to be able to
> create databases, logins, and users, as well as create stored procs,
> triggers, tables, etc. This user will be accessed from a vb.net
> application. Is there anything less powerful than the 'sa' user that
> will do the trick, or some kind of roll-my-own permissions? I really
> haven't played around too much with assigning SQL Server permissions
> before.
> Cheers,
> Marcus
>

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.
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>

Postal code search and LIKE statement

I'm trying to create a form that allows someone to find anaddress from a postal code search in the database. My query works exactly as I’dlike in query builder using the following select:

Select [FIELD_LIST] from addresses WHETE POSTCODE LIKE ‘%’+@.POSTCODE+’%’

I then pass the user entered post code to the select statementwhich is executed. However, I’m getting some odd behaviour. Assuming there isan address in the database with the post code “LS11 0ES”...

If I search for LS11, nothing is returned;

If I search for LS11%, nothing is returned;

If I search for %LS11%, nothing is returned;

If I search for %LS11%%, the data is returned.

If I search for %LS%1%%, the data is returned (as is LS210ES etc. etc.).

However, I want the user to be able to enter shorter searchstrings and it to pull all the data back out, so they can enter a substringsuch as LS and it will pull out all the data without the users needing to enterthe full pattern of % symbols.

If I go into query builder (in visual web developer) andenter just “LS” it works as I’d want, but not when pulled from a web page.

Any ideas?

Thanks

Look for other causes. I'm guessing somewhere in your code, you are removing the last % in postcode either before assigning it to the parameter, or modifying it during the selecting event.|||Thanks.At the moment I have a details view bound to an objectsource which in turn is bound to the above query and the parameter taken in from the text box’s .text property which the user types in.How can I trace where its failing?Thanks|||

A) response.write all your variables

or

B) Use a debugger like visual studio

sql

Possibly similar issue with launching Report Builder from client PC's

Hi,

I got a working installation of reporting services 2005 up and running, Im able to create models and launch the Report Builder on the machine on which the server is installed, however if I try and run it from another machine on the network I get an error and Im able to view the following exception.

Following errors were detected during this operation.
* [04 Jul 2005 10:39:34 +01:00] System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Failed while downloading http://testserver/ReportServer/ReportBuilder/ReportBuilder.application
- Source: System.Deployment
- Stack trace:
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState)
at System.Deployment.Application.DownloadManager.DownloadManifest(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ManifestType manifestType, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirect(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifest(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, IDownloadNotification notification, DownloadOptions options)
at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut)
at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
Inner Exception
System.Net.WebException
- The remote server returned an error: (401) Unauthorized.
- Source: System
- Stack trace:
at System.Net.HttpWebRequest.GetResponse()
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)

Anyone able to help ?The error means that the client does not have the Whidbey CLR installed. You will need to install the Whidbey CLR on each client machine where you want to use report builder.

-Lukasz|||Hi,
I was facing similar problem while accessing Report Manager installed on different system on the network, I was able to get Report Manager menu but can not see contents on Report Server. It was displayed Blank without any exception.

Report Server is installed on http://testsrv/Reports$sql2005.

I changed configuration of Reporting Service on Server , Report Server Configuration Manager > Windows Service Identity> Service account from "Local System" to "Local Services".

I can access Report Manager with contents.

Meanwhile I noticed that Report Builder is not launching on the system which is a different system from the Report Server. As per my understanding, Report Builder is a "End user tool" and will be launched without any upgradation of the system (Different system from server). I am getting prompt for "Open/Save document".
Where as Report Builder is working fine on Server itself.

While opening document, I can see XML contents to access Report Builder.
I am pasting part of that XML file in orange colour,

<?xml version="1.0" encoding="utf-8" ?>

- <asmv1:assembly xsi:schemaLocation="urn:schemas-microsoft-com:asm.v1 assembly.adaptive.xsd" manifestVersion="1.0" xmlns:dsig="http://www.w3.org/2000/09/xmldsig#" xmlns="urn:schemas-microsoft-com:asm.v2" xmlns:asmv1="urn:schemas-microsoft-com:asm.v1" xmlns:asmv2="urn:schemas-microsoft-com:asm.v2" xmlns:xrml="urn:mpeg:mpeg21:2003:01-REL-R-NS" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<assemblyIdentity name="ReportBuilder.app" version="9.0.1116.8" publicKeyToken="49ef3e7f44a9c98c" language="neutral" processorArchitecture="msil" xmlns="urn:schemas-microsoft-com:asm.v1" />

<description asmv2:publisher="Microsoft" asmv2:product="Report Builder" xmlns="urn:schemas-microsoft-com:asm.v1" />

<deployment install="false" trustURLParameters="true" />

- <dependency>

- <dependentAssembly dependencyType="install" allowDelayedBinding="true" codebase="ReportBuilder.exe.manifest" size="7148">

<assemblyIdentity name="ReportBuilder.exe" version="9.0.1116.8" publicKeyToken="49ef3e7f44a9c98c" language="neutral" processorArchitecture="msil" type="win32" />

- <hash>

- <dsig:Transforms>

<dsig:Transform Algorithm="urn:schemas-microsoft-com:HashTransforms.Identity" />

</dsig:Transforms>

<dsig:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1" />

<dsig:DigestValue>aAi7VtRySyyvYXiAs4FuFXSiqLA=</dsig:DigestValue>

</hash>

</dependentAssembly>

Anyone knows how to fix this, It is required to upgrade my system, what are the upgrades require on the system to access Report Builder?

|||Your two issues are unrelated. Specifically for the Report Builder issue - the client machine (the one on which you want to run Report Builder) needs to have The .Net Framework 2.0 (CLR 2.0) installed on it.

The reason is that the Report Builder leverages the ClickOnce technology that is new in the .Net Framework 2.0.

Regarding your other issue - the Report Manager shows the top menu with a blank contents when your login does not have permissions on the root of the report server. You will need a role that has Read Properties (Browser roles has this) assigned to your login on the root of the report server namespace. Changing the account from Local System to Local Service should not affect this.

-Lukasz|||Thanks Lukasz, I have installed .NET Framework 2.0 on Client machine and able to launch Report Builder successfully.

I created a new user with sufficient permission and access to HOME directory on Report Server and able to access Reports/Data Sources/Models using Client Machine.

Thanks again!|||

I have what may be a similar problem. I try to click on the "Report Builder" button from a client machine and nothing happens, however I have tried things mentioned in the suggestions above and it did not seem to help. From a client PC, I logged in as myself and am not able to run "Report Builder", but when I login as a local admin I am able to run "Report Builder".

Our server has SQL Server 2005 Enterprise Edition and Visual Studio 2005 installed. I am able to run "Report Builder" from my PC where I had Visual Studio 2005 installed, but not SQL Server 2005 installed. I have local admin priviledges to my PC.

From a client PC, I logged in as myself and am not able to run "Report Builder". When I click on it nothing happens. I gave my Windows login "Content Manger" permissions under the SSRS Home -> Properties tab, and I also added my login and gave myself "System Administrator" and "System User" Roles under the SSRS ->Site Settings->Configure site-wide security option. This seems to be configured correctly, as "Report Builder" launches when I am on my PC. However, it does not launch on the client PC when logged in under my login.

Further, when I login as a local admin on the client PC, I am able to run "Report Builder", so I think the .net framework 2.0 is correctly installed and working properly. However, it is our policy not to give the user of the client PC local admin rights. My windows login does not have local admin rights to this particular PC.

Q1. Is local admin rights required on the client PC where "Report Builder" is ran? If so, is it only required for the first time it is loaded? It seems like it should not be required, however this would explain the symptoms detailed above.

Q2. Is there an error log that may help debug what this problem is? It is strange to me, that nothing happens, but no error is displayed on the http://.../reportserver/Pages page

Q3. When logged in as local admin, report builder installed to something like:

C:\Documents and Settings\Administrator.8ZR5551\Local Settings\Apps\2.0\VVJ0004J.B0N\H3KE16BP.Y07\repo..lder_89845dcd8080cc91_0009.0000_none_7ecce75e919dcccd

I am not sure if I can copy this install to another directory which the client has permission to run from. Is this a valid thing to do? I am guessing probably not since I tried this and it did not seem to work.I tried this and it did not seem to work..

Q4. I saw this posting talking about the need to run under PartialTrust mode, instead of FullTrust mode. So I reconfigured the RSWebApplication.config file to do that, and it still does not work.:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=280387&SiteID=1 which refers to the following link.

http://msdn2.microsoft.com/en-us/library/ms345245.aspx

I changed the C:\Program Files\MSSQL2005\MSSQL.3\Reporting Services\ReportManager\ RSWebApplication.config

From

<Configuration><UI> …

<ReportBuilderTrustLevel> FullTrust </ReportBuilderTrustLevel>

</UI>…</Configuration>

To

<Configuration><UI> …

<ReportBuilderTrustLevel>PartialTrust</ReportBuilderTrustLevel>

</UI>…</Configuration>

Stopped and restarted report server through reporting services configuration (not sure if I needed to do this) The problem still occurred, however.

|||

Well scratch that idea on local admin rights. Our IT department gave login admin to my windows login (My computer->Manage->System Tools: Local Users and Groups->Administrators) and it still did not work. When I click on "Report Builder" it still does not do anything. I also tried to use it directly at the following URL's and it still did not work, same result of not displaying anything:

http://.../reportserver/ReportBuilder/ReportBuilder.application

http://.../reportserver/ReportBuilder/ReportBuilderLocalIntranet.application

Q5. Is there any Internet Explorer settings that would cause this behavior? I tried disabling popup blocker, but this did not seem to help.

Q6. In the above posting, giving permission on the server HOME directory was required. What directory does this refer to? Does this mean each user of Report Builder needs Windows Directory level security to some directories like C:\Program Files\MSSQL2005\MSSQL.3\Reporting Services\ReportManager\ or others? This does not seem to make sense, but I was unsure which HOME directory was mentioned in the previous posting by Nilesh Trivedi.
.

|||Well this is almost a year old but I am facing the exact same problem as Chad Buher. Clicking on the Report Builder button does nothing, no error, nothing. Can this be caused by some network policy setting?