Asynchronus Transformation Component

Hi all,

I am missing something simple. I have added a new Transformation Script, put in my code to read the input rows, defined my outputs. I have tried to change the SynchonousInputId to 0, but I only get the option of None or input "Input 0" (91). What have I missed?

Set the SynchonousInputId to None.

The SynchonousInputId of "None" is synonymous with zero (0). The script transformation editor dropdown for the SynchonousInputId changed between service packs between saying "0" in the earlier case, to "None" in subsequent service packs. Both 0 and "None" for the SynchonousInputId property mean, "this is a async script transform".

Asynchronous Script Component

Hi--done some searching, but I am not finding exactly what I need. I am using an asynchronous script component as a lookup since my table I am looking up on requires an ODBC connection. Here is what my data looks like:

From an Excel connection:

Order Number

123

234

345

The table I want to do a lookup on has multiple rows for each order number, as well as a lot of rows that aren't in my first table:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

778 Another thing

889 Yet more stuff

etc. My desired result would be to pull all the items from table two that match on Order Number from table one. My actual results from the script I have is a single (random) row from table two for each item in table one.....So my current results look like:

Order Number Description

123 Freight

234 Freight

345 Null

And I want:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

345 Null

etc.... Here is my code, courtesy of half a dozen samples found here and elsewhere...

Code Snippet

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc

Public Class ScriptMain
Inherits UserComponent

Dim connMgr As IDTSConnectionManager90
Dim odbcConn As OdbcConnection
Dim odbcCmd As OdbcCommand
Dim odbcParam As OdbcParameter

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.JDEConnection
odbcConn = CType(connMgr.AcquireConnection(Nothing), OdbcConnection)

End Sub

Public Overrides Sub PreExecute()

odbcCmd = New OdbcCommand("SELECT F4211.SDDSC1, F4211.SDDOCO FROM DB.F4211 F4211 Where F4201.SHDOCO = ?", odbcConn)

odbcParam = New OdbcParameter("1", OdbcType.Int)
odbcCmd.Parameters.Add(odbcParam)

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim reader As Odbc.OdbcDataReader
odbcCmd.Parameters("1").Value = Row.SO
odbcCmd.ExecuteNonQuery()
reader = odbcCmd.ExecuteReader()
If reader.Read() Then

With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With

End If

reader.Close()

End Sub

Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(odbcConn)
End Sub

End Class

I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.

I have also worked out an alternate way to do this using merge join tasks...but then my datareader source goes off and fetches 300,000 rows from F4211 before my final result set of about 1200 rows. That just feels like a bad approach to me...or am I being over-cautious? I'm a newb (if you couldn't already tell)...so guidence is appreciated.

Thank you....

In a first data flow, you could load a staging table in SQL server with the contents of your ODBC source table. Then in a second data flow, you can use that staging table as the source for your lookup component. Might be a bit less work for you.|||So, to make sure I understand--add another data flow. Have it write the records from my F4211 table to a SQL table, then, in my original data flow, do a lookup on my newly created table in SQL...then, I suppose, add an Execute SQL task to blow all those records away? And, I suppose, just to be tidy about it....I could add a shrink database task to clean up afterwards.....?|||

In general and when possible it is a good idea to use staging tables to put all data pieces on the SQL Server side. Besides simplify the dataflow; it improves performance.

I think you are undeestanding Phil's sugestion pretty well; but I am not shure is I would bother with the shrink database step; if you are going to execute this process in a regular basis then you would need that space anyway.

|||Yep, you got it. Now have fun!|||

hilaryjade wrote:

I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.

So, to go back to the original question.... You want all the rows from the recordset, right? Don't you just need to change that If to a While loop?

Code Snippet

While reader.Read()

With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With

End While

|||Told ya I was a newb....Thanks so much!!! While a staging table and lookup off it is an interesting idea (and I appreciate it...) the script runs so much faster.|||

hilaryjade wrote:

Told ya I was a newb....Thanks so much!!! While a staging table and lookup off it is an interesting idea (and I appreciate it...) the script runs so much faster.

Did you test it? Can you share the timing results and row counts?

|||I haven't finished adding my full data set I need to pull to my script component yet, but after I do, I can run and time them. I did try adding and using a staging table last night--but ran into a bit of a data type mismatch roadblock on the lookup--I tried a handful of conversions to see if I could get my DT_R8 from Excel to play nicely with my Numeric from the staging table in SQL, but got a bit frustrated and went off to work on my third alternative...using merge joins (which works nicely and runs in 2.2 minutes, but 2 minutes starts feeling a bit long, you know?). At any rate, just creating the staging table took longer than the script takes (but, again, that was without my full data set)....After I get the script component complete and pulling all my data, I'll run, time, and post results. Again, thanks for all the help!|||

Well, I'm not really comparing apples to apples with this, since my script component is part of a data flow that starts with a connection to an excel file, does a lookup on a table with an odbc connection via the script component and then writes to a recordset and the data flow for the staging table concept uses a data reader to collect my records from a table with an odbc connection and then writes them to a SQL db table...

The dataflow with the script component ran in 00:06.844 and wrote 953 rows to my recordset (just writing the rows I needed, selected via script component)

The dataflow to create a staging table ran in 01:16.313 and wrote 219,155 rows to a table in a SQL db, where I could then do a lookup to grab the records I need (953 rows)

I think for this instance, where I need so few records from such a large table, it makes sense to use the asynchronous script component rather than create a staging table.

Again, thanks to all for the help and suggestions. I really appreciate it.

|||Did you use the Fast Load option in the OLE DB Destination when using the staging table approach?|||Yes, on the Connection Manager page for the destination editor, Data access mode is set to Table or View - fast load.

Asynchronous Script Component

Hi--done some searching, but I am not finding exactly what I need. I am using an asynchronous script component as a lookup since my table I am looking up on requires an ODBC connection. Here is what my data looks like:

From an Excel connection:

Order Number

123

234

345

The table I want to do a lookup on has multiple rows for each order number, as well as a lot of rows that aren't in my first table:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

778 Another thing

889 Yet more stuff

etc. My desired result would be to pull all the items from table two that match on Order Number from table one. My actual results from the script I have is a single (random) row from table two for each item in table one.....So my current results look like:

Order Number Description

123 Freight

234 Freight

345 Null

And I want:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

345 Null

etc.... Here is my code, courtesy of half a dozen samples found here and elsewhere...

Code Snippet

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc

Public Class ScriptMain
Inherits UserComponent

Dim connMgr As IDTSConnectionManager90
Dim odbcConn As OdbcConnection
Dim odbcCmd As OdbcCommand
Dim odbcParam As OdbcParameter

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.JDEConnection
odbcConn = CType(connMgr.AcquireConnection(Nothing), OdbcConnection)

End Sub

Public Overrides Sub PreExecute()

odbcCmd = New OdbcCommand("SELECT F4211.SDDSC1, F4211.SDDOCO FROM DB.F4211 F4211 Where F4201.SHDOCO = ?", odbcConn)

odbcParam = New OdbcParameter("1", OdbcType.Int)
odbcCmd.Parameters.Add(odbcParam)

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim reader As Odbc.OdbcDataReader
odbcCmd.Parameters("1").Value = Row.SO
odbcCmd.ExecuteNonQuery()
reader = odbcCmd.ExecuteReader()
If reader.Read() Then

With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With

End If

reader.Close()

End Sub

Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(odbcConn)
End Sub

End Class

I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.

I have also worked out an alternate way to do this using merge join tasks...but then my datareader source goes off and fetches 300,000 rows from F4211 before my final result set of about 1200 rows. That just feels like a bad approach to me...or am I being over-cautious? I'm a newb (if you couldn't already tell)...so guidence is appreciated.

Thank you....

In a first data flow, you could load a staging table in SQL server with the contents of your ODBC source table. Then in a second data flow, you can use that staging table as the source for your lookup component. Might be a bit less work for you.|||So, to make sure I understand--add another data flow. Have it write the records from my F4211 table to a SQL table, then, in my original data flow, do a lookup on my newly created table in SQL...then, I suppose, add an Execute SQL task to blow all those records away? And, I suppose, just to be tidy about it....I could add a shrink database task to clean up afterwards.....?|||

In general and when possible it is a good idea to use staging tables to put all data pieces on the SQL Server side. Besides simplify the dataflow; it improves performance.

I think you are undeestanding Phil's sugestion pretty well; but I am not shure is I would bother with the shrink database step; if you are going to execute this process in a regular basis then you would need that space anyway.

|||Yep, you got it. Now have fun!|||

hilaryjade wrote:

I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.

So, to go back to the original question.... You want all the rows from the recordset, right? Don't you just need to change that If to a While loop?

Code Snippet

While reader.Read()

With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With

End While

|||Told ya I was a newb....Thanks so much!!! While a staging table and lookup off it is an interesting idea (and I appreciate it...) the script runs so much faster.|||

hilaryjade wrote:

Told ya I was a newb....Thanks so much!!! While a staging table and lookup off it is an interesting idea (and I appreciate it...) the script runs so much faster.

Did you test it? Can you share the timing results and row counts?

|||I haven't finished adding my full data set I need to pull to my script component yet, but after I do, I can run and time them. I did try adding and using a staging table last night--but ran into a bit of a data type mismatch roadblock on the lookup--I tried a handful of conversions to see if I could get my DT_R8 from Excel to play nicely with my Numeric from the staging table in SQL, but got a bit frustrated and went off to work on my third alternative...using merge joins (which works nicely and runs in 2.2 minutes, but 2 minutes starts feeling a bit long, you know?). At any rate, just creating the staging table took longer than the script takes (but, again, that was without my full data set)....After I get the script component complete and pulling all my data, I'll run, time, and post results. Again, thanks for all the help!|||

Well, I'm not really comparing apples to apples with this, since my script component is part of a data flow that starts with a connection to an excel file, does a lookup on a table with an odbc connection via the script component and then writes to a recordset and the data flow for the staging table concept uses a data reader to collect my records from a table with an odbc connection and then writes them to a SQL db table...

The dataflow with the script component ran in 00:06.844 and wrote 953 rows to my recordset (just writing the rows I needed, selected via script component)

The dataflow to create a staging table ran in 01:16.313 and wrote 219,155 rows to a table in a SQL db, where I could then do a lookup to grab the records I need (953 rows)

I think for this instance, where I need so few records from such a large table, it makes sense to use the asynchronous script component rather than create a staging table.

Again, thanks to all for the help and suggestions. I really appreciate it.

|||Did you use the Fast Load option in the OLE DB Destination when using the staging table approach?|||Yes, on the Connection Manager page for the destination editor, Data access mode is set to Table or View - fast load.sql

Asynchronous Outputs on Script Component Best practice

If you have an output that is not synchronous with the input what is the best way of processing the data.

I am currently using a generic queue, and a custom class. I am creating an instance of the class in the ProcessINputRow and then adding it to the Queue.

The CreateNewOutputRows Dequeues the class instances and creates buffer rows.

Is there a better solution?

ArrayList? I've seen asynch components that cache data in an ArrayList.

-Jamie

|||The problem is having two threads, one putting data into a container and one taking it off. I think the queue is the best solution.

Assistance with error message

I am trying to run this script in Query Analyser and it seems to have a
problem in line 3:
-
select ID, 9, '2004', (select id from type where type = 'MF') from Dept
where dept in ('Internal Medicine', 'Surgery')
and ID + (select id from type where type = 'MF') +
convert(varchar(2), 9) +
convert(varchar(4), '2004')
not in
(select m.deptID + m.typeid + convert(varchar(2), m.dtmonth) +
convert(varchar(4), m.dtyear) from monthdept m, dept d, type t
where m.DeptID *= d.ID
and m.TypeID *= t.Type
)
The error message is: Invalid operator for data type. Operator equals add,
type equals uniqueidentifier.
I have tried playing with CAST/CONVERT without success. I am trying to look
for records on a key that spans 4 fields. Any suggestions/thoughts?
Schoo
PS: SQL Server 2000 running on W2K Server
Hi Schoo,
From your descriptions, I understood that the following codes raise an
error in QA, correct me if I was wrong, however, when I copied codes you
provided to my QA and then Parse Query (Ctrl + F5), it passed. To make
further research on this issue, I would appreciated if you could provide me
detailed DDL of your database and some sample record for me to reporduce it
on my machine. One more question, have you upgraded to the latest SQL
Server Service Pack?
Here is the document on how to get DDL from Enterprise Manager
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

assigning user to a database programmatically

Environment : vc.net , SQL Server
Hi All,
I have programmatically created an SQL Server database
by executing the script file for it. I have logged in as administrator
for creating the above database in my vc.net code using SQL APIs.
Now i need to assign a new user and pasword to this database
i have created in my program. Is there a way to do it ?
Regards,
Asif
Use sp_addlogin for adding the login to the database, then use sp_grantdbaccess to grant your user access to the database. If you then want to add the user to a role use sp_addrolemember
hth,
Lance

assigning user to a database programmatically

Environment : vc.net , SQL Server
Hi All,
I have programmatically created an SQL Server database
by executing the script file for it. I have logged in as administrator
for creating the above database in my vc.net code using SQL APIs.
Now i need to assign a new user and pasword to this database
i have created in my program. Is there a way to do it ?
Regards,
AsifUse sp_addlogin for adding the login to the database, then use sp_grantdbacc
ess to grant your user access to the database. If you then want to add the
user to a role use sp_addrolemember
hth,
Lance

Assigning Filepath to Global Variable in DTS

How can I assign file path to a DTS Global Variable in an activex script.

i hope u know how to create global variable in DTS. Here we have two global variable DestinationPath and SourcePath and see how its been used in VBScript

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Main = DTSTaskExecResult_Success

sSourceFile1 =dtsglobalvariables("DestinationPath").value
sSourceFile2 = dtsglobalvariables("SourcePath").value

set fso = CreateObject("Scripting.FileSystemObject")
fso.deletefile sSourceFile1
fso.copyfile sSourceFile2, sSourceFile1

End Function

http://www.sqldts.com is one of the best resource in DTS

Madhu

|||

Now that I have assigned a filepath to a global variable and is a dynamic one, since the file that is created is done on a daily basis and each file has a datetime stamp, i have figured out that already, but now i am facing trouble assigning the dynamic path that i have assigned in global variable to the Send Mail Task in DTS, can you help me with this issue.

|||

Consult http://msdn2.microsoft.com/en-us/library/ms141698.aspx for how to create a property expression on your send mail task.

You are just about there!

jkh

assign new value of ReadWrite column of string type in script component?

I am new user on VB ( I wish ssis support c# script)

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

Jun Fan wrote:

I am new user on VB ( I wish ssis support c# script)

It does. In SQL Server 2008!

Jun Fan wrote:

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

What is "prefix"?|||

Jun Fan wrote:

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

Could anyone tell me what I did wrong?

Thanks!

Your concatenated string is too long for the defined datatype of the strName column.
|||

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

|||

Jun Fan wrote:

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

Right, but if strName is defined as three bytes, and you try to add another byte from "prefix," it will fail beacuse four bytes is larger than the defined three byte maximum for strName.|||

Thanks for helping.

Yes, My streetName and prefix both are DT_WSTR wiht max lengh 100. Even the acutual value on each property is a couple char, but it take up all lengh. So I could not concatenate them without trim both property.

Thanks Again!

Jun

assign new value of ReadWrite column of string type in script component?

I am new user on VB ( I wish ssis support c# script)

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

Jun Fan wrote:

I am new user on VB ( I wish ssis support c# script)

It does. In SQL Server 2008!

Jun Fan wrote:

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

What is "prefix"?|||

Jun Fan wrote:

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

Could anyone tell me what I did wrong?

Thanks!

Your concatenated string is too long for the defined datatype of the strName column.
|||

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

|||

Jun Fan wrote:

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

Right, but if strName is defined as three bytes, and you try to add another byte from "prefix," it will fail beacuse four bytes is larger than the defined three byte maximum for strName.|||

Thanks for helping.

Yes, My streetName and prefix both are DT_WSTR wiht max lengh 100. Even the acutual value on each property is a couple char, but it take up all lengh. So I could not concatenate them without trim both property.

Thanks Again!

Jun

ASP-SQL Server Remote Connection error

Its been almost 2 months i start running a test script page to check if i made the remote connection until now its getting worst.

I did follow every bit of the instruction and pre-evaluation in fact the script is working locally with this string:

cn.Open "Driver={SQL Server};" & _
"Server=global-static-ip;" & _
"Address=local ip,1433;" & _
"Network=DBMSSOCN;" & _
"Database=testdb;" & _
"Uid=xx;" & _
"Pwd=xx;"

i also review the ff post for client and server trouble shooting tips:
http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

surface configuration, sql browser, firewall exemption...

and nothing seems change when i access the uploaded script.

is there anything i am missing?

by the way the script is uploaded by company.

hi,

what kind of exception are you reported with?

regards

|||Please do not double post and stick to your original posted thread.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Aspnetdb.mdf and aspnetdb_log script files

Hi,

Please any one can pass me Aspnetdb.mdf and aspnetdb_log scripts files in order to create those databases in sql server 2000.

thanks.....

Assumung that this is related to the Visual Web Development Kit, this can be downloaded under:
http://msdn.microsoft.com/vstudio/express/vwd/
HTH, jens Suessmeyer.

|||

Assuming that you mean the database that is created from the Visual Web Develoepr Package you can download it here. Once downloaded there should be a package to install the database within the web admin package.
http://msdn.microsoft.com/express/vwd/

HTH, Jens Suessmeyer.

ASPNETDB with publish provider

Hi!

I have created a sql script with publish provider and the file has all information about ASPNETDB and now i want to drive the sql script into my databas on my webbhosting.

How do i? i haveSQL Server Management Studio Express to admin my databas.

any?

I doubt you can connect directly to the database server on your webhosting company, but if you can then you should do like this:

Start Management Studio Express. Choose connect to server. Enter the name of the server (or IP) you wish to connect to. Make sure you enter the correct username and password. In this case I believe you should be using SQL authentication with the login you've been given from your hosting company. Once logged in, you start a new query (which will basically give you an empty window). Make sure your database is selected in the combo, and then simply paste your SQL code and press "play" :-)

If you cannot connect directly, through Management Studio Express, they may have web interface (such asSQL Server Web Data Administrator)that you can use to login to the server through the browser. But you must check with the company if they have such a system.

If this option is not available either, then you need to wrap up your code in an ASP.net page and execute it on the server. Let me know if this is what you need, and I can help you getting started with this.

Good luck!

|||

I think it works fine, my tables was created in my databases system tables folder.

But when iam trying to login, i get this messages:

EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'dbname', owner 'dbo'.

DO you iknow anyting of that?

|||

You installed the tables under a database login that "owns" the database i.e. is dbo.

When you log into your application, the connection is using different login/password that is not dbo i.e. has not been permissioned to use the stored procedures within your database. Either use the dbo login to provider your application login with the appropriate permissions (in this case "GRANT EXECUTE on [stored procedure name] to [login name]", or assign your application login with dbo permissions.

If you take the former route of assigning permissions on the actual database objects to your application login - note that the login will probably need execute permissions on all the stored procedures starting with aspnet_ in that database.

|||

So now i have created my table with a correct owner, but when i try to login on my webpage, i get same error.

How can i change the login ASP.NET control to check this table owner.aspnet_Users instead of dbo.aspnet_Users?

Any?

|||

Why do you want the aspnet_ tables under a different owner? They really should be under dbo.

|||

Because i get error messages when i try to login on the webpage, (the error message i write on top).

But if you have another idés so tell me. My userid to the database maybee couldt work with owner dbo.

|||

One Simple Answer to You.

SQL Server Database Publishing Services

http://www.microsoft.com/downloads/details.aspx?FamilyId=6F03273C-FFC8-4F5E-BAFC-041FBD68FD1E&displaylang=en

aspnet_regsql

Where's the sql script to generate "aspnet_regsql" application services database stored? Thanks.

We use ASP.NET SQL Server Registration tool (aspnet_regsql.exe) to create a Microsoft SQL Server database for use by the SQL Server providers in ASP.NET, and I haven't heard the corresponding SQL script. Are you sure there is a SQL script used to created database objects for use by application services?

For more information about the tool, you can refer to:

http://msdn2.microsoft.com/en-us/library/ms229862.aspx

|||I read an article saying DBA can use a script provided by MS to generate the application services database on SQL server. Thanks.

aspnet_regiis on remote system

Sure, the subject line seems easy enough. But is there a way that I can run this tool against my local SQL 2k5 server and script out the objects it creates to try and set up the tables and what-not on a remote server that doesn't accept remote connections? Or is there maybe a pre-built script somewhere that you can point me to?

I've tried to grab the stored procs and tables under the aspnet schema and script them out but it's not working correctly. Still can't run my login form against it.

Thanks

Hello,

I think you mean aspnet_regsql.exe? If you run it on your own computer like this: aspnet_regsql.exe /? you will see all available options. In it you will read that it's possible to generate an SQL script file. You can then use this script file to create the tables.

|||

Yes, I did mean the aspnet_regsql.exe. I gen'ed the script. THanks...