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 Visability in a table

Hi,
I'm new to RS and want to know how to write an expression which will set
visablility of a table group header to false if group1.value = "Account".
Any ideas?
Thanks
Jonthe basics of using an expression to hide a text box is this:
Type an expression that evaluates to a Boolean: True to hide the item and
False to show the item. Click the expression (fx) button to edit the
expression.
Remember, true = hidden, false = show
I don't know if that will work for your table group header as well, but
thats how it works with text boxes.
Karl
"jonwolds" wrote:
> Hi,
> I'm new to RS and want to know how to write an expression which will set
> visablility of a table group header to false if group1.value = "Account".
> Any ideas?
> Thanks
> Jon

Conditional update

I want to update a varbinary(max). If the column is null, then I will just
set it, but if not null, then append with a .Write. Something like:
row = select...
if (row.Document = null)
Update myTable
Set Document = 0xFF
where FileName = 'Text99.txt';
else
UPDATE myTable
SET Document .WRITE(0xFF, null, 0)
WHERE FileName = 'Text99.txt';
What is the pattern to do this sort of thing? TIA
William Stacey [MVP]William Stacey [MVP] wrote:
> I want to update a varbinary(max). If the column is null, then I
> will just set it, but if not null, then append with a .Write.
> Something like:
> row = select...
> if (row.Document = null)
> Update myTable
> Set Document = 0xFF
> where FileName = 'Text99.txt';
> else
> UPDATE myTable
> SET Document .WRITE(0xFF, null, 0)
> WHERE FileName = 'Text99.txt';
> What is the pattern to do this sort of thing? TIA
Update MyTable
Set Document =
CASE ISNULL(Document, -99)
WHEN -99 THEN SOMETHING
WHEN Document THEN SOMETHING_ELSE
END
Where FileName = 'Text99.txt'
Even easier would be to write a stored procedure and set the new column
value accordingly using a local variable.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks David.
William Stacey [MVP]

Conditional Trigger

I am trying to write a trigger that will only fire when certain fields are updated. I can't seem to find any threads that relate to this issue. Below is the trigger syntax. Any info would be great. Thx

CREATE TRIGGER tr_feedback_date ON Calls
FOR UPDATE AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
if update (feedback) or update (analyst) or update (coordinator) or update (technical) or update (fixtime)
or update (phonewait) or update (resp)
UPDATE hold_complete
set hold_complete.feedbackdt = getdate()
from inserted with (nolock)
where hold_complete.fkey = inserted.callidThe create trigger statement can take an IF UPDATE ( column ) clause. You can read about it in Books Online.

Another option it to compare the values in the INSERTED table to the values in the DELETED table. Since updates involve both insertions and deletions, your affected record will exist in both and you can compare the differences based on the primary key.

blindman|||Also, there is very useful function COLUMNS_UPDATED() which you can use in trigger for checking what fields were updated.

conditional query

Hi there!

I'm struggling to write the correct SQL to do the following task:

there are 3 fields in a table: SSize1, SSize2, SSize3.
the data type of all three is integer.
i need to do a simple calculation to determine SSize based on the following condition:
if SSize3 <>0 then
SSize = SSize1 + (SSize2/SSize3)
else SSize = SSize1
end if

can anyone please help me? thanks in advance!

regards
leeDoes your SQL dialect support the CASE operator? That would be how I'd approach solving your problem.

-PatP|||do you want to get the SSize per each row?
If so, I think you have to store these 3 fields first in a temp table. Then use a cursor, or something like that,for your calculations to compute SSize per row.

If the SSize would get the total of these 3 fields, get first the sum for each of these fields. Placed it in integer variables, then proceed to your calculations. Try this

select @.SSize1 = sum(SSize1), @.SSize2 = sum(SSize2), @.SSize3 = sum(SSize3)
from table_name

if @.SSize3 <> 0 then
SSize = @.SSize1 + (@.SSize2/@.SSize3)
else
SSize = @.SSize1
end if

hope this would work on you :)|||Or, perhaps,

SELECT DECODE(SSize3, 0, SSize1, SSize1 + (SSize2 / SSize3)) SSize
FROM table_name;

Conditional Formatting With DateDiff

I am attemting to write an expression that changes the background color if the difference between two dates is less than 60 days. I have this expression but it does not work. The color never changes.

=IIF (DateDiff("Day", Fields!pract_start_date.Value, Fields!project_start_date.Value) < 60,"Yellow","White")

Maybe you should wrap the DateDiff() with an ABS() in case the dates are in the wrong order in your function, resulting in a DateDiff of -60 rather than 60?

|||

That's exactly what was wrong. I had them in the wrong order. I thought I had checked for that but, evidently, did not. Thanks.

Conditional CREATE SCHEMA

Hi,
I would like to write TO-SQL batches that call the CREATE SCHEMA...
statement. However, there is a reasonable likelihood that the schema I want
to create already exists. However, the CREATE SCHEMA statement has the
limitation that it be the first command in a batch. I found out the hard
way that this means that I can't run a batch like this...
IF (SCHEMA_ID('MySchema') IS NULL)
CREATE SCHEMA [MySchema] AUTHORIZATION dbo;
How do I conditionally create a schema based on whether it currently exists?
I came up with this, but I'd like to think there is something a little more
elegant.
DECLARE @.CS varchar(255);
SET @.CS = 'CREATE SCHEMA [MySchema] AUTHORIZATION dbo;';
IF (SCHEMA_ID('MySchema') IS NULL)
EXEC (@.CS);
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHi Daniel
Although you may not think your solution elegant it is a solution to the
issue which can be useful, for instance if you want to take some action
outside the scope of the current transaction.
John
"Daniel Jameson" wrote:
> Hi,
> I would like to write TO-SQL batches that call the CREATE SCHEMA...
> statement. However, there is a reasonable likelihood that the schema I want
> to create already exists. However, the CREATE SCHEMA statement has the
> limitation that it be the first command in a batch. I found out the hard
> way that this means that I can't run a batch like this...
> IF (SCHEMA_ID('MySchema') IS NULL)
> CREATE SCHEMA [MySchema] AUTHORIZATION dbo;
> How do I conditionally create a schema based on whether it currently exists?
> I came up with this, but I'd like to think there is something a little more
> elegant.
> DECLARE @.CS varchar(255);
> SET @.CS = 'CREATE SCHEMA [MySchema] AUTHORIZATION dbo;';
> IF (SCHEMA_ID('MySchema') IS NULL)
> EXEC (@.CS);
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>|||Hi Daniel,
I understand that you would like to find an elegant way to create a schema
if the schema does not exist.
If I have misunderstood, please let me know.
I think that your current workaround is elegant. If you use "Generate
Scripts... " to generate the script of schema objects in SQL Server 2005
Management Studio, you will find the following scripts:
USE [AdventureWorks]
GO
/****** Object: Schema [HumanResources] Script Date: 09/17/2007
13:41:51 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'HumanResources')
EXEC sys.sp_executesql N'CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]'
GO
As you can see, it is very similar to yours. Currently I do not think that
there is a better way due to the design limitation of using CREATE SCHEMA.
Please feel free to let us know if you have any other questions or
concerns. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Daniel,
What is this issue going on?
If there is any issue, please feel free to post back. We are very glad for
further assistance.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Concurrent access,, Locks, and Deadlocks

We use the following sp in our VB applications.
The basic logic in this sp is 1) read a row, 2)marked the row is
"checked out), 3) write the same row to another table.
This sp will be executed by many users at the same time. The current
logic we built having slow response, but won't lead to any
locking/deadlocks. However, if we add transaction to make sure the row
we read (you do not want others to have same row) is locked, then we
starting have deadlock/lock problem. Any suggestions to improve the
performance without risk of deadlocks (assuming we have good indexes on
the table)?
Thank you very much for your expertise.
CREATE PROCEDURE sp_get_dcn_sql
@.UserID AS char(8),
@.ProfileID as int
AS
Set nocount on
declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
@.sqlstr as nvarchar(3000)
declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
char(5)
declare @.flagGoodDCN as char(1)
create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
tempDept char(5) NULL)
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
tblYZProfileText where ProfileID = @.ProfileID
set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
where ' +
@.tempWhere + ' and (check_out is null or check_out = ''N'' or
check_out <> ''Y'') '
if ltrim(rtrim(@.tempOrderBy)) is not null
set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
set @.flagGoodDCN = ' '
while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
begin
insert into #tempDCN exec sp_executesql @.sqlstr
select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
#tempDCN
if @.tempDCN is not null
begin
update tblYZInventoryDetail set check_out = 'Y'
where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
WorkedUser)
select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
if @.@.error = 0 --catch PK violation
set @.flagGoodDCN = 'Y'
else
truncate table #tempDCN --go loop
end
else
begin
break
end
end
select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
drop table #tempDCN
Set nocount off
GO
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!I'm guessing that you're experiencing conversion deadlocks when the read
locks taken by the select later need to be upgraded to exclusive locks for
the update.
One common solution for this problem is to take update locks on the select
which should ease the deadlock problem when you introduce the transaction
statement, eg:
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy
from tblYZProfileText WITH (UPDLOCK)
where ProfileID = @.ProfileID
You can read up on this locking hint in SQL Server Books Online here:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_1hf7.asp
Take care not to over-use locking hints as they can hurt you more than help
you if you use them when you don't need to..
HTH
Regards,
Greg Linwood
SQL Server MVP
"YZ" <ycz@.dex.com> wrote in message
news:eR5yylNnEHA.3988@.tk2msftngp13.phx.gbl...
> We use the following sp in our VB applications.
> The basic logic in this sp is 1) read a row, 2)marked the row is
> "checked out), 3) write the same row to another table.
> This sp will be executed by many users at the same time. The current
> logic we built having slow response, but won't lead to any
> locking/deadlocks. However, if we add transaction to make sure the row
> we read (you do not want others to have same row) is locked, then we
> starting have deadlock/lock problem. Any suggestions to improve the
> performance without risk of deadlocks (assuming we have good indexes on
> the table)?
> Thank you very much for your expertise.
>
> CREATE PROCEDURE sp_get_dcn_sql
> @.UserID AS char(8),
> @.ProfileID as int
> AS
> Set nocount on
> declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
> @.sqlstr as nvarchar(3000)
> declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
> char(5)
> declare @.flagGoodDCN as char(1)
> create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
> tempDept char(5) NULL)
> select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
> tblYZProfileText where ProfileID = @.ProfileID
> set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
> where ' +
> @.tempWhere + ' and (check_out is null or check_out = ''N'' or
> check_out <> ''Y'') '
> if ltrim(rtrim(@.tempOrderBy)) is not null
> set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
> set @.flagGoodDCN = ' '
> while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
> begin
> insert into #tempDCN exec sp_executesql @.sqlstr
> select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
> #tempDCN
> if @.tempDCN is not null
> begin
> update tblYZInventoryDetail set check_out = 'Y'
> where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
> insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
> WorkedUser)
> select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
> if @.@.error = 0 --catch PK violation
> set @.flagGoodDCN = 'Y'
> else
> truncate table #tempDCN --go loop
> end
> else
> begin
> break
> end
> end
> select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
> drop table #tempDCN
> Set nocount off
> GO
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Concurrent access,, Locks, and Deadlocks

We use the following sp in our VB applications.
The basic logic in this sp is 1) read a row, 2)marked the row is
"checked out), 3) write the same row to another table.
This sp will be executed by many users at the same time. The current
logic we built having slow response, but won't lead to any
locking/deadlocks. However, if we add transaction to make sure the row
we read (you do not want others to have same row) is locked, then we
starting have deadlock/lock problem. Any suggestions to improve the
performance without risk of deadlocks (assuming we have good indexes on
the table)?
Thank you very much for your expertise.
CREATE PROCEDURE sp_get_dcn_sql
@.UserID AS char(8),
@.ProfileID as int
AS
Set nocount on
declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
@.sqlstr as nvarchar(3000)
declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
char(5)
declare @.flagGoodDCN as char(1)
create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
tempDept char(5) NULL)
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
tblYZProfileText where ProfileID = @.ProfileID
set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
where ' +
@.tempWhere + ' and (check_out is null or check_out = ''N'' or
check_out <> ''Y'') '
if ltrim(rtrim(@.tempOrderBy)) is not null
set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
set @.flagGoodDCN = ' '
while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
begin
insert into #tempDCN exec sp_executesql @.sqlstr
select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
#tempDCN
if @.tempDCN is not null
begin
update tblYZInventoryDetail set check_out = 'Y'
where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
WorkedUser)
select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
if @.@.error = 0 --catch PK violation
set @.flagGoodDCN = 'Y'
else
truncate table #tempDCN --go loop
end
else
begin
break
end
end
select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
drop table #tempDCN
Set nocount off
GO
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I'm guessing that you're experiencing conversion deadlocks when the read
locks taken by the select later need to be upgraded to exclusive locks for
the update.
One common solution for this problem is to take update locks on the select
which should ease the deadlock problem when you introduce the transaction
statement, eg:
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy
from tblYZProfileText WITH (UPDLOCK)
where ProfileID = @.ProfileID
You can read up on this locking hint in SQL Server Books Online here:
http://msdn.microsoft.com/library/en...on_7a_1hf7.asp
Take care not to over-use locking hints as they can hurt you more than help
you if you use them when you don't need to..
HTH
Regards,
Greg Linwood
SQL Server MVP
"YZ" <ycz@.dex.com> wrote in message
news:eR5yylNnEHA.3988@.tk2msftngp13.phx.gbl...
> We use the following sp in our VB applications.
> The basic logic in this sp is 1) read a row, 2)marked the row is
> "checked out), 3) write the same row to another table.
> This sp will be executed by many users at the same time. The current
> logic we built having slow response, but won't lead to any
> locking/deadlocks. However, if we add transaction to make sure the row
> we read (you do not want others to have same row) is locked, then we
> starting have deadlock/lock problem. Any suggestions to improve the
> performance without risk of deadlocks (assuming we have good indexes on
> the table)?
> Thank you very much for your expertise.
>
> CREATE PROCEDURE sp_get_dcn_sql
> @.UserID AS char(8),
> @.ProfileID as int
> AS
> Set nocount on
> declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
> @.sqlstr as nvarchar(3000)
> declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
> char(5)
> declare @.flagGoodDCN as char(1)
> create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
> tempDept char(5) NULL)
> select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
> tblYZProfileText where ProfileID = @.ProfileID
> set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
> where ' +
> @.tempWhere + ' and (check_out is null or check_out = ''N'' or
> check_out <> ''Y'') '
> if ltrim(rtrim(@.tempOrderBy)) is not null
> set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
> set @.flagGoodDCN = ' '
> while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
> begin
> insert into #tempDCN exec sp_executesql @.sqlstr
> select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
> #tempDCN
> if @.tempDCN is not null
> begin
> update tblYZInventoryDetail set check_out = 'Y'
> where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
> insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
> WorkedUser)
> select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
> if @.@.error = 0 --catch PK violation
> set @.flagGoodDCN = 'Y'
> else
> truncate table #tempDCN --go loop
> end
> else
> begin
> break
> end
> end
> select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
> drop table #tempDCN
> Set nocount off
> GO
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Concerning .net and SQL Procedures

Recently i had to write a script in sql to compare multiple tables to get a result of items that do not conform to certain business logic. In doing so i wrote all of this information into a sql parameter which branches out to a few other parameters within the parameter.

Now if you need the code just let me ask, but this is a general question to see if it has occured for anyone else.

The problem i am recieving is when i access the code from a .net windows application it tells me:

Error Message:
Insert Error: Column name or number of supplied values does not match table definition.
Insert Error: Column name or number of supplied values does not match table definition.

Procedure Errored On: val_GetDuplicateItemsFromAssignment
Line Number: 16

However when i run the sql parameter within SQL it accesses it just find. This is using the same parameter values.

Does anyone know why this could be happening?

Please do show the code used to insert the values.

concatenation with space

Hi,

I am unable to concat 2 fields with a space between them in sql query.
I want to write my query in following fashion only as there are many conditions which I concat. Thus I am using variable @.sql_st and not the direct sql statement.

Following query works perfect
DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + EM.LAST_NAME) as emp_name from employee_master em'
execute (@.SQL_ST)

but when modified to get space between first & last name of employee I get an error
DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + ' ' + EM.LAST_NAME) as emp_name from employee_master em'
execute (@.SQL_ST)

Pls reply ASAP.

Thanks
ShubhangiHi,

USE the below...

DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + ' + ' ' + 'EM.LAST_NAME) as emp_name from employee_master em'
execute (@.SQL_ST)

Cheers,
Sharmila|||Hi,

Space doesn't appears between first & last name using the below query.
please reply.

Quote:

Originally Posted by Senthil

Hi,

USE the below...

DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + ' + ' ' + 'EM.LAST_NAME) as emp_name from employee_master em'
execute (@.SQL_ST)

Cheers,
Sharmila

|||Hi,

Sorry Please use like this..

DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + '' '' + ' + ' ' + 'EM.LAST_NAME) as emp_name from employee_master em'
print @.SQL_ST

Regards,
Sharmila

concatenating Varchar and Text

I am trying to write a select statement that concatenates a Varchar and Text
field into a Text.
So I basically want something like:
Select VarcharColumn + TextColumn from tablea
I know that I could convert the TextColumn to varchar(8000) and concatenate
that with the varchar column but there may be instances where the TextColumn
exceeds 8000 bytes. So I would need the datatype of this concatenated field
to be of type TEXT.
Any help would be appreciated.
ThanksYou will have to use UPDATETEXT to concatenate text columns. Check out the
details, syntax and examples of UPDATETEXT in SQL Server Books Online.
Anith

concatenating Varchar and Text

I am trying to write a select statement that concatenates a Varchar and Text
field into a Text.
So I basically want something like:
Select VarcharColumn + TextColumn from tablea
I know that I could convert the TextColumn to varchar(8000) and concatenate
that with the varchar column but there may be instances where the TextColumn
exceeds 8000 bytes. So I would need the datatype of this concatenated field
to be of type TEXT.
Any help would be appreciated.
ThanksYou will have to use UPDATETEXT to concatenate text columns. Check out the
details, syntax and examples of UPDATETEXT in SQL Server Books Online.
--
Anith

concatenating Varchar and Text

I am trying to write a select statement that concatenates a Varchar and Text
field into a Text.
So I basically want something like:
Select VarcharColumn + TextColumn from tablea
I know that I could convert the TextColumn to varchar(8000) and concatenate
that with the varchar column but there may be instances where the TextColumn
exceeds 8000 bytes. So I would need the datatype of this concatenated field
to be of type TEXT.
Any help would be appreciated.
Thanks
You will have to use UPDATETEXT to concatenate text columns. Check out the
details, syntax and examples of UPDATETEXT in SQL Server Books Online.
Anith

concatenating strings like summing numbers

I'd like to join string within a group, preferable with a separator, the same
way I'd sum values.
So, I'd like to write somethign like
=Join( Fields!Email.Value, ";" )
to obtain a string of semicolon separated substrings, the same way I'd say
=Sum( Fields!Number.Value) to get the sum of numeric field.
Does anyone know a way to do this?Having exactly the same problem. Thought of writing custom code, but not sure
what arguments to pass. The code had an array of strings as an argument, but
when i called the function the same way I would call an aggregate, got an
error message saying that aggregates can take only numeric values as
arguments..
"Max" wrote:
> I'd like to join string within a group, preferable with a separator, the same
> way I'd sum values.
> So, I'd like to write somethign like
> =Join( Fields!Email.Value, ";" )
> to obtain a string of semicolon separated substrings, the same way I'd say
> =Sum( Fields!Number.Value) to get the sum of numeric field.
> Does anyone know a way to do this?
>
>

Concatenating Strings

Suppose that I get a resultset of state abbreviations from dataset. Is there a way to write a code so that final output would be concatenation of states separated by a ",".

I tried maintaining a variable in the "Code" block of the report. Apparently, the delcarations do not work as expected.

I want to do something like this:

Dim str As String

and then str should be updated as each row gets rendered. Is that possible?

Thanks.

You may want to read this blog article about "custom aggregates": http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

-- Robert