Problem With Variables in Execute SQL Task

I am trying to run a simple update (just to establish it works) passing in a variable in the execute SQL task. It is within a Foreach loop and the incoming data is from a RecordSet destination. One of the fields in the recordset is UniqueID. I have a variable called User::UniqueID which holds the uniqueID value. I then have a second variable which is the insert statement of

"INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@.[User::UniqueID] + ", null, null,null,null,null,null,null"

with delayvalidation set to True.

when I run the package i get the following error:

Error: 0xC0014054 at Execute SQL Task: Failed to lock variable "INSERT rptlifespan_transactionimagecopy SELECT 543, null, null,null,null,null,null,null" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

543 is the value of my first UniqueID so that bit is working ok. What I do not know is why the insert statement is failing.

Please help i have very little hair left!!

Is the SQLSourceType=Variable and the SourceVariable set to the name of your variable in the task editor? It looks as if maybe SQLSourceType=Variable and then you're setting the SqlStatementSource property through an expression to the value of your variable.
|||

Hi Jay

i have checked and the SqlSourceType is Variable and the SourceVariable is set to User:: SQLTest2 which is the name of the variable that contains the Value INSERT blah blah blah....

When I run it the package fails with the same error and when I go back into the Execute Sql Task editor the SourceVariable has changed to the actual INSERT statement.

Any help appreciated

|||Delete the Execute SQL Task and try recreating it. I think, too, there must be an expression set somewhere incorrectly.|||

Racsco wrote:

When I run it the package fails with the same error and when I go back into the Execute Sql Task editor the SourceVariable has changed to the actual INSERT statement.

That shouldn't be happening. Maybe you should delete the Execute SQL task and recreate it.
|||

Hi Guys

thanks for your continuing help.

I have deleted and recreated it and now i get the following error

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "INSERT rptlifespan_transactionimagecopy SELECT 543, null, null,null,null,null,null,null" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

any ideas?

|||Right. You're selecting more than one column and perhaps row, so you need to set the Execute SQL task to return a Full Result Set, not Single Row, and assign it to an OBJECT variable, not an integer variable.

Any reason you're selecting a bunch of NULL literals? Drop them and add them later using a derived column if you desire. It will make working with your result set easier.|||

Phil Brammer wrote:

Right. You're selecting more than one column and perhaps row, so you need to set the Execute SQL task to return a Full Result Set, not Single Row, and assign it to an OBJECT variable, not an integer variable.

Any reason you're selecting a bunch of NULL literals? Drop them and add them later using a derived column if you desire. It will make working with your result set easier.

It's an INSERT statement. There will be no resultset.
|||

JayH wrote:

Phil Brammer wrote:

Right. You're selecting more than one column and perhaps row, so you need to set the Execute SQL task to return a Full Result Set, not Single Row, and assign it to an OBJECT variable, not an integer variable.

Any reason you're selecting a bunch of NULL literals? Drop them and add them later using a derived column if you desire. It will make working with your result set easier.

It's an INSERT statement. There will be no resultset.

Oh, right. I saw the INSERT and the SELECT together and misread it as two distinct statements.

So unless you have another SQL statement after the result set, set the result set to "None."|||

OK I am nearing frustration point!!

I have ripped it back to the bare minimum just so I can get it to work. I have a data flow that does a select on 1 column - UniqueID from a table into a recordset destination as an object variable called LoadList. This then flows to a Foreach Container set as Foreach ADO Enumerator with 1 Variable mapped as User:: UniqueID and index 0. Within the Foreach Loop is the Execute SQL task with SQLSourceType of Variable and SourceVariable of User:: SQLTest3 and result set set to none. I have 2 package level variables which are User:: UniqueID which is INT32 and value is initially 0 and User:: SQLTest3 which is "INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@.[User::UniqueID] . So all i am trying to do is place the unique ID from the initial select into a different table.

Thanks once again for any help

|||

Racsco wrote:

OK I am nearing frustration point!!

I have ripped it back to the bare minimum just so I can get it to work. I have a data flow that does a select on 1 column - UniqueID from a table into a recordset destination as an object variable called LoadList. This then flows to a Foreach Container set as Foreach ADO Enumerator with 1 Variable mapped as User:: UniqueID and index 0. Within the Foreach Loop is the Execute SQL task with SQLSourceType of Variable and SourceVariable of User:: SQLTest3 and result set set to none. I have 2 package level variables which are User:: UniqueID which is INT32 and value is initially 0 and User:: SQLTest3 which is "INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@.[User::UniqueID] . So all i am trying to do is place the unique ID from the initial select into a different table.

Thanks once again for any help

Your description sounds good. I assume you have a reason for doing it this way instead of just bulk loading into rptlifespan_transactionimagecopy from the data flow. So from your "frustration" comment I assume you're still having a problem. Is it the "An error occurred while extracting the result into a variable..."? I don't see how you could be getting that if your ResultSet is None and there are no mappings on the Resultset page.
|||Forget the data flow.

Code block because of the $@.!* emoticon conversions...

1 - Execute SQL Task: select uniqueID from table. Put that into an object variable (User::ObjectVar, perhaps) and set the result set type to "Full Result Set"
2 - Foreach Loop - Set it to loop on the object variable created from step 1 (User::ObjectVar). Map the output accordingly to your "holding" variable (User::UniqueID)
3 - Execute SQL Task within the foreach loop: SQLSourceType: Variable SourceVariable: User::SQLTest3

That's it. One thing to note: On the variable, User::UniqueID, make sure you have EvaluateAsExpression set to TRUE and that you are using the expression property to build the insert string.|||

Hi Phil

I have followed the above the only bit I am not sure on is where you say "you are using the expression property to build the insert string" as my variable User:: SqlTest3 contains the insert statement?

Cheers again

scott

|||

Racsco wrote:

Hi Phil

I have followed the above the only bit I am not sure on is where you say "you are using the expression property to build the insert string" as my variable User:: SqlTest3 contains the insert statement?

Cheers again

scott

On the properties for SQLTest3, since you are concatenating another user variable in your SQL statement, you need to do that in the expression. Set the property, EvaluateAsExpression = TRUE for the variable, SQLTest3. Then use the expression:

"INSERT INTO ... SELECT " + [User::UniqueID]|||

Hi Phil

Went home and slept on it, came in deleted the lot and started fresh. What I did differently was to cast the UniqueID as a string from the initial select rather than casting it in User:: SQLtest3 variable and it works fine now.

Thanks for all your help and patience and also thanks to JayH

scott

Problem with variables

Hi,

I'm new to ssis and unfortunatly I'm having problems already. I'm trying to set a value to a variable in a Script Component using the following code:

Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try

Dim vars As IDTSVariables90
Me.VariableDispenser.LockForWrite(varName, vars)
Me.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub

It runs without any trouble and no errors. I've made several tests on the input variable and it's just fine. At the end the destination variable does not change. Everyone who is using that code does not complain.

The destination variable is a date ant it is writable.

Any ideia of what is happening or any alternative sugestion?

Thank you in advance

Variables are re-set to their original values after package execution. You can monitor their values during run-time by adding a breakpoint and putting a watch on the variable.

|||

Try using these function !! It may solve u r problem...

Private Function writeVariable(ByVal varName As String, ByVal value As String) As Object

Dim result As Object

Try

Dim vars As Variables

Dts.VariableDispenser.LockOneForWrite(varName, vars)

Try

vars(varName).Value = value

result = vars(varName).Value

Catch ex As Exception

Throw ex

Finally

vars.Unlock()

End Try

Catch ex As Exception

Throw ex

End Try

Return result

End Function

Private Function readVariable(ByVal varName As String) As Object

Dim result As Object

Try

Dim vars As Variables

Dts.VariableDispenser.LockForRead(varName)

Dts.VariableDispenser.GetVariables(vars)

Try

result = vars(varName).Value

Catch ex As Exception

Throw ex

Finally

vars.Unlock()

End Try

Catch ex As Exception

Throw ex

End Try

Return result

End Function

Dev

|||Thank you both very much. I will try to do as you suggest.
|||

Anthony Martin wrote:

Variables are re-set to their original values after package execution.

Correct in what you see, but a bit of while lie, as there is no explicit reset, there is just no Save of the package state. The instance of the package that is executing is just thrown away at the end, so you never see what happened unless you ask, breakpoints etc

It is perhaps most confusing when in the designer and executing a package, that you cannot just examine a variable value when you feel like it, and the values are not there to view at the end of execution. The simple reason for this is that the instance in the designer is not actually the instance being executed, as the designer itself cannot execute a package. You'll notice that as part of execution a package is saved. This is because it needs to be saved, so it can be loaded by the debug host, an entirely separate process that needs to load the package from scratch. This debug host (dtsdebughost.exe) is what really runs the package for us, and it then sends little messages to the designer so we can see what is going on, and get the pretty colours and numbers. Similarly if we look at a variable value in the designer, even whilst a package is executing, we are looking at an offline copy. To see the real live copy in the debug host we need to use the Watch window and a breakpoint, so that the designer can ask explicitly for the current value.

Does that make sense?

|||It makes a lot of sense Anthony. Maybe my code worked but I just could not see the change. I looked at the package explorer and it kept it's original value.

I feel a little embarassed for making you guys waste your time with something so trivial.
Again, thank you.
|||(Darren?) A common mistake, I know I made it. It is confusing, and certainly not immediately obvious as what you see all appears OK on the surface, just the values being wrong! Not a waste of time at all to ask.

Problem with variables

Hi,

I'm new to ssis and unfortunatly I'm having problems already. I'm trying to set a value to a variable in a Script Component using the following code:

Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try

Dim vars As IDTSVariables90
Me.VariableDispenser.LockForWrite(varName, vars)
Me.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub

It runs without any trouble and no errors. I've made several tests on the input variable and it's just fine. At the end the destination variable does not change. Everyone who is using that code does not complain.

The destination variable is a date ant it is writable.

Any ideia of what is happening or any alternative sugestion?

Thank you in advance

Variables are re-set to their original values after package execution. You can monitor their values during run-time by adding a breakpoint and putting a watch on the variable.

|||

Try using these function !! It may solve u r problem...

Private Function writeVariable(ByVal varName As String, ByVal value As String) As Object

Dim result As Object

Try

Dim vars As Variables

Dts.VariableDispenser.LockOneForWrite(varName, vars)

Try

vars(varName).Value = value

result = vars(varName).Value

Catch ex As Exception

Throw ex

Finally

vars.Unlock()

End Try

Catch ex As Exception

Throw ex

End Try

Return result

End Function

Private Function readVariable(ByVal varName As String) As Object

Dim result As Object

Try

Dim vars As Variables

Dts.VariableDispenser.LockForRead(varName)

Dts.VariableDispenser.GetVariables(vars)

Try

result = vars(varName).Value

Catch ex As Exception

Throw ex

Finally

vars.Unlock()

End Try

Catch ex As Exception

Throw ex

End Try

Return result

End Function

Dev

|||Thank you both very much. I will try to do as you suggest.
|||

Anthony Martin wrote:

Variables are re-set to their original values after package execution.

Correct in what you see, but a bit of while lie, as there is no explicit reset, there is just no Save of the package state. The instance of the package that is executing is just thrown away at the end, so you never see what happened unless you ask, breakpoints etc

It is perhaps most confusing when in the designer and executing a package, that you cannot just examine a variable value when you feel like it, and the values are not there to view at the end of execution. The simple reason for this is that the instance in the designer is not actually the instance being executed, as the designer itself cannot execute a package. You'll notice that as part of execution a package is saved. This is because it needs to be saved, so it can be loaded by the debug host, an entirely separate process that needs to load the package from scratch. This debug host (dtsdebughost.exe) is what really runs the package for us, and it then sends little messages to the designer so we can see what is going on, and get the pretty colours and numbers. Similarly if we look at a variable value in the designer, even whilst a package is executing, we are looking at an offline copy. To see the real live copy in the debug host we need to use the Watch window and a breakpoint, so that the designer can ask explicitly for the current value.

Does that make sense?

|||It makes a lot of sense Anthony. Maybe my code worked but I just could not see the change. I looked at the package explorer and it kept it's original value.

I feel a little embarassed for making you guys waste your time with something so trivial.
Again, thank you.
|||(Darren?) A common mistake, I know I made it. It is confusing, and certainly not immediately obvious as what you see all appears OK on the surface, just the values being wrong! Not a waste of time at all to ask.

Problem with variable of type money?

Here's what I want to do: I've got a table with orders, each order has
a specific discountrate (an int, which represents a percentage). Each
order consists of 1 or more items in another table, each item in that
table has a price. Now I want to return the full price and the
discounted price (or the discounted amount).

Here's a relevant excerpt of the code:

--------------------
CREATE TABLE #tmp (OrderID Integer,
Price money,
Discount money)

DECLARE @.Discount money

SELECT @.Discount =
(
(
(SELECT SUM(OrderDetails.Price * OrderDetailsAmount)
FROM OrderDetails
WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
+
(SELECT ISNULL(SUM(OrderDetailsSupplement.Price *
OrderDetailsAmount),0)
FROM OrderDetailsSupplement
INNER JOIN OrderDetails ON
OrderDetailsSupplement.OrderDetailsID = OrderDetails.OrderDetailsID
WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
)
*
( @.DiscountRate / 100 )
)

SELECT CustomerFull,
SUM(Price) As Price,
SUM(Discount) As Discount,
SUM (Products) As Products,
COUNT(@.orderID) As Orders
FROM #tmp
GROUP BY CustomerFull
ORDER BY CustomerFull
--------------------

The problem: instead of getting a low number (like 0.57 for instance),
I get a 0. Right now I've "solved" this by replacing "( @.DiscountRate /
100 )" with just "@.DiscountRate" and then dividing by 100 in my asp
code, but I'd really like to know what I'm doing wrong.

--
BVHAm 2 Mar 2006 07:23:09 -0800 schrieb bartvanhemelen@.gmail.com:

> Here's what I want to do: I've got a table with orders, each order has
> a specific discountrate (an int, which represents a percentage). Each
> order consists of 1 or more items in another table, each item in that
> table has a price. Now I want to return the full price and the
> discounted price (or the discounted amount).
> Here's a relevant excerpt of the code:
> --------------------
> CREATE TABLE #tmp (OrderID Integer,
> Price money,
> Discount money)
> DECLARE @.Discount money
> SELECT @.Discount =
> (
> (
> (SELECT SUM(OrderDetails.Price * OrderDetailsAmount)
> FROM OrderDetails
> WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
> +
> (SELECT ISNULL(SUM(OrderDetailsSupplement.Price *
> OrderDetailsAmount),0)
> FROM OrderDetailsSupplement
> INNER JOIN OrderDetails ON
> OrderDetailsSupplement.OrderDetailsID = OrderDetails.OrderDetailsID
> WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
> )
> *
> ( @.DiscountRate / 100 )
> )
> SELECT CustomerFull,
> SUM(Price) As Price,
> SUM(Discount) As Discount,
> SUM (Products) As Products,
> COUNT(@.orderID) As Orders
> FROM #tmp
> GROUP BY CustomerFull
> ORDER BY CustomerFull
> --------------------
> The problem: instead of getting a low number (like 0.57 for instance),
> I get a 0. Right now I've "solved" this by replacing "( @.DiscountRate /
> 100 )" with just "@.DiscountRate" and then dividing by 100 in my asp
> code, but I'd really like to know what I'm doing wrong.

In your example i can't see where @.DiscountRate is declared or set. From
where should the value for @.DiscountRate come?

bye,
Helmut|||BVH,

You are probably the victim of integer arithmetic.

Change @.DiscountRate / 100 to @.DiscountRate / 100.0.

That "point zero" tells the system that you want float division,
instead of integer division. The better solution would be to cast the
integer to a float but adding .0 will work.|||(bartvanhemelen@.gmail.com) writes:
> *
> ( @.DiscountRate / 100 )
> )
>...
> The problem: instead of getting a low number (like 0.57 for instance),
> I get a 0. Right now I've "solved" this by replacing "( @.DiscountRate /
> 100 )" with just "@.DiscountRate" and then dividing by 100 in my asp
> code, but I'd really like to know what I'm doing wrong.

@.DiscountRate was integer, correct?

You should have left out the parentheses above. This mandates SQL Server
to compute this expression before it gets mixed with the rest. But if
you divide two integers, you get integer division, which is not what you
want at all.

Assuming that what is before the * is money, leaving out the parenthesis,
transforms the division to money division.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||what happens next week when the boss wants to give a five and a half
percent discount???

percentages are ALWAYS better stored as real, and multiplied by 100 for
display to the users.|||I agree with Doug. We usually use reals to hold our percentages. In the
db, a 5.5% discount would look like .055.......

Because, in effect, that's really what 5.5% represents.|||figital (mharen@.gmail.com) writes:
> I agree with Doug. We usually use reals to hold our percentages. In the
> db, a 5.5% discount would look like .055.......
> Because, in effect, that's really what 5.5% represents.

In our shop we can never make up our mind... So some of the percentages
are stored as aba_percent, others as aba_fraction and yet others as float.
aba_percent is just an alias for "float" but the name implies that it is
a percentage, and that you should divide with 100 before use. aba_fraction
is float, and constrained to be between 0 and 1. Multiply with 100 before
display, and divide by 100 before storing. Those that just float, can
hold values outside the range [0..1]. (I have considered a constraint
to keep them between -10 and 10, but that is a risky business, as one
day 1200% may be a correct value.)

The problem with storing percentages as fraction, is that some developers
make the entry forms a carbon of the data model, so they don't display
the fraction as a percentage, but as a fraction...

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>ba_fraction is float, and constrained to be between 0 and 1

Well, actually, I've used percentages to keep track of growth rates, or
percentage growths where the percentage can greatly exceed one.

For instance, 2.00 means 200 percent, which means multiply by 200
percent.

Another really good thing about storing 5 percetn as .05 is that the
very first time the programmer/UI dweeb displays it that way, the
programmer dweeb sees it.

At the VERY worst, the end user sees it, and pretty much ANYONE used to
dealing with money, growths, or numbers will report it as a pretty
minor bug, and realize what is going on.
Again, differing points of view arrived at by rational people, but IMO
float is a much better solution.
regards,
doug

Problem with variable in T-SQL

Hi,

I want to find out the project names, for the PROJ table and I know
the Project IDs as a string (comma seperated).

I tried this:

--------
DECLARE @.proj_ids varchar(500)

SET @.proj_ids='(100,200)'

SELECT PROJ_NAME FROM PROJ WHERE PROJ_ID IN + @.proj_ids
--------

But this throws the error:
"Incorrect syntax near '+'"

in Query Analyzer.

Could someone please point, what I am doing wrong?
I know, I must be doing something very silly!Hi

Check out the excellent article at:
http://www.algonet.se/~sommar/dynamic_sql.html

John

"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310140529.3af4dd96@.posting.google.c om...
> Hi,
> I want to find out the project names, for the PROJ table and I know
> the Project IDs as a string (comma seperated).
> I tried this:
> --------
> DECLARE @.proj_ids varchar(500)
> SET @.proj_ids='(100,200)'
> SELECT PROJ_NAME FROM PROJ WHERE PROJ_ID IN + @.proj_ids
> --------
> But this throws the error:
> "Incorrect syntax near '+'"
> in Query Analyzer.
> Could someone please point, what I am doing wrong?
> I know, I must be doing something very silly!|||[posted and mailed, please reply in news]

Surajit Laha (s_laha@.rediffmail.com) writes:
> I want to find out the project names, for the PROJ table and I know
> the Project IDs as a string (comma seperated).
> I tried this:
> --------
> DECLARE @.proj_ids varchar(500)
> SET @.proj_ids='(100,200)'
> SELECT PROJ_NAME FROM PROJ WHERE PROJ_ID IN + @.proj_ids
> --------
> But this throws the error:
> "Incorrect syntax near '+'"
> in Query Analyzer.
> Could someone please point, what I am doing wrong?
> I know, I must be doing something very silly!

Yes, you are inventing your own syntax. SQL Server is not clever enough
to understand that.

Although, John recommended one article on my web site, I like to
recommend another. Look at
http://www.algonet.se/~sommar/array...ist-of-integers
to see a function that will help you out.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks a lot to you people!!!

The article at the URL pointed, was fabulous!
I really liked it and solved a lot of confusions of mine...

Thanks guys.

-surajit

Problem with varchar(max) in SQL Server 2005

Hi All,
I am having a small problem with varchar(max) type variable. Please look at
the code below.
DECLARE @.varcharmax varchar(max), @.varchar varchar(8000)
SET @.varcharmax = REPLICATE(‘D’, 10000) – this will end up with 10000
lengthy string
SET @.varchar = REPLICATE(‘D’, 10000) – this will end up with 8000 leng
thy
string
When I run the a code like;
SET @.varcharmax = @.varchar + @.varchar + @.varcharmax
SELECT LEN(@.varcharMAX) AS Length
The result is 18000
But when I take the varcharmax front;
SET @.varcharmax = @.varcharmax + @.varchar + @.varchar
SELECT LEN(@.varcharMAX) AS Length
The result is 26000
What is the problem with my code? I made a small experiment
(http://spaces.msn.com/members/dineshpriyankara/Blog/cns!1p-RgJ7SV2D2-6Y3lW0
UC8uA!217.entry) on this, if want have a look please. I am using April CTP.
Highly appreciate your reply.
Dinesh Priyankarahttp://communities.microsoft.com/ne...p=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dinesh Priyankara" wrote:

> Hi All,
> I am having a small problem with varchar(max) type variable. Please look a
t
> the code below.
> DECLARE @.varcharmax varchar(max), @.varchar varchar(8000)
> SET @.varcharmax = REPLICATE(‘D’, 10000) – this will end up with 1000
0
> lengthy string
> SET @.varchar = REPLICATE(‘D’, 10000) – this will end up with 8000 le
ngthy
> string
> When I run the a code like;
> SET @.varcharmax = @.varchar + @.varchar + @.varcharmax
> SELECT LEN(@.varcharMAX) AS Length
> The result is 18000
> But when I take the varcharmax front;
> SET @.varcharmax = @.varcharmax + @.varchar + @.varchar
> SELECT LEN(@.varcharMAX) AS Length
> The result is 26000
> What is the problem with my code? I made a small experiment
> (http://spaces.msn.com/members/dineshpriyankara/Blog/cns!1p-RgJ7SV2D2-6Y3l
W0UC8uA!217.entry) on this, if want have a look please. I am using April CTP
.
> Highly appreciate your reply.
> Dinesh Priyankara
>
>
>|||Posted same at
http://communities.microsoft.com/ne...p=sqlserver2005
thanks
"Mike Epprecht (SQL MVP)" wrote:
> http://communities.microsoft.com/ne...p=sqlserver2005
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Dinesh Priyankara" wrote:
>

problem with varchar and nvarchar datatype in linked server

Hi,

I am updating a remote table using linked server in sql server 2005.

but in case of varchar and nvarchar i am getting an error :

"OLE DB provider "SQLNCLI" for linked server "LinkedServer1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 16955, Level 16, State 2, Line 1

Could not create an acceptable cursor."

thanks in advance.

Thanks & Regards

Pintu

Pintu, I had a similar problem with deleting rows through a linked sever from SQL Server 2005. I tried from SQL Server 2000 and got a slightly different error :-

"The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements."

which led me to this article:-

http://support.microsoft.com/kb/814581

which solved my problem. Hope it helps you too.