conditionally executing sql statement

I need to write .sql file in a dynamic way.
inside the file i need to decide at run time (of the sql script) whether or
not to run a SQL statement that I don't know it's content while dynamically
creating the sql script, this sql statement might include a GO statement
what makes a problem putting it inside an if begin end block.
for example
in my script a runtime check
select @.runStatement = configVal from database at script runtime execution
if @.runStatement =1
begin
-- here comes an unknown sql statement at the time of creating the sql
script that might include a GO command which will break the syntax of the
entire block
-- go <- this here makes a TSQL error for the end command since it
breaks the begin / end block.
end
can someone recomend of an approach for how to solve this?
execute sql is not an option here since the internal SQL statement might be
larget then 4000 nvarchar characters and I can not declarae a @.ntext local
variable
TIA.>> inside the file i need to decide at run time (of the sql script) whether
Under normal circumstances, this is a poor way to write SQL code. The kludgy
workaround is to assign the SQL statement to a variable, replace the tokens
that are not needed and use EXEC or sp_ExecuteSQL to execute it.
The right way can be suggested only if you can explain the overall
situation. Why do you have to resort to such complex approach? Is there a
3rd party tool involved?
Anith|||martin (news.microsoft.com) writes:
> I need to write .sql file in a dynamic way.
> inside the file i need to decide at run time (of the sql script) whether
> or not to run a SQL statement that I don't know it's content while
> dynamically creating the sql script, this sql statement might include a
> GO statement what makes a problem putting it inside an if begin end
> block.
> for example
> in my script a runtime check
> select @.runStatement = configVal from database at script runtime execution
> if @.runStatement =1
> begin
> -- here comes an unknown sql statement at the time of creating the
> sql script that might include a GO command which will break the syntax
> of the entire block
> -- go <- this here makes a TSQL error for the end command since it
> breaks the begin / end block.
> end
>
> can someone recomend of an approach for how to solve this?
> execute sql is not an option here since the internal SQL statement might
> be larget then 4000 nvarchar characters and I can not declarae a @.ntext
> local variable
Are you on SQL 2000 or SQL 2005?
If you are on SQL 2000, I would srtongly recommend that you run the
control loop from a client. It could be very difficult to sort out
from SQL only. It could be a little easier on SQL 2005, since there
you can work with nvarchar(MAX) and you could do the batch splitting
in CLR code.
I echoes Aniths suggestion that you could be better served by telling
us the full story. This could give you better suggestions.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||it should support database modifications / upgrades.
the commands are not known at the time of designing the tool that will
execute the statements.
i understand that running each script from a client tool like a VB.NET app
is a good option but is it not possible to run it from sql script file using
some goto label....?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eedlU0VaGHA.1020@.TK2MSFTNGP02.phx.gbl...
> Under normal circumstances, this is a poor way to write SQL code. The
> kludgy workaround is to assign the SQL statement to a variable, replace
> the tokens that are not needed and use EXEC or sp_ExecuteSQL to execute
> it.
>
> The right way can be suggested only if you can explain the overall
> situation. Why do you have to resort to such complex approach? Is there a
> 3rd party tool involved?
> --
> Anith
>|||martin (news.microsoft.com) writes:
> it should support database modifications / upgrades.
> the commands are not known at the time of designing the tool that will
> execute the statements.
> i understand that running each script from a client tool like a VB.NET
> app is a good option but is it not possible to run it from sql script
> file using some goto label....?
Possible and possible. With severe kludges maybe. And it depends on the SQL
Server version.
If the purpose of the tool is run scripts for database changes, I strongly
recommend using a control part in a client language.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql

Conditional timeout issue when executing SP

In the past week, I've been running into a very unusual timeout issue
with SQL 2005 SP2 and the execution of a single, specific stored
procedure. The stored procedure executes in under 1 second and uses
all appropriate indexes when it's run from the SQL Enterprise Manager,
but when the query is executed as part of the loading process of the
website it's used in, the data doesn't load and a SQL timeout error is
encountered. In both circumstances, the query originates from the same
machine.
In the website, the procedure that is timing out isn't the first query
in the loading process to access that database or set of tables. It
seems that the problem is just something with that query itself. There
really isn't anything too out of the ordinary about the query either;
it's just a single SELECT statement with a few joins, two subqueries
(one of which contains a subquery of its own) in the SELECT and one
subquery in the WHERE, and
I tried restarting IIS on the machine just for kicks, but it doesn't
solve the problem. I also tried copying the website application to
another machine, but I still receive the same timeout error on the
same query even when the query continues to execute just fine from the
Enterprise Manager environment on the first machine. To make matters
even more puzzling, the query / stored procedure _usually_ loads
without fuss when I pass in a different value for the single parameter
it takes; sometimes though, the procedure never loads within the
website application even when I try all of the different reasonable
values for that parameter.
The only way to fix the problem that I've found is to either restart
the SQL service or change the number of threads that the service is
using (which seems to issue a "soft restart" of sorts to SQL itself).
Once restarted, the website application loads fine and runs fine for a
few days. However, once a few days have passed, the SQL server again
starts giving timeouts for that query. The timeout errors that _do_
occur at this point are usually given when the single parameter is
different that the last parameter that gave the timeout errors before
the last time the server was restarted. In all reality, everything
just seems so random, and so it's hard to pin down any more details
for sure.
Like I said, this issue has only popped up in the past week or so and
had been running fine for the 6+ months previous. Has anyone else
encountered this issue before or otherwise have any suggestions for
how I can fix the problem? I'd really appreciate anything at this
point because I'm running out of ideas.My guess is that you have parameter sniffing issues. I suggest you spend an hour or two with below.
It is worth your time.
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"amattie" <amattie@.gmail.com> wrote in message
news:57616074-99f3-4c49-9b49-babbddfe11d9@.e23g2000prf.googlegroups.com...
> In the past week, I've been running into a very unusual timeout issue
> with SQL 2005 SP2 and the execution of a single, specific stored
> procedure. The stored procedure executes in under 1 second and uses
> all appropriate indexes when it's run from the SQL Enterprise Manager,
> but when the query is executed as part of the loading process of the
> website it's used in, the data doesn't load and a SQL timeout error is
> encountered. In both circumstances, the query originates from the same
> machine.
> In the website, the procedure that is timing out isn't the first query
> in the loading process to access that database or set of tables. It
> seems that the problem is just something with that query itself. There
> really isn't anything too out of the ordinary about the query either;
> it's just a single SELECT statement with a few joins, two subqueries
> (one of which contains a subquery of its own) in the SELECT and one
> subquery in the WHERE, and
> I tried restarting IIS on the machine just for kicks, but it doesn't
> solve the problem. I also tried copying the website application to
> another machine, but I still receive the same timeout error on the
> same query even when the query continues to execute just fine from the
> Enterprise Manager environment on the first machine. To make matters
> even more puzzling, the query / stored procedure _usually_ loads
> without fuss when I pass in a different value for the single parameter
> it takes; sometimes though, the procedure never loads within the
> website application even when I try all of the different reasonable
> values for that parameter.
> The only way to fix the problem that I've found is to either restart
> the SQL service or change the number of threads that the service is
> using (which seems to issue a "soft restart" of sorts to SQL itself).
> Once restarted, the website application loads fine and runs fine for a
> few days. However, once a few days have passed, the SQL server again
> starts giving timeouts for that query. The timeout errors that _do_
> occur at this point are usually given when the single parameter is
> different that the last parameter that gave the timeout errors before
> the last time the server was restarted. In all reality, everything
> just seems so random, and so it's hard to pin down any more details
> for sure.
> Like I said, this issue has only popped up in the past week or so and
> had been running fine for the 6+ months previous. Has anyone else
> encountered this issue before or otherwise have any suggestions for
> how I can fix the problem? I'd really appreciate anything at this
> point because I'm running out of ideas.

Concurrent executions of "fn_trace_gettable" function are failing.

Hi,
While executing
select * from fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL.10\MSSQL\DATA\log.trc', default);
query simulateneously from multiple clients, only one
of them succeeds and all others give the following error message:
File 'C:\Program Files\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\log.trc'
either does not exist or is not a recognizable trace file. Or there was an
error opening the file.
Seems like the "fn_trace_gettable" function is locking the trace log file
internally during it's execution. I do not have any alternative other than
executing the query simulateously from multiple clients. Can anyone suggest
a workaround for this problem?
Thanks,
NileshI haven't tested this myself, but definitely seems like a locking issue on
the file, and can see why that would be the case.
Can you load the file into one table first, and then use DTS to parallelly
copy that table to multiple locations?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Nilesh" <nilesh.oswal@.gmail.com> wrote in message
news:%23rXnKvzjEHA.3664@.TK2MSFTNGP12.phx.gbl...
Hi,
While executing
select * from fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL.10\MSSQL\DATA\log.trc', default);
query simulateneously from multiple clients, only one
of them succeeds and all others give the following error message:
File 'C:\Program Files\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\log.trc'
either does not exist or is not a recognizable trace file. Or there was an
error opening the file.
Seems like the "fn_trace_gettable" function is locking the trace log file
internally during it's execution. I do not have any alternative other than
executing the query simulateously from multiple clients. Can anyone suggest
a workaround for this problem?
Thanks,
Nilesh|||Narayana,
I am firing this query from two datasets for generating a report (to be
deployed on SQL Reporting server). Unable to think of any workaround for
that. Any help would be greatly appreciated.
thanks,
Nilesh
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:eOsdGr1jEHA.384@.TK2MSFTNGP10.phx.gbl...
> I haven't tested this myself, but definitely seems like a locking issue on
> the file, and can see why that would be the case.
> Can you load the file into one table first, and then use DTS to parallelly
> copy that table to multiple locations?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Nilesh" <nilesh.oswal@.gmail.com> wrote in message
> news:%23rXnKvzjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hi,
> While executing
> select * from fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL.10\MSSQL\DATA\log.trc', default);
> query simulateneously from multiple clients, only one
> of them succeeds and all others give the following error message:
> File 'C:\Program Files\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\log.trc'
> either does not exist or is not a recognizable trace file. Or there was an
> error opening the file.
> Seems like the "fn_trace_gettable" function is locking the trace log file
> internally during it's execution. I do not have any alternative other than
> executing the query simulateously from multiple clients. Can anyone
suggest
> a workaround for this problem?
> Thanks,
> Nilesh
>
>

Concatenating string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
DanOn Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
--
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegroups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
> > When executing the following statements:
> > declare @.x char(10), @.y char(10)
> > set @.x = 'abc'
> > set @.y = @.x + 'def'
> > select @.x
> > select @.y
> > the results are:
> > abc
> > abc
> > I expect @.y should be equal to 'abcdef'. If I change the var types to
> > int for example, then @.y is summed correctly. Can anyone tell me why,
> > or what I'm doing wrong? Thanks.
> > Dan
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.

Concatenating string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
DanOn Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegroups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
>
>
>
>
>
>
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.

Concatenating string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
Dan
On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.
|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegro ups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>
|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
>
>
>
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan
|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.