mapping XML data to variable

I can’t figure out how to map xml data stored in a table to a variable in integration service.

For example:
I would like to use a “for each loop container” to iterate through a row set selected from database. Each row has three columns, an integer, a string and an xml data. In the variable mappings, I can map the integer column and the string column to a variable with type of int and a variable with type of string. But I am having trouble to map the xml data column to any variable. I tried using either a string variable or object. It always reports error like “variable mapping number X to variable XXX can’t apply”.

Any help?

This is a supported scenario. Ensure that:

The column is actually being loaded into the record set: Check the column mappings in the recordset dest The value being mapped to the variable is less than 4000 characters long: select max(datalength(xmlCol)) from XmlTable

Mapping of an xml column to variable

I have a For Each Loop that iterates over a recordset stored in a variable. One of the columns in the recordset is type xml and I want to map it to a variable using Variable Mappings of the For Each Loop container. I am getting this error:

Error: 0xC001C012 at FELC Loop thru report defs: ForEach Variable Mapping number 4 to variable "User::Parameters_xml" cannot be applied.

I have tried changing the type of the Parameters_xml variable to Object and String, but I get the same error. Any ideas?

you can try using a script task instead.|||I tried casting the xml-typed column to varchar(8000) and then it worked.

Map One generic Input column to multiple Destination column

I have a stored proc I am updating in an OLEDB Command from the results of a Transform Script Component. The Stored Proc has over 65 input parameters, most of them have a NULL passed in. I dont want to create output columns in the Transform Script Component for all of them to map them from the "Available Input Columns" to "Available Destination Columns".

I want to create 3 or 4 generic Output columns for their data type - say IntegerOutput (datatype Int), DateTimeOut (datatype datetime) and so on. The I want to map these generic columns in the OLEDB Command as Available Input Columns" to multiple "Available Destination Columns" - wherever the datatype matches the input column.

But the OLEDB Command Column Mappings let me map One to One only. This will create a huge and unnecessary workload for me to develop and maintain - when I tell you I have 3 such stored procedures, all of whose interfaces are exactly same and for which I can create similar Output columns in the Transform Script Component.

So how do I go about doing this the smart way?

thanks in advance!

Hi,

You can use "Copy Column" transformation component to copy one input column to multiple output columns. If you have to perform some computing between original and new columns, you can use "Derive Column" transformation.

Jean-Pierre Riehl

http://blog.djeepy1.net

http://www.bewise.fr

|||Sorry not very elegant, this is more work than creating all the output columns one by one. I want to create one DataType_NULL Column which I want to reuse to map to the destination columns.

|||For what you are describing I would probably just call the stored procedure from inside the script component. As you have seen, the OLE Command doesn't really support this, so script, Copy Column, or Derived Column are the only way to do this that I am aware of.

Many Year Payments

I need to create a stored procedure that will show all clients that have made payments every year for a number of years (3) or (5). My tables are simple but I have no clue how to ask the SQL question, mostly because it is each and every year.

tblClients ClientID (pk)
tblOrders OrderID (pk) ClientID (fk)
tblOrderPayments PaymentID (pk) OrderID (fk)

Thanks for any help,Depends on how you would like the output. Also: In your tables there's no date. And is not possible for one payment to apply to many orders?

And before I start: You do not 'ask SQL questions', rather you 'construct SQL queries'. And lastly: Always use singular names for your tables, e.g. Client and not Clients, since all tables can hold multiple records all of your table names end up being plural and so the plural becomes redundant.

Anyway. I'll use some common sense to fill in the gaps in your question: I'll assume the payment date rather than the order date is the important one, and I'll assume therefore there's a date on the payment table. Also I'll assume for a moment that your DB design is correct although I doubt it, since it's almost certainly the case that several orders can be paid with one order. I do not know of businesses that does not allow you to pay off several orders in one payment.

So your question as stated is like follows: You want a list of all clients that's made at least one payment a year for the last X years. Is that right? Sounds like a weird question for a business to ask...

If this is indeed what they want, it's a rather interesting SQL problem. I'll give you a few solutions just for my own amusement, even though I suspect they'll be useless for you because your DB design is wrong and you probably misunderstood the needs of your management.

Here's my first attempt:

set @.EndYear = 2004
select CustomerID,
 CustomerName
 from Customer C
 where CustomerID in (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear)
 and CustomerID in (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - 1)
 and CustomerID in (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - 2)

It will work for the 3 year case, but sucks bigtime. If you have a small database (less than 1 million orders) then this will be the way to go since it's easy to understand and you probably won't notice and bad performance. If one of my guys wrote this query for me I'd not be impressed though.
It sucks because:
- Only works for a set number of years. You'll have to write 2 of them to cater for 3 or 5 year queries
- Uses IN with large sets, several times. Bad for performance.
It's not all bad, though. It's cool because:
- It's simple to understand for anyone out there
- Does not use cursors or temp tables

Here's a slightly better one:

set @.EndYear = 2004
select CustomerID,
 CustomerName
 from Customer C
 join (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear) as Y1
 on C.CustomerID = Y1.CustomerID
 join (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - 1) as Y2
 on C.CustomerID = Y2.CustomerID
 join (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - 2) as Y3
 on C.CustomerID = Y2.CustomerID

The same as the first one, but without the expensive IN statements. Still uses a lot of DISTINCTs though, and you still have to write 2 queries for the 3 and 5 year cases. Let's extrapolate this query to one that will work for any number of years from 1 to 5:

set @.EndYear = 2004
set @.NoYears = 4
select CustomerID,
 CustomerName
 from Customer C
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear) as Y1
 on C.CustomerID = Y1.CustomerID
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and (datepart(year, P.Date) = @.EndYear - 1
 or @.NoYears < 2)) as Y2
 on C.CustomerID = Y2.CustomerID
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and (datepart(year, P.Date) = @.EndYear - 2
 or @.NoYears < 3)) as Y3
 on C.CustomerID = Y3.CustomerID
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and (datepart(year, P.Date) = @.EndYear - 3
 or @.NoYears < 4)) as Y4
 on C.CustomerID = Y4.CustomerID
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and (datepart(year, P.Date) = @.EndYear - 4
 or @.NoYears < 5)) as Y5
 on C.CustomerID = Y5.CustomerID

Whoa! That's a good one eh? Again, it will work, but I'll probably get blasted by the community here for even thinking about posting **** like that. The query will work for any number of years to check from 1 to 5, so you can do your 3 and 5 year queries on it just fine without the need to write 2 seperate queries.

Ok, enough of that. Now let's try another approach: Instead of doing it all in one SQL statement, we'll create a temp table with all CustomerIDs in it. Then we'll iterate through the years we want to check, and remove all customers that did not make a payment in each year. So in the end we're left with only the customers that made payments in all the years checked.

set @.EndYear = 2004
set @.NoYears = 4
set @.I = 0
select CustomerID
 into #Temp
 from Customer
while (@.I < @.NoYears)
begin
 delete T
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - @.I
 right join #Temp T
 on O.CustomerID = T.CustomerID
 where P.OrderID is null
 set @.I = @.I + 1
end
select C.CustomerID,
 C.CustomerName
 from Customer C
 join #Temp T
 on C.CustomerID = T.CustomerID

This is also a nice and simple solution, and will probably give you fairly decent performance. The good thing about it is that it will work for ANY number of years, although the more years you do the longer it will take. It's also sexy because it uses a RIGHT JOIN, my favourite type of join. :)

I'm going to stop here, but there are almost certainly more elegant solutions out there. I can imagine one where you use a helper table for all the years you want to check, and then do a distinct and a having count on a simple join in a sub-query - it will work in a single query and will be able to do an arbitraty number of years. I'll challenge anyone else out there to give it a shot and I'll post it in a day or two.|||Hi i think i found a nice solution for you :).

Data: i used the Northwind database for testing, i created a payments table containing customerid, orderid and paymentdate and populated it with testdata using the orders table.

DECLARE @.YearSpan int
SET @.YearSpan = 3
SELECT
 YearsOfPayment.CustomerID,
 Sum(FirstYear) As FirstYearPayments,
 Sum(SecondYear) As SecondYearPayments,
 Sum(ThirdYear) As ThirdYearPayments,
 Sum(FourthYear) As FourthYearPayments,
 Sum(FifthYear) As FifthYearPayments
FROM
 (
 Select
 Customers.CustomerID,
 (
 Case When Payments.Paymentdate Between '01/01/1996' And '12/31/1996' Then 1 Else 0 End
 ) AS FirstYear,
 (
 Case When Payments.Paymentdate Between '01/01/1997' And '12/31/1997' Then 1 Else 0 End
 ) AS SecondYear,
 (
 Case When Payments.Paymentdate Between '01/01/1998' And '12/31/1998' Then 1 Else 0 End
 ) AS ThirdYear,
 (
 Case When Payments.Paymentdate Between '01/01/1999' And '12/31/1999' Then 1 Else 0 End
 ) AS FourthYear,
 (
 Case When Payments.Paymentdate Between '01/01/2000' And '12/31/2000' Then 1 Else 0 End
 ) AS FifthYear
 From
 Customers
 Inner Join Payments On Payments.CustomerID = Customers.CustomerID
 ) AS YearsOfPayment
GROUP BY
 YearsOfPayment.CustomerID
 HAVING
 SUM(FirstYear) > 0
 AND SUM(SecondYear) > 0
 AND SUM(ThirdYear) > 0
 AND SUM(FourthYear) > (CASE WHEN @.YearSpan > 3 THEN 0 ELSE -1 END)
 AND SUM(FifthYear) > (CASE WHEN @.YearSpan > 4 THEN 0 ELSE -1 END)

Todo:
You have to replace the hardcoded dates with a @.FirstYear Parameter and modify the WHEN
clauses accordingly ;)

Good things here:
- if you need to check for more years, just modify it to return an additional CASE column
- performance won't change with the number of years checked (= the number of CASES you return since no subselects are involved

Further Tuning:
- to speed it up a even little more you can create and index on the paymentdate and put a WHERE statement in the "inner" SELECT to only check the years between @.firstdate and @.firstdate + @.yearspan

- Moon|||:: And before I start: You do not 'ask SQL questions', rather you 'construct SQL queries'.

... and before you post again, you should rethink your arrogant attidue and stop correcting someone since most people will find this very offensive.

:: And lastly: Always use singular names for your tables, e.g. Client and not Clients,
:: since all tables can hold multiple records all of your table names end up being plural
:: and so the plural becomes redundant.

... from OO point of view where you think of collections and items, a table is a collection and holding items (records) and therefore it is 100% valid to use plural.|||Moon's solution is novel, but only works for up to a certain number of years. Here's my solution that will work for an arbitrary number of years, with only one query and no temp tables or cursors:

set @.LastYear = 2004
set @.NumYears = 5
 select D.CustomerId,
 C.[Name]
 from Customer C
 join (select distinct O.CustomerId,
 datepart(year, P.[Date]) as PaidYear
 from [Order] O
 join Payment P
 on O.OrderId = P.OrderId
 and datepart(year, P.[Date]) <= @.LastYear
 and datepart(year, P.[Date]) > @.LastYear - @.NumYears) as D
 on C.CustomerID = D.CustomerID
group by D.CustomerID,
 C.[Name]
 having count(*) = @.NumYears

Who's your daddy! $5 for anyone with a more elegant solution.
And if I come off as arrogant, feel free to skip my comments and just look at my code.|||Relational databases are NOT OO and therefore your plural argument does not hold.

From IDEF1X

December 21, 1993

Section: 3.1.2 Entity Syntax

...The entity name is a noun phrase that describes the set of things the entity
represents. The noun phrase is in singular form, not plural. Abbreviations and
acronyms are permitted, however, the entity name must be meaningful and consistent
throughout the model...

IE and other data modeling standards organisations agree with the above statement. Also I was taught the same thing at University in my first year. I was under the impression that this is common knowledge.|||*points at the year, cough, :P*

well as long as Microsoft uses the plural convention in their own databases: master, Northwind, Pubs, ... i imply that there is a point in using plural for his own database objects as well ;)

Also i didn't say that using singular is a complete NoNo, just that using plural is not forbidden but valid to use.

PS: University != real life ;). I also was taught a few things in university such as C++ that are invalid by now - since when i learned C++, it was still a draft, without namespaces, and whatnot and if i would need to code in C++ today it would be a nearly a full restart.|||Yeah this one is nice :)

And sorry my yesterdays comment was rude as well :S.|||Like I said: If you start naming tables with plurals, then in the end ALL your tables (or at least a very large percentage) will end up being plural, and so the 's' at the end of every table becomes just another letter you have to type in every line of SQL code and serves no purpose whatsoever. Remember that 99% of all tables will have more than one row and therefore should in your argument be plural in the naming.

And who ever said MS wrote good code, eh?

The date may be 10 years old, but please feel free to provide me with a more recent reference from an SQL standards authority that says 'Yea, we made a mistake in '93, go ahead and use plurals now'

I disagree with you. There's no good reason to use plurals. Using plurals is a nono. Except if you're MS in which case you are expected to write bad code and thus are allowed plurals in table names.

Manipulating the result set of one stored procedure from another....

Hi,

I have one stored procedure that calls another ( EXEC proc_abcd ). I would
like to return a result set (a temporary table I have created in the
procedure proc_abcd) to the calling procedure for further manipulation. How
can I do this given that TABLE variables cannot be passed into, or returned
from, a stored procedure?

Thanks,

Robin

Example: (if such a thing were possible):

DECLARE @.myTempTable1 TABLE ( ID INT NOT NULL )
DECLARE @.myTempTable2 TABLE ( ID INT NOT NULL )

....
/*
Insert a test value into the first temporary table
*/

INSERT INTO @.myTempTable1 VALUES ( 1234 )
....

/*
Execute a stored procedure returning another temporary table of
values.
*/

EXEC proc_abcd @.myTempTable2 OUTPUT

...
...

/*
Insert the values from the second temporary table into the first.
*/

SELECT * INTO @.myTempTable1 FROM @.myTempTable2Robin Tucker (idontwanttobespammedanymore@.reallyidont.com) writes:
> I have one stored procedure that calls another ( EXEC proc_abcd ). I
> would like to return a result set (a temporary table I have created in
> the procedure proc_abcd) to the calling procedure for further
> manipulation. How can I do this given that TABLE variables cannot be
> passed into, or returned from, a stored procedure?

Have a look at http://www.sommarskog.se/share_data.html where I discuss
various techniques.

> SELECT * INTO @.myTempTable1 FROM @.myTempTable2

You cannot do a SELECT INTO with a table variable.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

manipulating ntext in stored procedures

Hi,

I have been trying to write a SP where, in the result set is brought back, two ntext columns are combined with a text string, However I get an error when I click on apply;

"Error 403: Invalid operator for data type. Operator equals add, type equals ntext"

I can only assume that this means you can't add (or even manipulate) ntext columns in a SP.

Short of changing the sp to bring back 3 columns and to do the manipulation in my c# code I can't think of a way round this.

Anyone with any ideas?

thanks in advance

rich

SP code ( I have highlighted the problem area):

CREATE PROCEDURE [dbo].[usp_RCTReportQuery]

@.PersonType varchar(255),
@.Status varchar(255),
@.Oustanding varchar(255)

AS

DECLARE @.PersonTypeID int,
@.StatusID int,
@.OustandingID int

SELECT @.PersonTypeID = PersonTypeID FROM TBL_LU_People_Type WHERE PersonType = @.PersonType
SELECT @.StatusID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Status
SELECT @.OustandingID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Oustanding

SELECT surname as [Potential Claimant], s.thevalue + '<BR /><BR />' + o.thevalue as [Status]
FROM tbl_people p
INNER JOIN tbl_Lu_people lup on lup.personid = p.personid and persontypeid = @.PersonTypeID
LEFT JOIN tbl_memo s on s.caseID = p.caseID and s.memotypeid = @.StatusID
LEFT JOIN tbl_memo o on o.caseID = p.caseID and o.memotypeid = @.OustandingID
GO

Richie:

If you are running SQL Server 2005 you might be able to leverage a function to work around this issue. Perhaps a function like this:

alter function dbo.appendText
( @.prm_textIn nvarchar(max),
@.prm_appendString varchar (8000)
)
returns nvarchar (max)
as

begin

return ( @.prm_textIn + @.prm_appendString )

end

This is coded as a scalar function; you might be better off setting it up as an inline function so that you can potentially leverage it in the future with the CROSS JOIN operator for other purposes. Below is an example:

declare @.what nvarchar (max) set @.what = ''
declare @.firstPart varchar (40) set @.firstPart = ' ** This is'
declare @.secondPart varchar (40) set @.secondPart = ' a test. **'

select @.what = @.what + ' #' + convert (nvarchar(5), iter)
from small_iterator (nolock)

select len (@.what) as [len],
substring (dbo.appendText (@.what, @.firstPart + @.secondPart), len(@.what) - 6, 30)
as [righthand piece]

-- Sample Output: --

-- len righthand piece
-- -- --
-- 218263 #32767 ** This is a test.

Dave

|||if you are on 2005, you could just use a nvarchar(MAX) column instead of ntext.

www.elsasoft.org|||

Richie:

You might be able to get your query to work in SQL 2005 doing something like this:

SELECT surname as [Potential Claimant], cast (s.thevalue as nvarchar (max)) + '<BR /><BR />' + o.thevalue as [Status]

|||

Please specify the version of SQL Server in your posts so it is easy to suggest the correct solution.

1. If you are using SQL Server 2005 then use varchar(max), nvarchar(max), and varbinary(max). The text, ntext, and image data types have been deprecated. The newer max data types will allow you to manipulate the values on the server-side just like regular varchar, nvarchar or varbinary values. Also, for backward compatibility reasons any expression that involves string concatenation will return only a string of maximum length 8000 bytes. In order to concatenate larger length strings you have to cast at least one of the values to varchar(max) or nvarchar(max). So in your SELECT list, you could do something like CAST(s.thevalue as varchar(max)) + '<BR/><BR>' + o.thevalue.

2. If you are not using SQL Server 2005 then there is no way to concatenate strings larger than 8000 bytes. You have to either dump the rows into a temporary table and use UPDATETEXT to manipulate each row individually or simply return the values as multiple columns and concatenate on the client-side

Lastly, you should actually leave the presentation to the client side and do these type of operations on the server. What if you want to later introduce a paragraph break or ident the line? What if the formatting is more complex? You should just return the strings as is to the client and then format it there.

|||

Cheers for the tip. Unfortunately I am still using Sql Server 2000 :(

I tried creating a function but you can't have variables of type ntext in functions.

Is there anyway I can do this in SQL Server 2000?

|||I am using sql server 2000 and try using table temp.

Whats wrong?

ROBUST PLAN. Somebody help?

---

No

se puede crear una fila de tabla de trabajo más larga que el máximo

admitido. Vuelva a enviar la consulta con la sugerencia ROBUST PLAN.

TRANSLATION

Quote:

Originally Posted by

A

row of table of work cannot be created longer than the admitted

maximum. Return to send the consultation with suggestion ROBUST PLAN.

Cant create line in table work longer,

CREATE TABLE temp
(
Proc_id INT,
Proc_Name SYSNAME,
Definition NTEXT
)

-- get the names of the procedures that meet our criteria
INSERT temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

--Inicializar the NTEXT column
UPDATE temp SET Definition=''

CODE --

DECLARE

@.txtPval binary(16),

@.txtPidx INT,

@.curName SYSNAME,

@.curtext NVARCHAR(4000)
--

--
DECLARE C CURSOR

LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

SELECT OBJECT_NAME(id), text

FROM syscomments s

INNER JOIN #MYTEMP t

ON s.id = t.Proc_id

ORDER BY id, colid

OPEN c -> HERE TELL ME ERROR about robust plan.

FETCH NEXT FROM c INTO @.curname, @.curtext

--Start Loop

WHILE (@.@.FETCH_STATUS = 0 )
BEGIN
--get pointer for the current procedure name colid
SELECT @.txtPval = TEXTPTR(Definition)
FROM temp
WHERE Proc_Name = @.curname

--find out where to append the @.temp table′s value

SELECT @.txtPidx = DATALENGTH(Definition)/2
FROM temp
WHERE Proc_Name = @.curName

--Apply the append of the current 8kb chunk
UPDATETEXT temp.definition @.txtPval @.txtPidx 0 @.curtext
FETCH NEXT FROM c INTO @.curName, @.curtext
END

-- check what was produced
SELECT Proc_name, Definition, DATALENGTH(Definition)/2
FROM temp

-- check our filter
SELECT Proc_Name, Definition
FROM temp
WHERE definition LIKE '%foobar%'

--Clean up

DROP TABLE temp
CLOSE c
DEALLOCATE c
Thanks for try help.

manipulating ntext in stored procedures

Hi,

I have been trying to write a SP where, in the result set is brought back, two ntext columns are combined with a text string, However I get an error when I click on apply;

"Error 403: Invalid operator for data type. Operator equals add, type equals ntext"

I can only assume that this means you can't add (or even manipulate) ntext columns in a SP.

Short of changing the sp to bring back 3 columns and to do the manipulation in my c# code I can't think of a way round this.

Anyone with any ideas?

thanks in advance

rich

SP code ( I have highlighted the problem area):

CREATE PROCEDURE [dbo].[usp_RCTReportQuery]

@.PersonType varchar(255),
@.Status varchar(255),
@.Oustanding varchar(255)

AS

DECLARE @.PersonTypeID int,
@.StatusID int,
@.OustandingID int

SELECT @.PersonTypeID = PersonTypeID FROM TBL_LU_People_Type WHERE PersonType = @.PersonType
SELECT @.StatusID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Status
SELECT @.OustandingID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Oustanding

SELECT surname as [Potential Claimant], s.thevalue + '<BR /><BR />' + o.thevalue as [Status]
FROM tbl_people p
INNER JOIN tbl_Lu_people lup on lup.personid = p.personid and persontypeid = @.PersonTypeID
LEFT JOIN tbl_memo s on s.caseID = p.caseID and s.memotypeid = @.StatusID
LEFT JOIN tbl_memo o on o.caseID = p.caseID and o.memotypeid = @.OustandingID
GO

Richie:

If you are running SQL Server 2005 you might be able to leverage a function to work around this issue. Perhaps a function like this:

alter function dbo.appendText
( @.prm_textIn nvarchar(max),
@.prm_appendString varchar (8000)
)
returns nvarchar (max)
as

begin

return ( @.prm_textIn + @.prm_appendString )

end

This is coded as a scalar function; you might be better off setting it up as an inline function so that you can potentially leverage it in the future with the CROSS JOIN operator for other purposes. Below is an example:

declare @.what nvarchar (max) set @.what = ''
declare @.firstPart varchar (40) set @.firstPart = ' ** This is'
declare @.secondPart varchar (40) set @.secondPart = ' a test. **'

select @.what = @.what + ' #' + convert (nvarchar(5), iter)
from small_iterator (nolock)

select len (@.what) as [len],
substring (dbo.appendText (@.what, @.firstPart + @.secondPart), len(@.what) - 6, 30)
as [righthand piece]

-- Sample Output: --

-- len righthand piece
-- -- --
-- 218263 #32767 ** This is a test.

Dave

|||if you are on 2005, you could just use a nvarchar(MAX) column instead of ntext.

www.elsasoft.org|||

Richie:

You might be able to get your query to work in SQL 2005 doing something like this:

SELECT surname as [Potential Claimant], cast (s.thevalue as nvarchar (max)) + '<BR /><BR />' + o.thevalue as [Status]

|||

Please specify the version of SQL Server in your posts so it is easy to suggest the correct solution.

1. If you are using SQL Server 2005 then use varchar(max), nvarchar(max), and varbinary(max). The text, ntext, and image data types have been deprecated. The newer max data types will allow you to manipulate the values on the server-side just like regular varchar, nvarchar or varbinary values. Also, for backward compatibility reasons any expression that involves string concatenation will return only a string of maximum length 8000 bytes. In order to concatenate larger length strings you have to cast at least one of the values to varchar(max) or nvarchar(max). So in your SELECT list, you could do something like CAST(s.thevalue as varchar(max)) + '<BR/><BR>' + o.thevalue.

2. If you are not using SQL Server 2005 then there is no way to concatenate strings larger than 8000 bytes. You have to either dump the rows into a temporary table and use UPDATETEXT to manipulate each row individually or simply return the values as multiple columns and concatenate on the client-side

Lastly, you should actually leave the presentation to the client side and do these type of operations on the server. What if you want to later introduce a paragraph break or ident the line? What if the formatting is more complex? You should just return the strings as is to the client and then format it there.

|||

Cheers for the tip. Unfortunately I am still using Sql Server 2000 :(

I tried creating a function but you can't have variables of type ntext in functions.

Is there anyway I can do this in SQL Server 2000?

|||I am using sql server 2000 and try using table temp.

Whats wrong?

ROBUST PLAN. Somebody help?

---

No

se puede crear una fila de tabla de trabajo más larga que el máximo

admitido. Vuelva a enviar la consulta con la sugerencia ROBUST PLAN.

TRANSLATION

Quote:

Originally Posted by

A

row of table of work cannot be created longer than the admitted

maximum. Return to send the consultation with suggestion ROBUST PLAN.

Cant create line in table work longer,

CREATE TABLE temp
(
Proc_id INT,
Proc_Name SYSNAME,
Definition NTEXT
)

-- get the names of the procedures that meet our criteria
INSERT temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

--Inicializar the NTEXT column
UPDATE temp SET Definition=''

CODE --

DECLARE

@.txtPval binary(16),

@.txtPidx INT,

@.curName SYSNAME,

@.curtext NVARCHAR(4000)
--

--
DECLARE C CURSOR

LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

SELECT OBJECT_NAME(id), text

FROM syscomments s

INNER JOIN #MYTEMP t

ON s.id = t.Proc_id

ORDER BY id, colid

OPEN c -> HERE TELL ME ERROR about robust plan.

FETCH NEXT FROM c INTO @.curname, @.curtext

--Start Loop

WHILE (@.@.FETCH_STATUS = 0 )
BEGIN
--get pointer for the current procedure name colid
SELECT @.txtPval = TEXTPTR(Definition)
FROM temp
WHERE Proc_Name = @.curname

--find out where to append the @.temp table′s value

SELECT @.txtPidx = DATALENGTH(Definition)/2
FROM temp
WHERE Proc_Name = @.curName

--Apply the append of the current 8kb chunk
UPDATETEXT temp.definition @.txtPval @.txtPidx 0 @.curtext
FETCH NEXT FROM c INTO @.curName, @.curtext
END

-- check what was produced
SELECT Proc_name, Definition, DATALENGTH(Definition)/2
FROM temp

-- check our filter
SELECT Proc_Name, Definition
FROM temp
WHERE definition LIKE '%foobar%'

--Clean up

DROP TABLE temp
CLOSE c
DEALLOCATE c
Thanks for try help.

Managment Studio Scripting options?

Am I missing something on options somewhere? With Enterprise Manager when you choose to script a stored procedure you had the option to say include a drop statement and security statements. In Management Studio I don't see these options. Just script a create OR a drop. Not both. I think this is a giant step backwards for the tool if it isn't present or can't be configured.Nevermind...I found it. It was in the Generate Scripts wizard off the database. Too bad this wasn't easily exposed from the context menu on specific object (like a stored procedure).|||

This seems like a lot of work to get a script with the options I want.

Why can't the IDE just drop script options in the query window like Query Analyzer did in the past?

Managing Transactions in Stored Procedure (Nested)

Hi Everyone:

I have a master sp that calls 5 different sps. I would like to incorporate transaction(COMMIT and ROLLBACK) into my master sp. If do that, is that enough, or do I need to add some transaction code to the 5 sps that are being called. I would appreciate if you provide me with code, syntax and steps on how to do this for a specific situation like mine. I have read some articles on nested sps and transactions, but most are very complex examples, I just need a simple approach/advise. Thanks.

Rollback and Commit action should apply to your nested stored procedure calls. When you call a stored procedure within a transaction it executes within the context of that transaction. However, be wary about errors from the stored procedures you are calling from the master stored procedure. Review error handling within stored procedures to make sure you are equipt to handle nested errors and apply the proper transaction method.

Managing Transactions in Stored Procedure (Nested)

Hi Everyone:

I have a master sp that calls 5 different sps. I would like to incorporate transaction(COMMIT and ROLLBACK) into my master sp. If do that, is that enough, or do I need to add some transaction code to the 5 sps that are being called. I would appreciate if you provide me with code, syntax and steps on how to do this for a specific situation like mine. I have read some articles on nested sps and transactions, but most are very complex examples, I just need a simple approach/advise. Thanks.

Rollback and Commit action should apply to your nested stored procedure calls. When you call a stored procedure within a transaction it executes within the context of that transaction. However, be wary about errors from the stored procedures you are calling from the master stored procedure. Review error handling within stored procedures to make sure you are equipt to handle nested errors and apply the proper transaction method.

Managing the errors in stored procedures

How can I manage the errors into a stored procedure?
Example:
CREATE PROCEDURE MyProcedure
@.UserName SYSNAME = NULL
AS
BEGIN
DECLARE @.v INT
SET @.v = 0
EXEC @.v = sp_helpuser @.UserName
IF (@.@.ERROR <> 0)
BEGIN
RAISERROR('My Error Message...', 16, 1)
RETURN 1
END
IF (@.v <> 0)
BEGIN
RAISERROR('Return Value is 1', 16, 1)
RETURN 1
END
RETURN 0
END
I try this example, but in case than sp_helpuser exits with error, I did
not get my error messages in my client application.
What is the correct way to manage the errors?
Thank you for help!http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mihaly" <Mihaly@.discussions.microsoft.com> wrote in message
news:314DE418-5F6C-4115-AB97-0B93CA56AC25@.microsoft.com...
> How can I manage the errors into a stored procedure?
> Example:
> CREATE PROCEDURE MyProcedure
> @.UserName SYSNAME = NULL
> AS
> BEGIN
> DECLARE @.v INT
> SET @.v = 0
> EXEC @.v = sp_helpuser @.UserName
> IF (@.@.ERROR <> 0)
> BEGIN
> RAISERROR('My Error Message...', 16, 1)
> RETURN 1
> END
> IF (@.v <> 0)
> BEGIN
> RAISERROR('Return Value is 1', 16, 1)
> RETURN 1
> END
> RETURN 0
> END
> I try this example, but in case than sp_helpuser exits with error, I
did
> not get my error messages in my client application.
> What is the correct way to manage the errors?
> Thank you for help!
>|||> What is the correct way to manage the errors?
I ran your proc at it seems sp_helpuser exits with return code 0 and @.@.ERROR
is also 0. However, the sp_helpuser error is raised so you should be able
to detect the error in your client app. I don't know what language/API
you're using but the VBScript example below detects the sp_helpuser error.
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = connection
cmd.CommandType = adCmdStoredProcedure
cmd.CommandText = "MyProcedure"
Set pReturnCode = cmd.CreateParameter("@.RETURN_CODE")
pReturnCode.Type = adInteger
pReturnCode.Direction = adParamReturnValue
cmd.Parameters.Append pReturnCode
Set pLoginName = cmd.CreateParameter("@.UserName")
pLoginName.Type = adVarWChar
pLoginName.Direction = adParamInput
pLoginName.Value = "Test"
pLoginName.Size = Len(pLoginName.Value)
cmd.Parameters.Append pLoginName
On Error Resume Next 'trap Execute error
cmd.Execute
On Error GoTo 0 're-enable default error handler
MsgBox cmd.Parameters(0).Name & "=" & cmd.Parameters(0).Value
For Each error in connection.Errors
MsgBox error.Description
Next
Hope this helps.
Dan Guzman
SQL Server MVP
"Mihaly" <Mihaly@.discussions.microsoft.com> wrote in message
news:314DE418-5F6C-4115-AB97-0B93CA56AC25@.microsoft.com...
> How can I manage the errors into a stored procedure?
> Example:
> CREATE PROCEDURE MyProcedure
> @.UserName SYSNAME = NULL
> AS
> BEGIN
> DECLARE @.v INT
> SET @.v = 0
> EXEC @.v = sp_helpuser @.UserName
> IF (@.@.ERROR <> 0)
> BEGIN
> RAISERROR('My Error Message...', 16, 1)
> RETURN 1
> END
> IF (@.v <> 0)
> BEGIN
> RAISERROR('Return Value is 1', 16, 1)
> RETURN 1
> END
> RETURN 0
> END
> I try this example, but in case than sp_helpuser exits with error, I did
> not get my error messages in my client application.
> What is the correct way to manage the errors?
> Thank you for help!
>|||For what it's worth, I structure all my SPs similarly, using the following
pattern:
As
-- Declare the error handler variables ...
Declare @.Err Integer
Declare @.Msg VarChar(100)
/ *************************
In Functional part of the stored Proc
consists of blocks like the following,
(one block for each executable statement that might fail),
in each block, 3 parts,
1. set the string value of the error msg,
2. execute the statement, and then
3. test @.@.error, and if error occurred
Goto ErrHandler
********************************/
Set @.Msg = 'Failed, Unable to Insert <blank> into <blank>... '
Insert Tablename(... )
Values(x,y,z, etc. )
Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
-- --
Set @.Msg = 'Failed, Unable to Update Employee %d.'
Update EMployees Set
ColumnA = @.Val1,
ColumnB = @.Val2, ...
Where EmployeeID = @.EmployeeID
Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
-- --
-- ****************************************
****
-- ****************************************
****
ErrHandler:
Begin
If @.@.TranCount > 0 RollBack Transaction
If CharIndex('%d', @.Msg) > 0
Raiserror(@.Msg, 16, 1, @.EmployeeID)
Else RaisError(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)
End
-- ****************************************
****
/********************************
If there is more than one statement in the
Stored Proc, I create a block for each one
and put them all inside a Begin Tran Commit
Error Handler detects @.@.TranCount to
determine whether or not to RollBack...
This pattern allows you to put common
error handler at the end of all your SPs,
It Therefore keeps the code in the body
of the SP "Cleaner", making it easier to
read, and to see the SP's business structure
business process which you are implementing
********************************/
"Mihaly" wrote:

> How can I manage the errors into a stored procedure?
> Example:
> CREATE PROCEDURE MyProcedure
> @.UserName SYSNAME = NULL
> AS
> BEGIN
> DECLARE @.v INT
> SET @.v = 0
> EXEC @.v = sp_helpuser @.UserName
> IF (@.@.ERROR <> 0)
> BEGIN
> RAISERROR('My Error Message...', 16, 1)
> RETURN 1
> END
> IF (@.v <> 0)
> BEGIN
> RAISERROR('Return Value is 1', 16, 1)
> RETURN 1
> END
> RETURN 0
> END
> I try this example, but in case than sp_helpuser exits with error, I di
d
> not get my error messages in my client application.
> What is the correct way to manage the errors?
> Thank you for help!
>|||Uh Forgot You have to
Return(0)
After the Business Logic, before the ErrHandler Label, or the ErrHandler
Code will always run
-- Declare the error handler variables ...
Declare @.Err Integer
Declare @.Msg VarChar(100)
/ *************************
In Functional part of the stored Proc
consists of blocks like the following,
(one block for each executable statement that might fail),
in each block, 3 parts,
1. set the string value of the error msg,
2. execute the statement, and then
3. test @.@.error, and if error occurred
Goto ErrHandler
********************************/
Begin Transaction
Set @.Msg = 'Failed, Unable to Insert <blank> into <blank>... '
Insert Tablename(... )
Values(x,y,z, etc. )
Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
-- --
Set @.Msg = 'Failed, Unable to Update Employee %d.'
Update EMployees Set
ColumnA = @.Val1,
ColumnB = @.Val2, ...
Where EmployeeID = @.EmployeeID
Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
-- --
Commit Transaction
Return(0)
-- ****************************************
****
-- ****************************************
****
ErrHandler:
Begin
If @.@.TranCount > 0 RollBack Transaction
If CharIndex('%d', @.Msg) > 0
Raiserror(@.Msg, 16, 1, @.EmployeeID)
Else RaisError(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)
End
-- ****************************************
****
"CBretana" wrote:
> For what it's worth, I structure all my SPs similarly, using the followin
g
> pattern:
> As
> -- Declare the error handler variables ...
> Declare @.Err Integer
> Declare @.Msg VarChar(100)
> / *************************
> In Functional part of the stored Proc
> consists of blocks like the following,
> (one block for each executable statement that might fail),
> in each block, 3 parts,
> 1. set the string value of the error msg,
> 2. execute the statement, and then
> 3. test @.@.error, and if error occurred
> Goto ErrHandler
> ********************************/
> Set @.Msg = 'Failed, Unable to Insert <blank> into <blank>... '
> Insert Tablename(... )
> Values(x,y,z, etc. )
> Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
> -- --
> Set @.Msg = 'Failed, Unable to Update Employee %d.'
> Update EMployees Set
> ColumnA = @.Val1,
> ColumnB = @.Val2, ...
> Where EmployeeID = @.EmployeeID
> Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
> -- --
>
> -- ****************************************
****
> -- ****************************************
****
> ErrHandler:
> Begin
> If @.@.TranCount > 0 RollBack Transaction
> If CharIndex('%d', @.Msg) > 0
> Raiserror(@.Msg, 16, 1, @.EmployeeID)
> Else RaisError(@.Msg, 16, 1)
> If @.Err = 0 Set @.Err = -1
> Return(@.Err)
> End
> -- ****************************************
****
> /********************************
> If there is more than one statement in the
> Stored Proc, I create a block for each one
> and put them all inside a Begin Tran Commit
> Error Handler detects @.@.TranCount to
> determine whether or not to RollBack...
> This pattern allows you to put common
> error handler at the end of all your SPs,
> It Therefore keeps the code in the body
> of the SP "Cleaner", making it easier to
> read, and to see the SP's business structure
> business process which you are implementing
> ********************************/
>
> "Mihaly" wrote:
>

Managing Stored Procedures / Functions for releases.

WE release our software once a week. In 1 months time we will have over 500 stored procedures in our DataBase.

What we do for releases is when a stored procedure is changed, we put the Drop and Create parts of that script in our SQL Update Script.

A problem comes up when Developer A changes My_StoredProc and then developer B changes the same stored procedure. Sometimes it works ok (the developer B will run the update script before changing his stored procedure. HOwever, it can happen where one Update script file has the same SP 5 times (5 drops 5 creates)... especially if over 300 SP's are getting updating in 1 release.

We will always catch these on our tests, however, it's the 2 hours to fix the Test DB after we run these tests...

What is the best way to manage these? We thought about putting our stored procedures into Team Foundation Server, but we don't know if that will work for us.

We have 8 developers in our team.

If anyone could help or give advice on this, it would be awesome.

Thanks.

You need some form of source control system. If you have Visual Source Safe then put the DROP/CREATE scrips in it and before any developer changes an SP they make sure it isn't checked out. They check it out, do some work, then check the new update script in.

If you are all using different databases the developer should run the script that is in Source Safe against their database before making changes to ensure they are working on the latest version.

|||

Thanks, That is currently what we're switching to.

I wanted to know if there's anything better we should be doing :D

now we need to write a file to throw them all together.

Managing Insert with Identity

Hi,
I am new at stored procedures.
I am running SQL Server 2005 standard on XP Pro sp2.
I have a database that has about 100 tables. For example I have a table
called 'People' with a primary key called 'PeopleID', which is an int and
also of column type 'Identity'. This 'People' table also has a
'PeopleAttributesID' column, also of int nature, and relates to a table name
d
'PeopleAttributes' of which the primary key and Identity column is name
'PeopleAttributesID'. PeopleAttributes table also has a column named
'PeopleInterfaceListID' of int that also relates to a table called
PeopleInterfaceList that has a primary key, int, identity, called
PeopleInterfaceListID.
You get the idea. :-)
My question is how to create a stored procedure for inserting new people in
the 'People' table. My issues is dealing with the identity columns of the
'People' table and it's related tables.
How do I add a row into a table that has an identity primary key and has
foreign keys into tables that also have identity primary keys. Can you give
me an example of how this should be done in your opinion?
Thank you very, very much :)
Antoine Dubuc
MSN Messenger : banquo_ws@.hotmail.com
514-761-1832Antoine wrote:
> Hi,
> I am new at stored procedures.
> I am running SQL Server 2005 standard on XP Pro sp2.
> I have a database that has about 100 tables. For example I have a
> table called 'People' with a primary key called 'PeopleID', which is
> an int and also of column type 'Identity'. This 'People' table also
> has a 'PeopleAttributesID' column, also of int nature, and relates to
> a table named 'PeopleAttributes' of which the primary key and
> Identity column is name 'PeopleAttributesID'. PeopleAttributes table
> also has a column named 'PeopleInterfaceListID' of int that also
> relates to a table called PeopleInterfaceList that has a primary key,
> int, identity, called PeopleInterfaceListID.
> You get the idea. :-)
> My question is how to create a stored procedure for inserting new
> people in the 'People' table. My issues is dealing with the identity
> columns of the 'People' table and it's related tables.
> How do I add a row into a table that has an identity primary key and
> has foreign keys into tables that also have identity primary keys.
> Can you give me an example of how this should be done in your opinion?
> Thank you very, very much :)
> Antoine Dubuc
> MSN Messenger : banquo_ws@.hotmail.com
> 514-761-1832
Youinsert by leaving off the identity column. Once the insert is complete,
you can get the new value using the SCOPE_IDENTITY() function. if you need
to insert FK values, you need to pass them into the stored procedure and use
them in the insert. You'll need to get those values from your application
first.
David Gugick
Quest Software|||Hi David,
When I try to execute this, I get the cannot leave null for this column erro
r.
DI have to manually insert it using something like:
DECLARE PeopleID INT
PeopleID = SCOPE_IDENTITY
...
Exactly how do you do this?
thank you,
Antoine
"David Gugick" wrote:

> Antoine wrote:
> Youinsert by leaving off the identity column. Once the insert is complete,
> you can get the new value using the SCOPE_IDENTITY() function. if you need
> to insert FK values, you need to pass them into the stored procedure and u
se
> them in the insert. You'll need to get those values from your application
> first.
> --
> David Gugick
> Quest Software
>
>|||> When I try to execute this, I get the cannot leave null for this column
> error.
> DI have to manually insert it using something like:
> DECLARE PeopleID INT
> PeopleID = SCOPE_IDENTITY
Did you mean:
DECLARE @.PeopleID INT
... INSERT statement here
SET @.PeopleID = SCOPE_IDENTITY()
?|||>> have a database that has about 100 tables. For example I have a table ca
lled 'People' with a primary key called 'PeopleID', which is an int and also
of column type 'Identity'. This 'People' table also has a 'PeopleAttribute
sID' column, also of int na
ture, and relates to a table named 'PeopleAttributes' of which the primary k
ey and Identity column is named 'PeopleAttributesID'. PeopleAttributes table
also has a column named 'PeopleInterfaceListID' of int that also relates to
a table called PeopleInter
faceList that has a primary key, int, identity, called PeopleInterfaceListID
. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
However, these data element names and the improper use of IDENTITY as a
key imply that you have never written an RDBMS before. IDENTITY is not
a key by definition and you are mixing data and metadata. It looks
like the typhical newbie disaster with IDENTITY columns used as pointer
chains to mimic a 1970's IDMS database. Please get help instead of
newsgroup kludges.|||In order for a column with the IDENTITY 'property' to become a key then you
need only make it such using a CONSTRAINT, either PRIMARY KEY where no
natural key exists or UNIQUE if you are making it an artificial or surrogate
key.
Get a grip and be more constructive, the poster said his level of experience
was beginner so be polite and don't rant your rude rubbish.
There are many objects in the world that do not have a natural key, my
favourite is the message board example.
It's quite concievable that the author wants to create an auto generated
number and use that as a key, afterall, its becoming very dodgy to say the
least in the US using social security numbers and capturing that info on a
website, well - would you honestly give over your national insurance number
(uk equiv) to a company - nope.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132258326.365651.286720@.g14g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> However, these data element names and the improper use of IDENTITY as a
> key imply that you have never written an RDBMS before. IDENTITY is not
> a key by definition and you are mixing data and metadata. It looks
> like the typhical newbie disaster with IDENTITY columns used as pointer
> chains to mimic a 1970's IDMS database. Please get help instead of
> newsgroup kludges.
>

Managing Excel execution through DTS

We have a convoluted DTS package. The package is stored and scheduled to run on database server1. Within the package we:
Make a connection to database server2
Execute a Process Task to execute EXCEL.EXE that is installed on server3
Pass parameters through the Process Task direct EXCEL.EXE to open a .xls file on server4
When the Excel file opens, an auto_exec macro in the Excel file being opened imports a text file local to server4 AND directs Excel to save it with another name on server4.

The questions are:
1. Excel is not installed on server1 so how do we direct Excel to execute on server3 rather than server1 where the DTS package is being executed?

2. And how do we control the security context that executes Excel through this use of automation?

3. Other than potential CPU competition, are there any significant issues with having Excel installed on a dedicated database server?Yikes! That is convoluted. Is there any particular reason you are doing things this way?|||Dude -

Are you into masochism? Enjoy sleepless nights?

Seriously - just pull the excel spreadsheet from the server you are reading from, open it on the machine with the dts package, do the manipulation & then copy the final result to wherever its going....

Dont make it any more complicated than it has to be - K.I.S.S.

Simple = Robust.

Cheers,|||Why do you have 4 servers in this scenario when you just want to modify some data on another server than where the DTS-package is situated?

What kind of data does this file contain, and why does it have to be updated via Excel?

Managing concurrency in Stored Proc with Timestamp

Hello all,

I am trying to find the right way to get the correct error reported in a Stored Proc where the table contains a timestamp column. I gather the TSEqual is not the way to go (I'm using SQL Express, which uses Server 2005 syntax and TSEqual is deprecated).

Within an update procedure, given an ID, a TimeStamp, and a new value, I would like to specifically know if the update fails because the record was not found or because the timestamp indicated that the record was previously updated.

I can't figure out how to specifically raise the 532 error if the timestamps don't match, since I can't use RAISERROR.

Any thoughts would be much appreciated

Regards,

Flavelle

Will this do?

Since you are passing the Time stamp to the procecure follow these steps:

Exeucte an Update Statement and check the valuf of @.@.rowcount after the update statement. If the value is 0 execute a SELECT statement to check if the value exists for the given ID, once again check the value of rowcount if this is 1 then the daa was not updated because somebody else updated the record If the value is 0 then there exists no records.

Sample Code:

DECLARE @.TS TIMESTAMP

SELECT @.TS=@.@.DBTS

DECLARE @.CustomerID INT

SET @.CustomerID=9

UPDATE Customers

SET CustomerName='Nith'

WHERE CustomerID=@.CustomerID AND LastUpdate=@.@.DBTS

-- Instead of @.@.DBTS use your datetime value

IF @.@.ROWCOUNT=0

BEGIN

SELECT CustomerName FROM Customers WHERE CustomerID=@.CustomerID

IF @.@.ROWCOUNT=0

BEGIN

PRINT 'No Record Present for CustomerID'

END

ELSE

PRINT 'Some body else updated the data'

END

ELSE

PRINT 'Data Updated'

|||

An elegant and simple solution - many thanks. Now if only I could raise the 532 error directly using RAISERRROR - but it looks like it is going the way of the Dodo bird. Pity.

Regards,

Flavelle

|||

I think this is a good example for handling concurrency in stored procedure.

Does the sql server 2000 that contains some variables in order to indicate which records had been updated?

or any other method to do the same thing but did not use any programming or extra column in the tables?

Please kindly advice.

Thanks & regards,

Clara

Managing concurrency in Stored Proc with Timestamp

Hello all,

I am trying to find the right way to get the correct error reported in a Stored Proc where the table contains a timestamp column. I gather the TSEqual is not the way to go (I'm using SQL Express, which uses Server 2005 syntax and TSEqual is deprecated).

Within an update procedure, given an ID, a TimeStamp, and a new value, I would like to specifically know if the update fails because the record was not found or because the timestamp indicated that the record was previously updated.

I can't figure out how to specifically raise the 532 error if the timestamps don't match, since I can't use RAISERROR.

Any thoughts would be much appreciated

Regards,

Flavelle

Will this do?

Since you are passing the Time stamp to the procecure follow these steps:

Exeucte an Update Statement and check the valuf of @.@.rowcount after the update statement. If the value is 0 execute a SELECT statement to check if the value exists for the given ID, once again check the value of rowcount if this is 1 then the daa was not updated because somebody else updated the record If the value is 0 then there exists no records.

Sample Code:

DECLARE @.TS TIMESTAMP

SELECT @.TS=@.@.DBTS

DECLARE @.CustomerID INT

SET @.CustomerID=9

UPDATE Customers

SET CustomerName='Nith'

WHERE CustomerID=@.CustomerID AND LastUpdate=@.@.DBTS

-- Instead of @.@.DBTS use your datetime value

IF @.@.ROWCOUNT=0

BEGIN

SELECT CustomerName FROM Customers WHERE CustomerID=@.CustomerID

IF @.@.ROWCOUNT=0

BEGIN

PRINT 'No Record Present for CustomerID'

END

ELSE

PRINT 'Some body else updated the data'

END

ELSE

PRINT 'Data Updated'

|||

An elegant and simple solution - many thanks. Now if only I could raise the 532 error directly using RAISERRROR - but it looks like it is going the way of the Dodo bird. Pity.

Regards,

Flavelle

|||

I think this is a good example for handling concurrency in stored procedure.

Does the sql server 2000 that contains some variables in order to indicate which records had been updated?

or any other method to do the same thing but did not use any programming or extra column in the tables?

Please kindly advice.

Thanks & regards,

Clara

Management Studio strange behaviour?

Under Management Studio, when I right-click a stored proc, select Modify, change the stored proc, then select Execute, the stored proc is updated on the server. But then I noticed that the sql tab holding the changed stored proc (in the right pane of Management Studio) still diplays and asterisk (*). When I right-clicked the tab it offeres the Save option but that is to save the sql file (with the * in its tab) to a file. This is confusing behaviour.

Is there any way to change this behaviour so running Execute causes the (*) to disapeear?

TIA,

Barkingdog

The asterisk means the script file you are modifying is not saved to disk. Executing the script/file does not save the file and that is why the asterisk does not disappear.

You have to consider the actual procedure in the database as not being the script/file in management studio that creates/updates it.

Execute does not save the file as it only communicates with the connected server. Save does not update the connected server as it only saves the script to disk.

Management Studio Sluggish

We have noticed that quite frequently, SQL Server Management Studio is somewhat sluggish with expanding lists for tables, opening stored procedures, opening files, etc.

Executing queries seems to be very quick though.

The machine that has SQL 2005 is 2.8 GHz with 512 MB of RAM.

Any idea why SQL Server Management Studio would seem somewhat sluggish?

That is really not very much memory for SQL Server (and the OS). I recommend adding additional memory and comparing the times -I think you will be pleased with the effect.|||Thanks. Got the order from the boss to bump it up to 2.5 GIG ram. I'll let you know how it performs after the upgrade.