Marking copied records

I have a big problem now. I need to write a SQL statement to copy some
records from a table to another table. At the process of copying, I want to
update a field in both table. The field is to identify whether the record is
the 1st, 2nd, 3rd, 4th, ... record that I've copied, which will be in runnin
g
sequence. There should be repeating numbers. The reason for doing this is if
a user modifies a record in the new table in the future, I will still know
how it originally was by referring back by that number.
Do you get what I mean? I have no idea whether SQL can do that. And whether
it can be settled in a statement. Can someone help me? Give me some guide?
Thank you.You could do this by adding a DateCopied (datetime -default getdate() )
column to the tables -perhaps even adding a WhoChanged (varchar(50) -default
system_user) column.
Other options include a Sequence (timeStamp datatype) Column.
Either of these choices would allow you to always restructure the sequence
of data changes.
Then you just add a TRIGGER to the primary table to copy the old version to
the archive table whenever there is a data change.
You might google "SQL Server" and "Audit Trail". Here's an article to get
you started:
http://expertanswercenter.techtarge...i980058,00.html
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:8D4B4729-8170-436E-BE05-D87933758E59@.microsoft.com...
>I have a big problem now. I need to write a SQL statement to copy some
> records from a table to another table. At the process of copying, I want
> to
> update a field in both table. The field is to identify whether the record
> is
> the 1st, 2nd, 3rd, 4th, ... record that I've copied, which will be in
> running
> sequence. There should be repeating numbers. The reason for doing this is
> if
> a user modifies a record in the new table in the future, I will still know
> how it originally was by referring back by that number.
> Do you get what I mean? I have no idea whether SQL can do that. And
> whether
> it can be settled in a statement. Can someone help me? Give me some guide?
> Thank you.

Mapping Output Parameter to a variable!

Hi there,

I am working on SSIS package that gets data from SQL 2005 Database and writes that to a flat file. But I need to write the count of records as part of the header.

Here is what i am trying:

The OLE DB Source is calling a stored procedure and returning two things i.e. a resultset and an output parameter. The data access mode is SQL Command.

Code Snippet

EXEC [Get_logins] ?, ?, ? OUTPUT
In the Set Query Parameters dialogbox, all the three patameters are mapped to three different user variables.

What is happening is that the user variable that is mapped to output parameter is never updated. The header property expression is written as follows

Code Snippet

RIGHT("0000000000" + (DT_STR, 10, 1252)@.LoginCount, 10)

I tried to watch the variable in watch window but to no avail. Any guidance if it is bug or I am missing some thing? Any thoughts, how can I accomplish this? I have also tried adding Row Count Transformation but its variable has the same behaviour. If I set the value of @.LoginCount variable to some value, this initially set value is successfully written to the file header.

Thanks

Paraclete

No bug, the OLE DB Source just doesn't support output parameters from stored procedures. The Execute SQL Task does, though. You could execute that one in your control flow and put your resultset in a variable. A script source component can shred the resultset into rows in your Data Flow.

Or you could issue two queries. One to count the rows and put that value in a variable in the Control Flow, and then another one in the Data Flow to produce the rows.
|||

Hi,

Thanks for your response. Yes I can calculate the number of rows in a separate query, but some of the rows may have bad data. So in this case these rows will be ignored or sent to error output i.e. will not be written to the Flat File Destination. So the count taken in a separate query will be incorrect i.e. CountATStart-Errors not the CountAtstart. This may create problem becuase the header has count of records in the file. Any guidance/thoughts are wellcomed.

Thanks,

Paraclete

|||You were probably on the right track with the Row Count transformation, but it won't write to the variable until all the rows have been recieved, at which point you've already written your header. Try putting a Sort component after the Row Count. This will queue up the rows between the Row Count and the Destination and should allow Row Count to set the variable before the header gets created. By the way, how are you writing the header?

Many to one relationship

Hello

I have need to write a query that I can pass in a bunch of filter criteria, and return 1 result...it's just ALL of the criteria must be matched and a row returned:

example:

Transaction table: id, reference

attribute table: attributeid, attribute

transactionAttribute: attributeid, transactionid

Example dat

Attribute table contains: 1 Red, 2 Blue, 3 Green

Transaction table contains: 1 one, 2 two, 3 three

transactionAttribute contains: (1,1), (1,2), (1,3), (2,3), (3,1)

If I pass in Red, Blue, Green - I need to be returned "one" only

If I pass in Red - I need to be returned "three" only

If I pass in Red, Green - nothing should be returned as it doesn't EXACTLY match the filter criteria

If anyone's able to help that would be wonderful!

Thanks, Paul

Hi Paul,

To get the result, you cannot pass the Attribute strings directly, you have to seperate them with commas. Each one is quoted with single quotes.

Here is an example for

SELECT * FROM TransactionAttribute
LEFT OUTER JOIN AttributeTable ON TransactionAttribute.AttributeId = AttributeTable.AttributeId
LEFT OUTER JOIN TransactionTable ON TransactionAttribute.TransactionId=TransactionTable.TransactionID
WHERE Attribute IN ('Red', 'Green')

HTH.

sql

Many left joins and slow performance

I am combining several columns and tables and I am wondering if there is a way to improve performance, for instance can I write an implicit join version of the following code, rather than having these explicit joins?

objCmd = new OleDbCommand ("SELECT MAmunicipalities.*, MAcitytown.*, countiesalias1.countyname, countiesalias1.countylinktitle AS clink1, countiesalias2.countyname, countiesalias2.countylinktitle AS clink2, countiesalias3.countyname, countiesalias3.countylinktitle AS clink3 FROM (MAmunicipalities LEFT OUTER JOIN MAcitytown ON MAmunicipalities.citytown = MAcitytown.citytown) LEFT OUTER JOIN MAcounties AS countiesalias1 ON MAcitytown.county1 = countiesalias1.countyname LEFT OUTER JOIN MAcounties AS countiesalias2 ON MAcitytown.county2 = countiesalias2.countyname LEFT OUTER JOIN MAcounties AS countiesalias3 ON MAcitytown.county3 = countiesalias3.countyname WHERE MAmunicipalities.municipality='plymouth'", objConn);

Sadly, I have even more joins left to add as well as other columns to add to the SELECT statement and more aliases to add as well. Any thoughts would be greatly appreciated.

So far, this appears to be a five table JOIN. That in itself 'shouldn't be a preformance issue.

Judicious usage of TABLE aliases would make the code a bit more readible (and maintainable).

And it is widely considered a 'best practice' to specifically identify columns instead of using [ SELECT * ].

For Example:

Code Snippet

SELECT
m.*,
c.*,
c1.CountyName,
c1.CountyLinkTitle AS cLink1,
c2.CountyName,
c2.CountyLinkTitle AS cLink2,
c3.CountyName,
c3.CountyLinkTitle AS cLink3
FROM MaMunicipalities m
LEFT JOIN MaCityTown c
ON m.CityTown = c.CityTown
LEFT JOIN MaCounties c1
ON c.County1 = c1.CountyName
LEFT JOIN MaCounties c2
ON c.County2 = c2.CountyName
LEFT JOIN MaCounties c3
ON c.County3 = c3.CountyName
WHERE m.Municipality = 'plymouth'

Are there additional tables to JOIN, or is it additional JOINs to the same tables (like with MaCounties above)?

|||I have additional tables to join. Right now it is taking between 10 and 15 seconds for a page to load using the code that is posted. I have indexed columns within those tables which has helped somewhat but it is still taking way too long. I suspect that having varchars being joined instead of ints is also contributing to the problem. Would a stored procedure help?|||

A stored procedure may help some -but its doubtful that it would provide the kind of improvement you really need.

As you suspect, the major issue is most likely the varchar() fields used for the JOINs.

One of the most significant things that help with speed on JOINs is the 'width' of an index. An integer field is 4 bytes. A varchar() field is as many bytes as characters (nvarchar() is double that.) The 'wider' the index, the fewer entries on an index page, therefore the more pages that have to be 'crawled' through and read to find the data.

Ideally, your CityTown and CountyName values would be integer values (CityTownID and CountyNameID).

I suggest that you explore using the Database Tuning Advisor to get assistance in fine tuning the indexes.

Refer to Books Online, Topic: 'Database Engine Tuning Advisor'

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.

Managing DB updates to client DB servers

I'm looking for ideas on how to write SQL scripts for updates that are
pushed out to clients for product updates. Obviously, We could just
keep track of the changes on a pad or write a database that requires
us to input those changes and eventually hand write the update
scripts. I was wondering if anybody has any solutions that may help
automate this process.

Is there a way to write an application that will compare a current
(updated) database structure against the last realease that will give
us the fields that need to be changed?

As far as creating the scripts for the initial install, thats easy. We
can do that right from the SQL Enterprise Manager.

Call me lazy! Any ideas?

ThanksDuncan (duncan.loxton@.gmail.com) writes:
> I'm looking for ideas on how to write SQL scripts for updates that are
> pushed out to clients for product updates. Obviously, We could just
> keep track of the changes on a pad or write a database that requires
> us to input those changes and eventually hand write the update
> scripts. I was wondering if anybody has any solutions that may help
> automate this process.
> Is there a way to write an application that will compare a current
> (updated) database structure against the last realease that will give
> us the fields that need to be changed?
> As far as creating the scripts for the initial install, thats easy. We
> can do that right from the SQL Enterprise Manager.

There a couple of products on the market. SQLCompare from Red Gate does
indeed compare two databases. DBGhost likes to tout itself as being
good for this. I have not use any of them.

Whatever method, you should keep all your code under version control,
and all your update scripts should have their foundation in the
version-control system. Basically a shipment is all changes between
the label for the previous shipment and this one. With some files added,
like triggers or indexes for changed tables.

In fact, once you have a good version control system up and running,
composing your scripts manually is not daunting task - but admittedly
it becomes boring after a while. The flip side is that you learn to
understand your process.

In fact, I started in our shop with something like this many years
ago. This has now evolved to a versatile toolset that we use. It is
available as freeware for anyone who want to try, see
http://www.abaris.se/abaperls/index.html.

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

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