Help on Script Component assignment of output variable

Hi all,

Actually I′m working with the beta 2 of the Sql Server 2005 with SSIS. And it′s great fun! But I′m now experience a problem:

I′m working with a script component. In that script component I would like to assign a value to a specific column ("Row.Formula"). The type of "Row.Formula" is Unicode text stream [DT_NTEXT]. Whenever I try to assign a byte array or a string to that field I am getting a compliation error. I have to assign a variable with the type "blobcolumn". But I cannot initialize this variable because "blobcolumn" has no public constructor.

Any thoughts on that?

The code sample:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Avanade.AMCS.DataProcessorReplacement.Utility

Public Class ScriptMain Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim wholeRowBuffer As Byte()
Dim columns() As String

wholeRowBuffer = Row.WholeLine.GetBlobData(0, CInt(Row.WholeLine.Length))

columns = ScriptComponentHelper.RetrieveStringArrayOutOfBuffer(wholeRowBuffer)

Row.Name = columns(1)
Row.MenuName = columns(2)
Row.CascadeName = columns(3)

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

'Conversion compliation error - cannot convert from byte array to 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn'

Row.Formula = buffer

End Sub

End Class

To access BLOB data in Script component, please use AddBlobData and GetBlobData.

For example:

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

Row.Formula.AddBlobData(buffer)

|||

---

Imports System
Imports System.Xml
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
.CoverNote = Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

The above scripting which highlightted wtih red color was encounter error.
The error msg : Expression doed not produce value.
Note: sources column for CoverNote is text data type which contain a lot multivalue.
example 001^002^003^004^005

Anyonce know how to write the correct scripting for handle such column with text data type.I dont have any idea for this case with text data type.

Thanks in advance.

|||

The line should just read

Row.CoverNote.AddBlobData(...

So remove the assignment part of the highlighted statement; AddBlobData() returns nothing.

|||Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

Thanks jaegd.

I had try to modify the code as per suggested.

Other columns output were fine and ok, just CoverNote result output was NULL value.

Any other script I miss out and need to add in or modified to able support this CoverNOte with text data type?

Appreciate for any help.

|||

What is the pipeline data type of the input column Row.CoverNote -- DT_TEXT,DT_NEXT, or something else?

|||The pipeline data type of the input column Row.CoverNote is DT_TEXT|||

Since the source pipeline type is DT_TEXT , before you can meaningfully called Split() on the character data contained in the blob, convert the pipeline data type to a .NET String first. Retrieval of DT_TEXT data is performed by calling GetBlobData() on the named/typed accessor and then decoding the array of bytes returned.

In other words, replace the line

CoverNote = Split(Row.CoverNote.ToString, "^")

with

Dim blobLength As Int32 = Convert.ToInt32(Row.CoverNote.Length)

Dim blobData() As Byte = Row.CoverNote.GetBlobData(0, blobLength)

Dim blobCodePage As Int32 = Row.CoverNote.ColumnInfo.CodePage

Dim joinedCoverNote As String = Text.Encoding.GetEncoding(blobCodePage).GetString(blobData)

CoverNote = Split(joinedCoverNote, "^")

|||

I had tried the sripting as per suggested.

The problems had been solved.

Thanks for your helping.

Help on Script Component assignment of output variable

Hi all,

Actually I′m working with the beta 2 of the Sql Server 2005 with SSIS. And it′s great fun! But I′m now experience a problem:

I′m working with a script component. In that script component I would like to assign a value to a specific column ("Row.Formula"). The type of "Row.Formula" is Unicode text stream [DT_NTEXT]. Whenever I try to assign a byte array or a string to that field I am getting a compliation error. I have to assign a variable with the type "blobcolumn". But I cannot initialize this variable because "blobcolumn" has no public constructor.

Any thoughts on that?

The code sample:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Avanade.AMCS.DataProcessorReplacement.Utility

Public Class ScriptMain Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim wholeRowBuffer As Byte()
Dim columns() As String

wholeRowBuffer = Row.WholeLine.GetBlobData(0, CInt(Row.WholeLine.Length))

columns = ScriptComponentHelper.RetrieveStringArrayOutOfBuffer(wholeRowBuffer)

Row.Name = columns(1)
Row.MenuName = columns(2)
Row.CascadeName = columns(3)

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

'Conversion compliation error - cannot convert from byte array to 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn'

Row.Formula = buffer

End Sub

End Class

To access BLOB data in Script component, please use AddBlobData and GetBlobData.

For example:

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

Row.Formula.AddBlobData(buffer)

|||

---

Imports System
Imports System.Xml
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
.CoverNote = Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

The above scripting which highlightted wtih red color was encounter error.
The error msg : Expression doed not produce value.
Note: sources column for CoverNote is text data type which contain a lot multivalue.
example 001^002^003^004^005

Anyonce know how to write the correct scripting for handle such column with text data type.I dont have any idea for this case with text data type.

Thanks in advance.

|||

The line should just read

Row.CoverNote.AddBlobData(...

So remove the assignment part of the highlighted statement; AddBlobData() returns nothing.

|||Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

Thanks jaegd.

I had try to modify the code as per suggested.

Other columns output were fine and ok, just CoverNote result output was NULL value.

Any other script I miss out and need to add in or modified to able support this CoverNOte with text data type?

Appreciate for any help.

|||

What is the pipeline data type of the input column Row.CoverNote -- DT_TEXT,DT_NEXT, or something else?

|||The pipeline data type of the input column Row.CoverNote is DT_TEXT|||

Since the source pipeline type is DT_TEXT , before you can meaningfully called Split() on the character data contained in the blob, convert the pipeline data type to a .NET String first. Retrieval of DT_TEXT data is performed by calling GetBlobData() on the named/typed accessor and then decoding the array of bytes returned.

In other words, replace the line

CoverNote = Split(Row.CoverNote.ToString, "^")

with

Dim blobLength As Int32 = Convert.ToInt32(Row.CoverNote.Length)

Dim blobData() As Byte = Row.CoverNote.GetBlobData(0, blobLength)

Dim blobCodePage As Int32 = Row.CoverNote.ColumnInfo.CodePage

Dim joinedCoverNote As String = Text.Encoding.GetEncoding(blobCodePage).GetString(blobData)

CoverNote = Split(joinedCoverNote, "^")

|||

I had tried the sripting as per suggested.

The problems had been solved.

Thanks for your helping.

Help on Script Component assignment of output variable

Hi all,

Actually I′m working with the beta 2 of the Sql Server 2005 with SSIS. And it′s great fun! But I′m now experience a problem:

I′m working with a script component. In that script component I would like to assign a value to a specific column ("Row.Formula"). The type of "Row.Formula" is Unicode text stream [DT_NTEXT]. Whenever I try to assign a byte array or a string to that field I am getting a compliation error. I have to assign a variable with the type "blobcolumn". But I cannot initialize this variable because "blobcolumn" has no public constructor.

Any thoughts on that?

The code sample:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Avanade.AMCS.DataProcessorReplacement.Utility

Public Class ScriptMain Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim wholeRowBuffer As Byte()
Dim columns() As String

wholeRowBuffer = Row.WholeLine.GetBlobData(0, CInt(Row.WholeLine.Length))

columns = ScriptComponentHelper.RetrieveStringArrayOutOfBuffer(wholeRowBuffer)

Row.Name = columns(1)
Row.MenuName = columns(2)
Row.CascadeName = columns(3)

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

'Conversion compliation error - cannot convert from byte array to 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn'

Row.Formula = buffer

End Sub

End Class

To access BLOB data in Script component, please use AddBlobData and GetBlobData.

For example:

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

Row.Formula.AddBlobData(buffer)

|||

---

Imports System
Imports System.Xml
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
.CoverNote = Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

The above scripting which highlightted wtih red color was encounter error.
The error msg : Expression doed not produce value.
Note: sources column for CoverNote is text data type which contain a lot multivalue.
example 001^002^003^004^005

Anyonce know how to write the correct scripting for handle such column with text data type.I dont have any idea for this case with text data type.

Thanks in advance.

|||

The line should just read

Row.CoverNote.AddBlobData(...

So remove the assignment part of the highlighted statement; AddBlobData() returns nothing.

|||Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

Thanks jaegd.

I had try to modify the code as per suggested.

Other columns output were fine and ok, just CoverNote result output was NULL value.

Any other script I miss out and need to add in or modified to able support this CoverNOte with text data type?

Appreciate for any help.

|||

What is the pipeline data type of the input column Row.CoverNote -- DT_TEXT,DT_NEXT, or something else?

|||The pipeline data type of the input column Row.CoverNote is DT_TEXT|||

Since the source pipeline type is DT_TEXT , before you can meaningfully called Split() on the character data contained in the blob, convert the pipeline data type to a .NET String first. Retrieval of DT_TEXT data is performed by calling GetBlobData() on the named/typed accessor and then decoding the array of bytes returned.

In other words, replace the line

CoverNote = Split(Row.CoverNote.ToString, "^")

with

Dim blobLength As Int32 = Convert.ToInt32(Row.CoverNote.Length)

Dim blobData() As Byte = Row.CoverNote.GetBlobData(0, blobLength)

Dim blobCodePage As Int32 = Row.CoverNote.ColumnInfo.CodePage

Dim joinedCoverNote As String = Text.Encoding.GetEncoding(blobCodePage).GetString(blobData)

CoverNote = Split(joinedCoverNote, "^")

|||

I had tried the sripting as per suggested.

The problems had been solved.

Thanks for your helping.

sql

help on join statement

I have two tables:

tblUserData
UserName
UserCode

tblBlogs
UserCode
BlogText

I have an SP which takes the username as a variable.

How can I select all blogtext from tblBlogs where the usercode belonging to the username in tblUserdata is equal to the usercode in tblBlogs?

so select all blogs for a specfic username...

SELECT TB.* FROM tblBlogs TB

JOIN tblUserData TUD ON TB.Usercode = TUD.UserCode

WHERE TUD.UserName = @.UserName

Help on global variable for insert statement

I am new to DTS, but really enjoy it and was wondering if someone could help me with the following small vb app.

I am using the following DTS insert statement to insert records into my table. I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls. How can I modify my code to insert those textboxes as well as run through the boxes and then check if they have nulls and NOT insert the ones that has nulls?

My form has 9 textboxes. Textbox1, 2, 3 Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column.

My question is how do I format the following line of code to do what I need?

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"

I think it is something like this, but I am Really not sure and some help would be greatly appretiated:

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')"

I am really not sure.

Here is all the code:

Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
'Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.ExecuteSQLTask2
oTask = CType(goPackage, DTS.Package).Tasks.New("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1.Description = "Execute SQL Task: undefined"
oCustomTask1.SQLStatement = "Insert into TestTable (Test1, Test2, Test3) " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"
oCustomTask1.ConnectionID = 1
oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False

goPackage.Tasks.Add(oTask)
oCustomTask1 = Nothing
oTask = Nothing

End Sub

This sentence is confusing: "I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls."

Do you mean that the table fields do not accept NULL values?

If I recall correctly, a textbox cannot contain a NULL value. At the minimum, it contains an 'empty string'.

In your code sniplet, you are inserting the value '1rowst' in the field [Test1], etc. There is no ambiguity about NULL values.

Perhaps I am missing something. Could you please expand upon your request so in order to clear up the confusion?

|||

I am sorry, lack of english.

My form has 9 textboxes. Textbox1, 2, 3 Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column.

My question is how do I format the following line of code do do what I need?

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"

I think it is something like this, but I am Really not sure and some help would be greatly appretiated:

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')"

I am really not sure.

Thanks

|||

I'm still confused. Please help me clarify.

You wish to put all the contents of three textboxes into a single field in the table?

"Textbox1, 2, 3 Needs to insert values into the first column"

If that is the situation, why not just have one textbox -not three?

|||

No, sorry Arnie,

Textbox 1,4,7 go in column 1

TextBox 2, 5, 8 Go in column 2

TextBox 3, 6, 9 Go in column 3

I need my application to insert the values into the Database. If there is Nulls in the textbox go to the next record.

I think the code should go something like this:

If Textbox2 = '' then

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox1.Text "', '" Textbox4.Text "', '" Textbox7.Text "')"

Elseif Textbox3 = '' then

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox1.Text "', '" Textbox4.Text "', '" Textbox7.Text "')"

And

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox2.Text "', '" Textbox5.Text "', '" Textbox8.Text"')"

As you can see I am throwing a rock into the grass, because I am not % 100 sure if this is correct.

Thanks for the patience Arnie

|||

If I am understanding you correctly, it appears that you wish to load textbox values into the table depending upon the status of other textbox values.

Your use of the IF...ELSEIF structure may work just fine for your intentions. Something like this might work.

IF Textbox1.Text = '' Then

IF Textbox2.Text = '' Then

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" + Textbox3.Text + "', '" + Textbox6.Text + "', '" + Textbox9.Text + "')"

ELSE

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox2.Text + "', '" + Textbox5.Text + "', '" + Textbox8.Text + "')"

END IF

ELSE

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" + Textbox1.Text + "', '" + Textbox4.Text + "', '" + Textbox7.Text + "')"

END IF

|||That was exactly what I needed. Thanks Arnie|||Of course you should be careful with this method since this is completely vulnerable to SQL injection attacks.

Just a friendly reminder.|||

Jon,

Thank you very much for that. I was not familiar with that at all. I just want to make sure. This is a big issue with web applications (wich mine is not). Is that correct?

Thanks

|||Yes it is, but it's completely possible from within windows forms applications as well. So it's definitely something to watch out for.

Help on creating a user function.

When I declare a cursor,I use a variable to replace the sql statement:
DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
@.sqlPlan
But it is not true.Who can correct for me.

Another question is :
How to execute a sql statement state by a variable "@.sqlPlan" and
insert the result to a table "@.FeatRequestStatus"?

I am a new hand of sql programming.Thank you very much for your helpWhen I use:
insert @.FeatRequestStatus
exec @.sqlPlan
It says "execute can be used as a source when insert into a table viarable"
"Kevin" <hua@.lucent.com> wrote in message
news:dc2mgs$16f@.netnews.proxy.lucent.com...
> When I declare a cursor,I use a variable to replace the sql statement:
> DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
> @.sqlPlan
> But it is not true.Who can correct for me.
> Another question is :
> How to execute a sql statement state by a variable "@.sqlPlan" and
> insert the result to a table "@.FeatRequestStatus"?
> I am a new hand of sql programming.Thank you very much for your help|||Kevin (hua@.lucent.com) writes:
> When I declare a cursor,I use a variable to replace the sql statement:
> DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
> @.sqlPlan
> But it is not true.Who can correct for me.

You need to say:

EXEC ('DECLARE rs CURSOR GLOBAL FAST_FORWARD ' + @.sqlPlan)

Note that I changed LOCAL to GLOBAL here. This is necessary, since the
cursor is accessed from a different scope than it is created.

> Another question is :
> How to execute a sql statement state by a variable "@.sqlPlan" and
> insert the result to a table "@.FeatRequestStatus"?

INSERT EXEC does not work with table variables, as you have experienced.
Use a temp table instead.

And if @.sqlPlan is an SQL statement, the syntax is

EXEC(@.sqlPlan)

The syntax you had on your other post:

EXEC @.sqlPlan

means "execute the stored procedure of which the name is in @.sqlPlan".

> I am a new hand of sql programming.Thank you very much for your help

In such case, I should maybe point out, that cursors is something
to be used sparingly. There are situations where cursors can be
motivated, but they often come with a price of severly reduced
performance. Work set-based if you can.

Dynamic SQL is not really anything for beginners - it's definitely an
advanced feature. Dynamic SQL makes things a lot more complex, and
avoid if you can. I have a longer article on dynamic SQL on my web
site that you could find useful:
http://www.sommarskog.se/dynamic_sql.html

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

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