Showing posts with label code. Show all posts
Showing posts with label code. Show all posts

Friday, March 30, 2012

pre-execute failure

I just started getting this error.

[DTS.Pipeline] Error: component "User Type" (377) failed the pre-execute phase and returned error code 0x8007000E.

It wasn't happening before. Does anyone know what it means?

Hi Jim,
Without more information it is probably impossible to say.

What type of component is it?
What are you using it for?
How have you configured it?
When do you get the error - when the package starts or when the data-flow starts?
What inputs does it take?

etc...etc...

Regards
Jamie|||

Excelent questions.
This s a dataflow component. All of it's input is from a table. It seems like just rearranging the dataflow components in the work flow eliminates the problem. Since it isn't happening any longer, I can't do a better job answering your questions.

I don't understand it.

|||Incidentally, that error is Out Of Memory, so there could have been some transient cause.... Please let us know if you do come across a repro.sql

Wednesday, March 28, 2012

PrecompileScriptIntoBinaryCode errors

Hi,

I have several script tasks. Every now and then I get an error that the task can't precompile the script into binary code, or something to that effect. It's very intermittent, so it doesn't really seem to have any rhyme or reason.

For example, I have a package that has been running perfectly for weeks now, but just TODAY it decided to give me a precompile error. So I changed the "PrecompileScriptIntoBinaryCode" setting to "false", and that solved the problem.

I don't get why this is happening. It seems like a bug. It makes me wonder if I should set all scripts to "false."

Thanks

sadie519590 wrote:

Hi,

I have several script tasks. Every now and then I get an error that the task can't precompile the script into binary code,

Does it give any more info? Like WHY it can't compile it?

-Jamie

|||Not that I remember. I can't reproduce this error at will. I just know that it happens with the script task once in a while, for reasons that aren't clear or logical, and seemingly random. I do think this is a bug.|||

Here is the error:

Error 1 Validation error. myTask : The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated. CashRec - BMO.dtsx 0 0

|||So do that and go in and hit the space bar somewhere in the script. Then close out of it and try again.|||

Tried that. But didn't work.

Why would that fix it? Seems strange to me....

I had this error happen AGAIN. This is what caused it: I went into the script, highlighted some code, then copied it to the clipboard for use in another place. Then I exited the script using the cancel button. NOTHING CHANGED.

Next thing I know, the script task is giving me the recompile error. This does seem buggy, doesn't it?

|||

Ugh - this keeps happening! All I do is go into the script, highlight some rows, then copy the data to the clipboard. Nothing changes in the script itself.

Then, once I'm out of the script, it displays the error. Changing the precompile to "false" gets rid of the problem.

But I don't think this problem should happen to begin with... it seems really flaky.

I had a package hang a job up for over 24 hours (it just ran and ran and never completed) due to a script error of this nature. This is not good because it doesn't even send an error message.

|||Do you have SP2 installed?|||

No.

If this was my server, it would have.

|||

sadie519590 wrote:

No.

If this was my server, it would have.

Then this likely applies to you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1855810&SiteID=1

PrecompileScriptIntoBinaryCode errors

Hi,

I have several script tasks. Every now and then I get an error that the task can't precompile the script into binary code, or something to that effect. It's very intermittent, so it doesn't really seem to have any rhyme or reason.

For example, I have a package that has been running perfectly for weeks now, but just TODAY it decided to give me a precompile error. So I changed the "PrecompileScriptIntoBinaryCode" setting to "false", and that solved the problem.

I don't get why this is happening. It seems like a bug. It makes me wonder if I should set all scripts to "false."

Thanks

sadie519590 wrote:

Hi,

I have several script tasks. Every now and then I get an error that the task can't precompile the script into binary code,

Does it give any more info? Like WHY it can't compile it?

-Jamie

|||Not that I remember. I can't reproduce this error at will. I just know that it happens with the script task once in a while, for reasons that aren't clear or logical, and seemingly random. I do think this is a bug.|||

Here is the error:

Error 1 Validation error. myTask : The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated. CashRec - BMO.dtsx 0 0

|||So do that and go in and hit the space bar somewhere in the script. Then close out of it and try again.|||

Tried that. But didn't work.

Why would that fix it? Seems strange to me....

I had this error happen AGAIN. This is what caused it: I went into the script, highlighted some code, then copied it to the clipboard for use in another place. Then I exited the script using the cancel button. NOTHING CHANGED.

Next thing I know, the script task is giving me the recompile error. This does seem buggy, doesn't it?

|||

Ugh - this keeps happening! All I do is go into the script, highlight some rows, then copy the data to the clipboard. Nothing changes in the script itself.

Then, once I'm out of the script, it displays the error. Changing the precompile to "false" gets rid of the problem.

But I don't think this problem should happen to begin with... it seems really flaky.

I had a package hang a job up for over 24 hours (it just ran and ran and never completed) due to a script error of this nature. This is not good because it doesn't even send an error message.

|||Do you have SP2 installed?|||

No.

If this was my server, it would have.

|||

sadie519590 wrote:

No.

If this was my server, it would have.

Then this likely applies to you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1855810&SiteID=1

Precompile Script task programatically

I have code which generates packages programatically, and script task is a part of the control flow. I've succeded to set source code programatically, but I do not know how to put binary code, because I need to have my script task precompiled.

Just setting PreCompile = true does not solve this problem

Thanks in advance.

Borko

Unfortunately, due to interactions with VSA (the script editing environment) this is not possible in this version.

Monday, March 26, 2012

Practice Version of SQL Server 2K?

Hi everyone,
Is there a version of MS SQL Server 2000 upon which one can practice writing code? I want to do so on my personal PC at home. Thanks.
ddaveThere is a 120 day evaulation version available on MS site that you could download.|||Originally posted by sbaru
There is a 120 day evaulation version available on MS site that you could download.

I have seen that in one of the T-SQL texts but is that it? Isn't there one you can use indefinitely?

ddave|||You want the developer edition...

http://www.microsoft.com/sql/howtobuy/development.asp|||Originally posted by sbaru
There is a 120 day evaulation version available on MS site that you could download.

I have seen that in one of the T-SQL texts but is that it? Isn't there one you can use indefinitely?

ddave|||You can use MSDE aswell which is free, (developer engine) but it doesn't come with any kind of IDE so you're basically in need of something like enterprise manager or something...|||umm...what's wrong with the $49.00US Developer Edition?

It's a full blown Enterprise Edition for a local client...

Practical Uses for the "Embedded Code" functionality

Dear Anyone,

I understand that there an "Embedded Code" functionality in RS2005. Does anyone know any good uses for this?

Thanks,
Joseph

Normally, you'll use embedded code to create "one off" functions that are (generally) specific to a report -- Maybe something that does a specific calculation that you need which isn't directly supported by VB.NET expressions.

Using embedded code (or code in a custom assembly) gives you greater control over logic flow and alows you to do stuff like more "advanced" conditional statements and looping which you can't do in an expression you place directly behind a control in a report

People will use a custom assembly (which is then referenced by the report) in cases where the functions are reusable, generally more complex. and do things like access system resources (the file system, the network, etc.) .

|||

Is it possible to iterate through a data set in the report in embedded code. If so, how would you reference the data set.

|||No, you can't. It would be nice to be able to just assign a "reporting services dataset" to an "true" ADO.NET datatable and then just walk the table, but there is no such functionality.|||

Hi,

Dont you thik there should be a property for a report that controls how many "records" get displayed on a page?. Currently when I create a report, only 3 records show up and I get 2000 pages. Instead I would prefer 100 records on one page and fewer number of pages?

Or is there any way to do this?

Thanks.

Friday, March 23, 2012

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

Wednesday, March 21, 2012

Posting an Image to SQL Server 2005

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

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

Thanks in advance

Here is the code:

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

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

Dim objConnAs SqlConnection

Dim objComAs SqlCommand

IfMe.FileUpload1.HasFileThen

Dim fileExtensionAsString

Dim fileOKAsBoolean =False

fileExtension = System.IO.Path. _

GetExtension(FileUpload1.FileName).ToLower()

Dim allowedExtensionsAsString() = _

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

For iAsInteger = 0To allowedExtensions.Length - 1

If fileExtension = allowedExtensions(i)Then

fileOK =True

EndIf

Next

'Try

Dim imagestreamAs System.IO.Stream = FileUpload1.FileContent

Dim data()AsByte

ReDim data(imagestream.Length - 1)

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

imagestream.Close()

objConn =New SqlConnection(strNewConnection)

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

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

'this is the aspnet_userid

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

objCom.Parameters.Add(useridparameter)

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

'This is the image name

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

objCom.Parameters.Add(imagenameparameter)

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

'this is the picture data

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

pictureParameter.Value = data

objCom.Parameters.Add(pictureParameter)

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

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

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

pictureParameter.Value ="IntroVideo"

objCom.Parameters.Add(categorynameParameter)

objConn.Open()

objCom.ExecuteNonQuery()

objConn.Close()

'bookmark

Label1.Text ="File uploaded!"

'Catch ex As Exception

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

EndIf

Hi,

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

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

Thanks.
|||

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

Postal Codes 10001-10005 as a parameter

Dear all
I like to implement a parameter in SSRS SP2, that accepts values like
10001-10005 as postal code. This should be translated to 10001, 10002,10003,
10004, 10005.
I tried to realize this with the following code:
Function SplittingCodes(ByVal s As String) As String
Dim ar As String()
Dim subar As String()
Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder
ar = s.Split(","c)
For i As Integer = 0 To ar.Length - 1
ar(i) = ar(i).Trim()
If ar(i).Contains("-") Then
subar = ar(i).Split("-"c)
For j As Integer = CInt(subar(0)) To CInt(subar(1))
sb.Append(" '")
sb.Append(j)
sb.Append("'")
If j <> CInt(subar(1)) Then sb.Append(",")
Next
Else
sb.Append("'")
sb.Append(ar(i))
sb.Append("'")
End If
If i <> ar.Length - 1 Then sb.Append(",")
Next
Return sb.ToString()
End Function
I created an sql query that uses the parameter like this:
SELECT SUM (a) as test FROM table WHERE co IN (@.pcode)
And in the dataset tab on Parameters I used this code for the parameter
@.pcode:
=Code.SplittingCodes(Parameters!pc.Value)
Unfortunately the reports gives an empty dataset back.
Any help would be apreciated!
Thanks,
MarcHello Rombooth,
The root cause of this issue is that the sql statement you use in the
report.
My suggestion is that you could create a function in the sql server side to
split the string. And you could use this function in the sql statement.
You could refer this article to create the split function.
http://www.devx.com/tips/Tip/20009
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Wei! This solved my problem.
Best Regards,
Marc|||Hello Marc,
My pleasure!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Postal Code.

Can any one help me regarding the postal code. As we have seen in the
website we put postal code and it will display the nearest distance... can
any one help me out regarding this ... like what would be the formula to
calculate the distance...
I would highly appreciate.
Thanks
Rogers wrote:
> Can any one help me regarding the postal code. As we have seen in the
> website we put postal code and it will display the nearest
> distance... can any one help me out regarding this ... like what
> would be the formula to calculate the distance...
> I would highly appreciate.
> Thanks
I think you'll need latitude and longitude to do the calculation. See
this page for an example:
http://px.sklar.com/code.html/id=1062
You can buy the database here:
http://www.zipcodeworld.com/
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Yeah but they do not have the long lats.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHyMDbKuFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Rogers wrote:
> I think you'll need latitude and longitude to do the calculation. See this
> page for an example:
> http://px.sklar.com/code.html/id=1062
> You can buy the database here:
> http://www.zipcodeworld.com/
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

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

Tuesday, March 20, 2012

Post dtsx packages to forum

First off, can you post .dtsx packages to the forum? I always end up cutting and pasting code, which is by is nature incomplete, as it generally has external dependencies (connections, variables).

Perhaps you cannot do so for security reasons?

Second, is there a good blog site that allows you to post .dtsx packages?

I'd rather post .dtsx links than cut and paste code (which half the time formats the code un-recognizably), or even worse, post package xml, which contains the layout information and possibly IL that makes it all but unreadable.
No, you can't post attachments here. There are a number of free hosting providers out there that will host your package for you. Also look at blogger.com to see if that works for you.

If it's just a one time deal, some of us around here can host it for you, if you want.|||Thanks Phil.

Posting sample packags (at least for myself) is not a one time deal Smile,

I'll look at blogger.com. Many of the blog sites I looked at only allow you to upload image/audio files.
|||

jaegd wrote:

Thanks Phil.

Posting sample packags (at least for myself) is not a one time deal ,

I'll look at blogger.com. Many of the blog sites I looked at only allow you to upload image/audio files.

Yep, including the one I run over at ssistalk.com, except there I run my own server, so it's easy for me to get non-image/audio files posted... Wink