Assing Report Parameters

Can you evaluate and assign a new value to a parameter in an expression for a textbox?

Did you just curse?

I'm not even sure what you're asking. It's too general. What are you trying to do?

|||

Basically you want to assign a new value to the Parameter after the user selects his option. is that correct?

If yes, you will not be able to reassign this parameter but what you can do is

1. create another hidden parameter

2. Make sure that this is placed next tothe first parameter

3. Now, you can use the expression to assign the value to this hidden parameter based on what is selected in the first parameter.

Hope this answers your question...or another option is to take it back to SQL and then use the expression in SQL to reassign the values.

Assigning values based on InitialToggleState of a control in SSRS

Hi,

I need to assign the value for a field in a report based on Expand/Collapse state of another field.

Eg. If Collapsed, the value should be "AA" else if Expanded "BB".

Is there any way to get the value of InitialToggleState for any field in SSRS.

Thanks in advance.

Sathya

Use True for Collapsed and False for Expanded.

As explained at the following link

http://www.manning-sandbox.com/thread.jspa?threadID=14881&tstart=60

Assigning values based on InitialToggleState of a control in SSRS

Hi,

I need to assign the value for a field in a report based on Expand/Collapse state of another field.

Eg. If Collapsed, the value should be "AA" else if Expanded "BB".

Is there any way to get the value of InitialToggleState for any field in SSRS.

Thanks in advance.

Sathya

Use True for Collapsed and False for Expanded.

As explained at the following link

http://www.manning-sandbox.com/thread.jspa?threadID=14881&tstart=60

Assigning the value of variables in a Subpackage

Hi,

I have a parent SSIS package that executes various subpackages. Each of the subpackages contain variables that are required for their successful execution, e.g. one has a variable of datetime datatype and a variable of varchar datatype.

This date will essentially change with every running of the package as it specifies the date that additional data has been added to the back-end SQL Server 2005 database.

I can't find anything in the expressions of the Execute Package Task that would allow me to pass these variables into the package.

Can anyone advise?

Thanks,

Paul

Look at the documentation of "Execute Package" task @. http://msdn2.microsoft.com/en-us/library/ms137609.aspx

Section on "Passing Values to Child Packages"

Hope this helps.

Thanks,
Loonysan

|||

Thanks Loonysan

I'll give it a go

Assigning one text box value to the other text box in the sql reporting services Form

Hi,

Can some one help me in this case,

I am manipulating a value for onetext.value , And I want to assign like this

seconetext.value = onetext.value in the SQl reporting services form,

Is it possible? if so can any one help me with the syntax?

-Thanks

Yes, this is possible.

In "seconetext", use this expression:

=ReportItems!onetext.Value

-Chris

assigning each record to one string

hello,

i would like to loop through a record set and assign each value to the
same string, (example i would like to return all of the first name in
the authors table = Authors_total.)

should i use a cursor or just a loop to do this? I have had some
trouble with the syntax in a cursor.

nicholas.gadaczDear Nicholas,

I hope following will be help full for you.
---------------
Declare @.varstr as varchar(4000) -- declreation of
set @.varstr = ''; --initializing you know the fact Null + somhting =
Null
select @.varstr = @.varstr+','+isnull(ProductName,',') from Product;
Select @.varstr;
---------------

Best of Luck :) :) :)

Saghir Taj
MCDBA
www.dbnest.com: Home of DB Professionals.

ngadacz@.ftresearch.com wrote:
> hello,
> i would like to loop through a record set and assign each value to
the
> same string, (example i would like to return all of the first name in
> the authors table = Authors_total.)
> should i use a cursor or just a loop to do this? I have had some
> trouble with the syntax in a cursor.
> nicholas.gadacz|||Why not do that client-side? SQL isn't the best place for this kind of
presentational functionality.

--
David Portas
SQL Server MVP
--|||I am still not sure how i would loop through all of the records. if a
use a cursor i get an error variable assignment is not allowed in a
cursor declaration.

The reason why I don't put this functionality is the client side is
that I have multiple client sides: asp php and soon .aspx (.net) with
changes I want to have the code centralized.

nicholas.gadacz|||(ngadacz@.ftresearch.com) writes:
> I am still not sure how i would loop through all of the records. if a
> use a cursor i get an error variable assignment is not allowed in a
> cursor declaration.

DELARE @.str varchar(8000), @.col varchar(30)

DECLARE cur INSENSTIVE CURSOR FOR
SELECT col FROM tbl ORDER BY col
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @.col
IF @.@.fetch_status <> 0
BREAK

SELECT @.str = CASE WHEN @.str IS NULL
THEN @.col
ELSE @.str + ',' + @.col
EMD
END
DEALLOCATE cur

This is one of the few things where you must use a cursor. Another poster
showed an example with a SELECT statement. However, that is not guaranteed
to work.

> The reason why I don't put this functionality is the client side is
> that I have multiple client sides: asp php and soon .aspx (.net) with
> changes I want to have the code centralized.

Beware that the above solution has a hard limit of the output string of
8000 characters.

In SQL2005 there will actually be a way to do this in a single statement,
by some fairly funny usage of the new XML stuff.

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

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

Assigning a variable a table value

Hi I'm new to SQL and I'm trying to build a store procedure. What I'm trying to find out is how to assign a variable a value from a table.

So, I declare a variable:
DECLARE @.variable int
Then I want to assign a single int value to that variable that is already in a table. For instance the id value from a single row in a table.

I've tried SELECT INTO and SET, but nothing has worked so far and I'm lost after searching for a few hours.

Thanks in advance for any help

You need to use Select. You can assign multiple variables at the same time, but you cannot mix assignments with result sets. You can use either Set or Select to assign literal or constant values to a variable. Set only allows one variable at a time.

You can also use variables for values in a select statement.

Here is a full example:

Declare @.variable int,

@.pkey int

Set @.pkey = 42

Select @.variable = col1

From myTable

Where pkCol = @.pkey|||Thanks for your time and help it worked perfect.

assign variable value in Exists subquery

Hi
How to assign a variable value from if exist ?
like
declare @.i int
if exits( select @.i = ID from table1 where ID = 100)
-- do sth
but I always get an error
Thanks a lot for helpingAnn
You cannot do in that way.
DECLARE @.ord INT
IF EXISTS (SELECT * FROM Orders WHERE OrderId=10249)
SELECT @.ord=Orderid FROM Orders WHERE OrderId=10249
SELECT @.ord
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||Ann,
Posting the actual error would help.
Does it have to be in a subquery?
declare @.i int -- Defaults to NULL
select @.i = ID from table1 where ID = 100
if @.i is not null
-- do sth
is easy to read/follow.
Regards
AJ
"Ann" <Ann@.discussions.microsoft.com> wrote in message news:14A79DE3-EC8D-45D9-8554-F96AF32
956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||declare @.i int
SET @.i = ( select ID from table1 where ID = 100)
if @.i IS NOT NULL ......
Although I assume you can do whatever you want to do probably simpler with a
join instead of an IF, but for that you would have to post the rest of your
code.
Jacco Schalkwijk
SQL Server MVP
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||My problem is
I have two tables
Product
Product_ID Product_Name
100 Apple
101 Peach
102 Banana
Order
Product_ID Customer_ID Quantity
100 1 5
101 1 6
Now I need to generate a report with every product and every customer. The
problem is that if nobody purchases Banana(which is 102),I need to insert
null
so it will look like
Customer_ID Product_ID Quantity
1 100 5
1 101 6
1 102 NULL
IF EXISTS(
SELECT * FROM Order WHERE Customer_ID =1) INSERT INTO
#temp(Customer_ID ,Product_ID , Quantity) SELECT Customer_ID ,Product_ID,
Quantity FROM Order WHERE Customer_ID = 1
ELSE
INSERT INTO #temp(Customer_ID ,Product_ID ,
Quantity) VALUES(1,Product_ID,NULL) -- suppose only one product here
If I use
declare @.i int -- Defaults to NULL
select @.i = ID from table1 where ID = 100
if @.i is not null
-- do sth
I won't get 102(banana) in here
If I user
DECLARE @.ord INT
IF EXISTS (SELECT * FROM Orders WHERE OrderId=10249)
SELECT @.ord=Orderid FROM Orders WHERE OrderId=10249
SELECT @.ord
I'd have to select twice,that's why I am asking if possible,I can assign a
value in if exists
Thanks everyone
"Jacco Schalkwijk" wrote:

> declare @.i int
> SET @.i = ( select ID from table1 where ID = 100)
> if @.i IS NOT NULL ......
> Although I assume you can do whatever you want to do probably simpler with
a
> join instead of an IF, but for that you would have to post the rest of you
r
> code.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
>
>|||I assume you have a Customers table as well? Try:
SELECT C.customer_id, P.product_id,
COALESCE(SUM(quantity),0) AS quantity
FROM Customers AS C
CROSS JOIN Products AS P
LEFT JOIN Orders AS O
ON C.customer_id = O.customer_id
AND P.product_id = O.product_id
AND O.orderid = 10249
GROUP BY C.customer_id, P.product_id
I would think that the Orders table is denormalized if it has both the
order number and the customer id. Doesn't Order determine Customer?
David Portas
SQL Server MVP
--|||Thanks a lot,that 's what I need
"David Portas" wrote:

> I assume you have a Customers table as well? Try:
> SELECT C.customer_id, P.product_id,
> COALESCE(SUM(quantity),0) AS quantity
> FROM Customers AS C
> CROSS JOIN Products AS P
> LEFT JOIN Orders AS O
> ON C.customer_id = O.customer_id
> AND P.product_id = O.product_id
> AND O.orderid = 10249
> GROUP BY C.customer_id, P.product_id
> I would think that the Orders table is denormalized if it has both the
> order number and the customer id. Doesn't Order determine Customer?
> --
> David Portas
> SQL Server MVP
> --
>

assign the Null value to a variable that is not a Variant data type.

Hello Everyone,

I trying to upgrade our Alpha 4v4 Dos Database to MS SQL 2000 with Access XP front end and I have four tables that won't let import my data into them. I keep recieveing a message that says "You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)"
What can I do to get rid of this stupid error, is it a problem with Access XP or SQL 2000.Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?|||Originally posted by Paul Young
Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?

I tried that and I get a MS Jet Database Engine Error when I tried the import function, I know that in Access you can append data in, can you do that in SQL. What I mean is could I just link my Alpha tables into the SQL Database and then try to Append/Insert into my SQL tables. SQL is all new to me and I'm still in the process of learning it and it is alot different then Alphav4 and Access 97 functionality that I'm used to.|||Originally posted by Paul Young
Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?

Paul,

I can't import, append or insert. But I can copy and paste my old records into the new tables with a few error messages and only 65000 at a time. I guess that something is better than nothing. If anyone has a better way, I'm always open to try something new.|||Yes, you can append data in SQL.

There would be many ways to do this. The very first one that comes to mind would be to use DTS if you are using SQL 7/2k. DTS can connect to a variety of data sources and allows you to transform the data on the fly.

Microsoft's Books Online has some good information on this.|||Originally posted by Paul Young
Yes, you can append data in SQL.

There would be many ways to do this. The very first one that comes to mind would be to use DTS if you are using SQL 7/2k. DTS can connect to a variety of data sources and allows you to transform the data on the fly.

Microsoft's Books Online has some good information on this.
I've been using the DTS function and that is what I first tried to do my import with or even append and I still get an error message. If I import the whole table it will only bring in the structure, no data i get an error on that. THen if I try to append the data I get a different error. I'll look online and see if I can findout what I'm doing wrong. Or I'll just cut and paste since that seems to work.|||What were your errors?

DTS can be used to import a structure and/or data. Often I have found it easyier to created a db, and then use DTS to suck strucutre and all. Once the data is in I can make modifications and move the data to it's ultimate home.|||Originally posted by Paul Young
What were your errors?

DTS can be used to import a structure and/or data. Often I have found it easyier to created a db, and then use DTS to suck strucutre and all. Once the data is in I can make modifications and move the data to it's ultimate home.

If I import my data from Alpha into Access 97 and then into SQL I get an Insert error on 4 of my tables for certain Date fields that says Data Over flow invaild character value for cast specification. If I try to go directly into Alpha 4v4 which are Dbase 5 tables it just won't do it. I get a ms jet vb error and it will import nothing at all. Atleast in access it will import 32 of 36 tables.|||It seems odd that you get a jet error. Are you using the dBase 5 driver or the ODBC driver?|||Originally posted by Paul Young
It seems odd that you get a jet error. Are you using the dBase 5 driver or the ODBC driver?

Ok I changed my data source to dbase III and I was able to import my data directly form Alpha 4v4, plus I'm starting to usnderstand this DTS function. I was wondering if I can use it to just append the tables, not create them everytime. I noticed that everytime I use the wizard it wants to create the table then import the data. I want it to just Append the data now that I have the tables in SQL so that I can refreash the old data with the new until I'm ready to run everything in SQL. Can i physically change the SQL startments for that DTS function and is that possible.|||You should be able to just append data, I don't have dBase 5 to test with but when I load a CSV file into an existing table I can select Transformations and choose Append rows to destination table. That should do it for you.|||Originally posted by Paul Young
You should be able to just append data, I don't have dBase 5 to test with but when I load a CSV file into an existing table I can select Transformations and choose Append rows to destination table. That should do it for you.

Ok I can't find what your talking about "Select transformations and choose append rows?", could you do me a favor and type out the steps that you use to see if I'm going in the right direction. Appending my Bbase file and your CSV file shouldn't be that different as far as the steps go. Thank you for your help.|||ADP is the answer to all your problems.

It only deals with SQL Server-- so its much simpler than what you're talkin about..

in access 2002, you can even create a linked server-- just like how you can link to a different db in a mdb..

of course, i think that you'll need to put drivers on the SQL Server-- but thats not that big of a deal..|||Originally posted by aaron_kempf
ADP is the answer to all your problems.

It only deals with SQL Server-- so its much simpler than what you're talkin about..

in access 2002, you can even create a linked server-- just like how you can link to a different db in a mdb..

of course, i think that you'll need to put drivers on the SQL Server-- but thats not that big of a deal..

ADP, could you tell me more about it, I typed it into the help file and nothing came up.|||1. Fire up DTS
2. Fill in the data source
3. fill in the data destination click "NEXT >"
4. On the "Select Source Tables and Views" panel click on the elips "..." under Transformations.
5. Click on the "Append rows to destination table" radio button and then click on "OK"
6. Back on the "Select Source Tables and Views" panel click on "Next >"
7. On the "Save, schecule , and replicate package" panel click on "Next >".
8. You should be able to take it from here.|||Originally posted by Paul Young
1. Fire up DTS
2. Fill in the data source
3. fill in the data destination click "NEXT >"
4. On the "Select Source Tables and Views" panel click on the elips "..." under Transformations.
5. Click on the "Append rows to destination table" radio button and then click on "OK"
6. Back on the "Select Source Tables and Views" panel click on "Next >"
7. On the "Save, schecule , and replicate package" panel click on "Next >".
8. You should be able to take it from here.

Step four was the one that I wasn't finidng, thank you so much Paul, I did get it to work and now I just need to adjust it so that I won't get any errors when I try to append a date field. Thanks

Assign session variable value to update parameter

Hi, I'm trying to update a sqlserver database through vb.net in an asp.net 2.0 project. I'm using a sqldatasource and am trying to code an update parameter with a session variable.

code snippet:

<UpdateParameters><asp:ParameterName="hrs_credited"/>

<asp:ParameterName="updater_id"DefaultValue="<%$ Session("User_ID")%>"Type="Int32"/>

<asp:ParameterName="activity_id"/>

<asp:ParameterName="attendee_id"/>

</UpdateParameters>

The error message that I receive is:

Error 2 Literal content ('<asp:Parameter Name="updater_id" DefaultValue="" Type="Int32"/>') is not allowed within a 'System.Web.UI.WebControls.ParameterCollection'. C:\Development\CME\dataentry\attendance.aspx 29

Does anyone have an idea how to assign the session var value to the parameter?

Thanks!

There is a special parameter called a SessionParameter that does exactly that. Refer to this page for more information:http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sessionparameter.aspx

Assign Null value to Variable

I have a column with int data type.. i am trying to assign this column to a variable ( int) in For Each Loop..but it keeps giving me an erro

The type of the value being assigned to variable "User:ubcontractor_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Am i getting this error becasue the column has NULL value?. how can I resove this probelm?

Populate your source with 0s instead of NULLs in your source query, if applicable. (Or some arbitrary number)|||

can i do thsi in Expression builder using NULL function?

if so, can you show me some examples?

|||Anywhere in an expression builder, you can do:

ISNULL(ColumnOrVariable) ? 0 : ColumnOrVariable

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

Assign expression value using code

I am trying to use a variable to set an attribute value of an SSIS task but I keep running into the 4000 character limit of the string variable. Not sure why the variable which is of .NET type String has this limit when it doesn't when you are in a .NET environment. Regardless, can anyone provide some sample code that I could use to do this in a script task? I am trying to set the QueryString property of the Data Mining Query Task. All help would be appreciated.

Thank you in advance.

Variables and expressions in SSIS are limited to 4000 bytes. Sorry.|||As far as dynamically setting the QueryString, when you look at the properties of the Data Mining Query task, is there an "expressions" parameter? If you expand the expressions parameter, do you have an option for QueryString? (I'm not sitting in front of SSIS at the moment, so I can't verify.) If so, you may want to put your variable there instead of using a script.|||

All task properties support expressions, it is only Data Flow components that require the developer to actually set some code that says an expression is supported.

The problem is that the result of an expression cannot be greater than 4000 characters. Using a variable whose value is greater than 4000 characters will not work because this still has to pass through the expression evaluator to be assigned to the property value.

Since the DM Query Task does not offer anything other than a literal string for the query, this cannot be set dynamically within the package. I think this is a limitation, obviously it would be nice to have expressions with > 4000 characters but also tasks should be (consistently) developed with properties such as this accepting literals, variables and files. An ideal example is the Execute SQL Task with the SourceSQLType property that describes the interpretation of the SQL Statement property.

|||That is too bad. Hopefully this will change in a later version. Thanks for the help.

assign count value

Hi all,

How I can assign a COUNT result to a variable in store procedure like this:

Code Snippet

SELECT @.NumCalc=COUNT(*) AS NC
FROM (SELECT COUNT(*) AS N
FROM Trace
GROUP BY Ora) TraceTmp

thanks a lot

by hid

Hi all sorry but

SELECT @.NumCalc=COUNT(*) /*AS NC */
FROM (SELECT COUNT(*) AS N
FROM Trace
GROUP BY Ora) TraceTmp

Run !

Bye Bye Hid

|||

If you want save into @.NumCalc result of SELECT COUNT(*) AS N FROM Trace GROUP BY Ora, you could use following code:

Code Snippet

SELECT @.NumCalc= COUNT(*)

FROM #Trace

GROUP BY Ora

But if SELECT COUNT(*) AS N FROM Trace GROUP BY Ora returns more than one line, only last result will be saved in @.NumCalc.

If you want save into @.NumCalc number of lines returned by SELECT COUNT(*) AS N FROM Trace GROUP BY Ora, you could use following:

Code Snippet

select @.NumCalc = count(distinct ora) from #trace

|||

Are you getting an error message returned? There's actually nothing wrong with your query - as long as you declare @.NumCalc then it should work fine.

Incidentally, your query is equivalent to this:

SELECT @.NumCalc = COUNT(DISTINCT Ora)

FROM Trace

...which, personally, I find easier to read.

Chris

|||

You code will not execute BECAUSE you cannot BOTH assign a aggregrate to a variable AND give it an ALIAS.

@.NumCalc=COUNT(*) AS NC

Remove the [ AS NC ] and it will execute just fine.

However, it would be easier to read if written as:

Code Snippet

SELECT @.NumCalc = count(DISTINCT Ora)
FROM Trace

|||

Arnie,

I see that this solution have been recommended several times. It will work just if column [Ora] does not accept NULL.

Code Snippet

select

count(distinct c1)

from

(

select null as c1

union all

select 1

) as t

select

count(*)

from

(

select

count(*) as cnt

from

(

select null as c1

union all

select 1

) as t1

group by

c1

) as t2

AMB

|||

'Hunchback'

I guess I didn't get your point. The OP asked for help with [ SELECT count(*) ]. In giving him/her the 'benefit of the doubt', I assume that if NULL is not to be included in the count, the OP will later clarify.

And yes, you are correct, there were previous variations of the same suggestion. I was not offering anything new EXCEPT for an explanition about why the OP's code excerpt would not work. (Actually, one post told the OP that there was "nothing wrong with your query", when in fact, it just will not execute as presented.

|||

Arnie,

> I assume that if NULL is not to be included in the count, the OP will later clarify.

The OP is counting the number of rows produced by a grouping by [Ora], so changing it to count(distinct [Ora]) could not produce the same result y [Ora] allows NULL. That was my point and was reproduced with the attached script.

AMB

|||Thanks for the clarification -I overlooked that point. I appreciate that you corrected my mistake.|||

Arnie said:

'Actually, one post told the OP that there was "nothing wrong with your query", when in fact, it just will not execute as presented.'

Yes, that was me. It seems that the OP had found a solution and subsequently corrected their query in a follow-up post, to which I was replying. The second query has the alias commented out so it will work as posted and, therefore, my statement is correct.

Chris

|||

Yes, I agree. Yet, it did seem confusing since due to forum latency it first appeared to me that you were responding to the original post. Even after the thread caught up, it was difficult to determine which post you responded to.

I should not have included that comment -it didn't add any value to the discussion. My regrets.

Assign a value to a variable without using SQL Task

Greetings once again,

I am trying to achieve a seemingly simple task of assigning datetime value to a user variable at the point my package starts running. How can I do this without using a SQL Script Task? Should I be using a script task for this or is there a simpler way to achieving the same thing?

Thanks in advance.

There are a couple System variable that might have have what you are looking for: StartTime and ContainerStartTime. To see them go to variables and click in the gray icon with the X.|||

Hi Rafael,

Yes thank you. I am now using the System variable StartTime which is the start time of the package when it's run.

That's perfect!

ASP.net SQL Problem

Hello,

I have as exemple these values into my database:
ruiter paard punten
1 1 2
1 1 10
2 1 4
1 1 10

i want the second highest value of "punten" grouped by ruiter and paard

this is what i have got so far:
SELECT u.ruiter,u.paard,(SELECT max(TOP 2 punten) FROM uitslag where ruiter = u.ruiter and paard = u.paard) AS points1 FROM uitslag u group by u.ruiter,u.paard"

when i put this code into my asp.net page i get errors

Someone any ideas how i can fix this??
When i can get it to work for the second highest value. I then can work further for the third, fourth,... highest value.

tnx alot
PhilippeUse Query Analyser, that SQL just isn't SQL. It will give you better hints as to why it's wrong

ASP.NET 2 SQL Server Error

y i cannot insert value into my MS SQL, I'm using ASP.NET to link to my sql server and insert data into it! i'm also testing it on a standalone PC! the error message is...

Server Error in '/data' Application.
------------------------

SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.

Source Error:

Line 87: MyCommand = New SqlCommand(InsertCmd, MyConnection)
Line 88:
Line 89: MyCommand.Connection.Open()
Line 90:
Line 91: Try

Source File: c:\inetpub\wwwroot\data\Registration1.aspx.vb Line: 89

Stack Trace:

[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
System.Data.SqlClient.SqlConnection.Open() +384
data.WebForm1.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\data\Registration1.aspx.vb:89
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1277

------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

what is wrong with the code? my code is this...

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MyConnection = New SqlConnection("server=(local)\NetSDK;database=shangri;Integrated Security=SSPI")
Dim DS As DataSet
Dim MyCommand As SqlCommand
Dim InsertCmd As String = "INSERT INTO Customer(custID,custFirstname) VALUES('" & txtLastName.Text & "', '" & txtFirstName.Text & "')"

MyCommand = New SqlCommand(InsertCmd, MyConnection)

MyCommand.Connection.Open()

Try
MyCommand.ExecuteNonQuery()

Catch ex As Exception
Response.Write("error hao zhi")
End Try

MyCommand.Connection.Close()

End Sub

------------------
i have also included import system.data.sqlclient
Please help!
what the problem? Thansk bye!!! :)=(local)\NetSDK?

i think your connection string's whack|||INSERT INTO Customer(custID,custFirstname) VALUES('" & txtLastName.Text & "', '" & txtFirstName.Text & "')"

That's also going to generate an error. Unless custID is the users last name.|||any suggestion on that connection string?

ASP.NET / SQL Server Stored Procedure Question

Hello,

I wrote a stored procedure that inserts data into one table, then inserts the value of the identity column into another table:

SET

NOCOUNTON;

INSERTINTO ContactUs_TBL

(FullName, Email, Phone, Message)

VALUES

(@.FullName, @.Email, @.Phone, @.Message)

SELECT@.@.IDENTITY

INSERTINTO ContactUsQuestions_TBL

(QuestionText, ContactId)

VALUES

(@.QuestionText,@.@.IDENTITY)

In the CodeFile in asp.net (c#.net), I'm not what to set the value property to below. Right now I just hardcoded a 2 to see how it would work. Could anyone help me out and tell me what I should put here? Each of the other statements I used were set to the value of a form control, but since this id isn't a form control, just an identity column, I'm not sure what to do:

comm.Parameters.Add(

"@.ContactId",SqlDbType.Int);

comm.Parameters["@.ContactId"].Value = 2;

-- rkeslar

Replace @.@.IDENTITY with SCOPE_IDENTITY(). This isn't related to your problem, but you should do it anyhow.

You don't add a parameter for ContactID. You don't pass it into the stored procedure, and you don't tell .NET about it.

|||

hmm. ContactID is a primary key in the ContactUs_TBL, so I see why you wouldn't pass it into the stored procedure or tell .net about it for that table. But it's also a foreign key in the ContactUsQuestions_TBL so how is it going to get inserted into that table if I don't pass it into the stored procedure and tell .net about it?

Thanks

|||Because the stored procedure picks up the value when it's running and passes it to the second insert statement.