cursor type error

this is an error which i happen to encounter..can you guys
help me out here..
cursor type should be :rdopenForwardonly
lock type should be :rdConcurReadonly
Rowsetsize should be : 1
how do i solve this error.i tried the isql/w script but
its not working either..any chance you guys know..
thanks
Please post this to the SQL Server Programming newsgroup for assistance
from other SQL developers.
Chris Skorlinski
Microsoft SQL Server Support
Please reply directly to the thread with any updates.
This posting is provided "as is" with no warranties and confers no rights.

Cursor type changed?

Hi,
I just posted this on sqlserver.connect since I'm not sure where it belongs.
So here goes.
We've recently began migrating to SQL 2005 from SQL 7 and have had a few
issues.
Right now we have an issue when trying to logon to the server through our
application.
'sa' login works from our application but when we try to logon as a user we
get:
"[ODBC SQL Server Driver]Cursor type changed"
I have tried logging on through Query Analyzer and that works fine for all
users so it should not be a permission issue.
I've tried to search the web high and low without really finding a solution
or cause for this error. I've seen a few a reports of people having the
same- or similar problems but no solutions.
I'd be greatful for any tips you guys and girls might have.
Thanks in advance and have a great wend.
Regards,
Tony HolopainenThis message is informational, not an error. Perhaps the application code
is treating the message as an error simply because it's unexpected.
Are you using ADO or calling ODBC directly? Do the get this message during
login or when you run a query? I wouldn't expect this to be security
related unless different results are returned depending on the user logging
in.
Hope this helps.
Dan Guzman
SQL Server MVP
"TonyH" <tony@.nospam.com> wrote in message
news:e8OWomAnGHA.4604@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I just posted this on sqlserver.connect since I'm not sure where it
> belongs. So here goes.
> We've recently began migrating to SQL 2005 from SQL 7 and have had a few
> issues.
> Right now we have an issue when trying to logon to the server through our
> application.
> 'sa' login works from our application but when we try to logon as a user
> we get:
> "[ODBC SQL Server Driver]Cursor type changed"
> I have tried logging on through Query Analyzer and that works fine for all
> users so it should not be a permission issue.
> I've tried to search the web high and low without really finding a
> solution or cause for this error. I've seen a few a reports of people
> having the same- or similar problems but no solutions.
> I'd be greatful for any tips you guys and girls might have.
> Thanks in advance and have a great wend.
> Regards,
> Tony Holopainen
>

cursor type

Hi there,
Can anyone tell me what's happening here?
I have the following code snippet that opens a recordset using some stored
procedure that returns some rows.
Dim rst as new ADODB.recordset
Dim Count as long
'rst.CursorType = adOpenStatic
rst.Open "Execute my_storedprocedure" & ItemID, _
m_con, adOpenStatic, adLockReadOnly
Count =rst.Recordcount
I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
out that after opening the recordset, rst.CursorType = 0 (adOpenForwardOnly)
again. I tried setting rst.CursorType = adOpenStatic before specifically
before opening the recordset but it didn't help. It will still be reset to
adOpenForwardOnly after it's open. I think that's why RecordCount
returns -1.
Many thanks.
SusanPerhaps this will help:
http://www.sqlteam.com/item.asp?ItemID=11842
"Susan" <xxx> wrote in message news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> Can anyone tell me what's happening here?
> I have the following code snippet that opens a recordset using some stored
> procedure that returns some rows.
>
> Dim rst as new ADODB.recordset
> Dim Count as long
> 'rst.CursorType = adOpenStatic
> rst.Open "Execute my_storedprocedure" & ItemID, _
> m_con, adOpenStatic, adLockReadOnly
> Count =rst.Recordcount
> I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
> out that after opening the recordset, rst.CursorType = 0
> (adOpenForwardOnly) again. I tried setting rst.CursorType = adOpenStatic
> before specifically before opening the recordset but it didn't help. It
> will still be reset to adOpenForwardOnly after it's open. I think that's
> why RecordCount returns -1.
> Many thanks.
> Susan
>|||I found out that this only happens when I use "EXEC my_storedprocedure" to
open a recordset. If I use embedded sql to open a recordset, e.g.
rst.Open "SELECT * FROM Products", m_con, adOpenStatic, adLockReadOnly
then it will returns the RecordCount fine.
But how do I work around that? I still like to use stored procedure though.
Thanks,
Susan
"Susan" <xxx> wrote in message news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> Can anyone tell me what's happening here?
> I have the following code snippet that opens a recordset using some stored
> procedure that returns some rows.
>
> Dim rst as new ADODB.recordset
> Dim Count as long
> 'rst.CursorType = adOpenStatic
> rst.Open "Execute my_storedprocedure" & ItemID, _
> m_con, adOpenStatic, adLockReadOnly
> Count =rst.Recordcount
> I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
> out that after opening the recordset, rst.CursorType = 0
> (adOpenForwardOnly) again. I tried setting rst.CursorType = adOpenStatic
> before specifically before opening the recordset but it didn't help. It
> will still be reset to adOpenForwardOnly after it's open. I think that's
> why RecordCount returns -1.
> Many thanks.
> Susan
>|||Could you clarify that you are using SET NOCOUNT ON within you stored
procedure?
Do you have any PRINT statetments withing your stored procedure?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
2000 - www.ciquery.com/articles/useofindexes.asp
"Susan" <xxx> wrote in message
news:uy0B%232bMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> I found out that this only happens when I use "EXEC my_storedprocedure" to
> open a recordset. If I use embedded sql to open a recordset, e.g.
> rst.Open "SELECT * FROM Products", m_con, adOpenStatic, adLockReadOnly
> then it will returns the RecordCount fine.
> But how do I work around that? I still like to use stored procedure
though.
> Thanks,
> Susan
> "Susan" <xxx> wrote in message
news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
stored
found
adOpenStatic
>|||I suspected that too but no, I don't have SET NOCOUNT ON or Print statement.
Actually I found out sortly that if I set the connection's cursor location
to adUseClinet
m_con.CursorLocation = adUseClient
then it will return the RecordCount just fine. Does this mean that if I use
a stored procedure to open a recordset, then I need to specifically set
adUseClient to get a recordset other than a firehose forward only recordset?
But adUseServer is fine if I use embedded sql statement to open a recordset?
Susan
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dsv9cr$p82$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Could you clarify that you are using SET NOCOUNT ON within you stored
> procedure?
> Do you have any PRINT statetments withing your stored procedure?
>
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Susan" <xxx> wrote in message
> news:uy0B%232bMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> though.
> news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> stored
> found
> adOpenStatic
>|||I thought that all ADO recordsets returned from a stored procedure are
client side.
"Susan" <xxx> wrote in message news:OXjHa8lMGHA.3708@.TK2MSFTNGP09.phx.gbl...
>I suspected that too but no, I don't have SET NOCOUNT ON or Print
>statement.
> Actually I found out sortly that if I set the connection's cursor location
> to adUseClinet
> m_con.CursorLocation = adUseClient
> then it will return the RecordCount just fine. Does this mean that if I
> use a stored procedure to open a recordset, then I need to specifically
> set adUseClient to get a recordset other than a firehose forward only
> recordset? But adUseServer is fine if I use embedded sql statement to open
> a recordset?
> Susan
>
> "Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
> news:dsv9cr$p82$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
>|||No, you can open a server-side cursor on a stored procedure. What's
difficult is opening a server-side scrollable cursor that supports
RecordCount on a stored procedure:
http://groups.google.com/group/micr.../>
aef2?hl=en&
To the OP, I hope you take to heart the advice in that thread to use a less
expensive way to count your records.
Bob Barrows
JT wrote:
> I thought that all ADO recordsets returned from a stored procedure are
> client side.
> "Susan" <xxx> wrote in message
> news:OXjHa8lMGHA.3708@.TK2MSFTNGP09.phx.gbl...
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Cursor to Array

Can you Assign a cursor to an array

For example:

Declare AuditInfo cursor
for
Select top 2 TRAKRID,MeterID,DelCount,ContactID,Type,Active,Cre atedDateTime,ModifiedBy,DateModified
from TRAKRAudit
where TRAKRID = @.ID
order by createdDatetime desc
--Print @.ID
open AuditInfo

-- Get all the newest values
fetch next From AuditInfo into Array()

Thanks
LJdeclare @.tbl table (...)
insert @.tbl Select top 2 TRAKRID,MeterID,DelCount,ContactID,Type,Active,Cre
atedDateTime,ModifiedBy,DateModified
from TRAKRAudit
where TRAKRID = @.ID
order by createdDatetime desc|||Ok this leads me to another ?

Is there any way to dynamically go through the columns in this Temp table.

The 2 records represent Old and New Values from Table1 and Table1_audit.

I am building a string:

Select @.NewStr = Coalesce(convert(varchar(25),@.MeterIDNew),'null')
Select @.NewStr = ('New MeterID = ' + @.NewStr)
Select @.OldStr = Coalesce(convert(varchar(25),@.MeterIDOld),'null')
Select @.OldStr = ('Old Meter ID = ' + @.OldStr)
Select @.StrOutput = @.StrOutput + @.NewStr + @.OldStr + Char(10)

Select @.NewStr = Coalesce(convert(varchar(25),@.MV90IDNew),'null')
Select @.NewStr = ('New MV90 ID = ' + @.NewStr)
Select @.OldStr = Coalesce(convert(varchar(25),@.MV90IDOld),'null')
Select @.OldStr = ('Old MV90 ID = ' + @.OldStr)
Select @.StrOutput = @.StrOutput + @.NewStr + @.OldStr + Char(10)
.
.
.

To put in the body of an e-mail.

Sample
-----------------------
New SCADA ID = 58 Old SCADA ID = 58
New MeterID = null Old Meter ID = null
New MV90 ID = Entergy Old Meter ID = Entergy
New Name = TestNamejon Old Name = TestNamejon
------------------------

I need to do this for 10 tables. I am looking for a way to do this programitaclly so I do not have to create a stored procedure for each of these Tables. And if the table def changes I do not have to change the SP.

Thanks for the speedy reply
LJ|||try gettin the names of the table from sysobjects (usign a cursor) and make generic code for ur process which will use the tablenames from the names in sysobject.

so u can do sumthin for as many tables as u want. with just one sp

Currency Problem

I have a currency that needs to go into the Database. The column in the database is of type money.
When i try ctype or convert into integer, desimal or even sqlmoney type i get an error. Cannot convert data type nvarchar to data type money.

Can anyone help me with this Please.
Here is the code...

sqlcom = New SqlCommand("insert MeasureQuantities(MeasureQuantityName,MeasureQuantityDescrip,MeasureQuantityPrice) values(@.MeasureQuantityName,@.MeasureQuantityPrice,@.MeasureQuantityDescrip)", ocnn)
ocnn.Open()
Dim decPrice As System.Data.SqlTypes.SqlMoney
decPrice.ToDecimal()
decPrice = CType(MeasureQuantityPriceAdd.Text, System.Data.SqlTypes.SqlMoney)
sqlcom.Parameters.Add("@.MeasureQuantityName", MeasureQuantityNameAdd.Text)
sqlcom.Parameters.Add("@.MeasureQuantityPrice", decPrice) 'MeasureQuantityPriceAdd.Text, System.Data.SqlTypes.SqlMoney))
sqlcom.Parameters.Add("@.MeasureQuantityDescrip", MeasureQuantityDescripAdd.Text)
sqlcom.ExecuteNonQuery()What is the value of MeasureQuantityPriceAdd.Text? If the textbox is blank, you might be getting this error. Are you databinding the textbox on page load? If so, make sure you are checking if the page is a postback, and only populate it if the page is not a poatback.|||Thanks, the real problem was in the order of the variables in the update string.

Currency Column in Report Problem

I have a column in a report which displays an amount of currency. The currency is stored in sqlserver databse as 'Money' data type.

My problem is when I run the report it rounds off the value and does not disply to any decimal places. I want it to display the currency as 2 decimal places as this is how it is input and stored in the database.

anyone know how to do this?

macca

You could format the textbox like this:

http://msdn2.microsoft.com/en-us/library/ms251684(VS.80).aspx

Find more info here:

http://msdn2.microsoft.com/en-us/library/ms252073(VS.80).aspx

Currency

hi,
i would like to write a sql function which takes 3 parameters.
1. Source currency type
2. destination currency type
3. Amount to be converted
I need the dynamic rates.
Does anybody hav any idea about how to go with this.
Thanks in advance,
Vinu
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1Are you using SQL Server 2005 ? Therefore you could use Webservices to
call an currency service synchronously or async. by storing the data
in a currency table. That sure is possible with SQL 2000 but it MORE
easier with SQL 2005.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--

Culture in MSSQL 2005 express edition

i am trying to add xml to the xml data type column. The xml contains both
english and hebrew characters. English characters are saved well, but hebrew
saved as "''?" how can i fix it?Try converting the xml to varbinary(max) before inserting. From BOL:
Text Encoding
SQL Server 2005 stores XML data in Unicode (UTF-16). XML data retrieved from
the server comes out in UTF-16 encoding. If you want a different encoding,
you have to perform the required conversion on the retrieved data.
Sometimes, the XML data may be in a different encoding. If it is, you have
to use care during data loading. For example:
a.. If your text XML is in Unicode (UCS-2, UTF-16), you can assign it to
an XML column, variable, or parameter without any problems.
b.. If the encoding is not Unicode and is implicit, because of the source
code page, the string code page in the database should be the same as or
compatible with the code points that you want to load. If required, use
COLLATE. If no such server code page exists, you have to add an explicit XML
declaration with the correct encoding.
c.. To use an explicit encoding, use either the varbinary() type, which
has no interaction with code pages, or use a string type of the appropriate
code page. Then, assign the data to an XML column, variable, or parameter.
Example: Explicitly Specifying an Encoding
Assume that you have an XML document, vcdoc, stored as varchar(max) that
does not have an explicit XML declaration. The following statement adds an
XML declaration with the encoding "iso8859-1", concatenates the XML
document, casts the result to varbinary(max) so that the byte representation
is preserved, and then finally casts it to XML. This enables the XML
processor to parse the data according to the specified encoding "iso8859-1"
and generate the corresponding UTF-16 representation for string values.
SELECT CAST(
CAST (('<?xml version="1.0" encoding="iso8859-1"?>'+ vcdoc) AS VARBINARY
(MAX))
AS XML)
David Barber [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"michael" <michael@.discussions.microsoft.com> wrote in message
news:19B65A8D-E246-46EB-A85B-C517D1830BAD@.microsoft.com...
> i am trying to add xml to the xml data type column. The xml contains both
> english and hebrew characters. English characters are saved well, but
hebrew
> saved as "''?" how can i fix it?|||Thanks a lot
"David Barber [MS]" wrote:

> Try converting the xml to varbinary(max) before inserting. From BOL:
> Text Encoding
> SQL Server 2005 stores XML data in Unicode (UTF-16). XML data retrieved fr
om
> the server comes out in UTF-16 encoding. If you want a different encoding,
> you have to perform the required conversion on the retrieved data.
> Sometimes, the XML data may be in a different encoding. If it is, you have
> to use care during data loading. For example:
> a.. If your text XML is in Unicode (UCS-2, UTF-16), you can assign it to
> an XML column, variable, or parameter without any problems.
>
> b.. If the encoding is not Unicode and is implicit, because of the sourc
e
> code page, the string code page in the database should be the same as or
> compatible with the code points that you want to load. If required, use
> COLLATE. If no such server code page exists, you have to add an explicit X
ML
> declaration with the correct encoding.
>
> c.. To use an explicit encoding, use either the varbinary() type, which
> has no interaction with code pages, or use a string type of the appropriat
e
> code page. Then, assign the data to an XML column, variable, or parameter.
>
> Example: Explicitly Specifying an Encoding
> Assume that you have an XML document, vcdoc, stored as varchar(max) that
> does not have an explicit XML declaration. The following statement adds an
> XML declaration with the encoding "iso8859-1", concatenates the XML
> document, casts the result to varbinary(max) so that the byte representati
on
> is preserved, and then finally casts it to XML. This enables the XML
> processor to parse the data according to the specified encoding "iso8859-1
"
> and generate the corresponding UTF-16 representation for string values.
>
> SELECT CAST(
> CAST (('<?xml version="1.0" encoding="iso8859-1"?>'+ vcdoc) AS VARBINARY
> (MAX))
> AS XML)
>
> --
> David Barber [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "michael" <michael@.discussions.microsoft.com> wrote in message
> news:19B65A8D-E246-46EB-A85B-C517D1830BAD@.microsoft.com...
> hebrew
>
>

Cube Processing : Diff ''Tween "Database" and "Cube" Type?

In short, I notice that when creating a SSIS package for processing the cube, you can process the "database" and/or the "cube". Is it redundant to have both being processed in a SSIS task? I assume processing the "database" will also process the "cube"?

I assume that if I want everything to be refreshed, then I should simply process the database as the only step in the task, as well as making sure it's "Process Full" for the Processing Options.

Can anyone explain the different processing types?

Thanks!

If you're doing ProcessFull, then do ProcessFull on the database. ProcessFull on the database will process all dimensions then process all cubes.

If you did ProcessFull on the cube, then it will not process the dimensions (except the first time since they would be unprocessed the first time you process the cube). So all the new facts that point to new dimension members will either blow up or be assigned to the unknown member.

Anyway... ProcessFull on the database.

|||

Hi,

Depends on your setup but have a look at http://msdn2.microsoft.com/en-us/library/ms345142.aspx may help you.

And it is worth reading the performance guide as well

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

It is really up to you to decide what is best for you, we break it up into little parts. So if it does goes wrong, it is not going to roll back the entire transaction. Most of ours, unprocess, process dimensions, process a cube. Others are update dimensions, then process the cube. Never really use process database as there is often more than one cube in the database and i am only ever interested in one of the cubes.

Hope that helps

Matt

Cube Processing : Diff ''Tween "Database" and "Cube" Type?

In short, I notice that when creating a SSIS package for processing the cube, you can process the "database" and/or the "cube". Is it redundant to have both being processed in a SSIS task? I assume processing the "database" will also process the "cube"?

I assume that if I want everything to be refreshed, then I should simply process the database as the only step in the task, as well as making sure it's "Process Full" for the Processing Options.

Can anyone explain the different processing types?

Thanks!

If you're doing ProcessFull, then do ProcessFull on the database. ProcessFull on the database will process all dimensions then process all cubes.

If you did ProcessFull on the cube, then it will not process the dimensions (except the first time since they would be unprocessed the first time you process the cube). So all the new facts that point to new dimension members will either blow up or be assigned to the unknown member.

Anyway... ProcessFull on the database.

|||

Hi,

Depends on your setup but have a look at http://msdn2.microsoft.com/en-us/library/ms345142.aspx may help you.

And it is worth reading the performance guide as well

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

It is really up to you to decide what is best for you, we break it up into little parts. So if it does goes wrong, it is not going to roll back the entire transaction. Most of ours, unprocess, process dimensions, process a cube. Others are update dimensions, then process the cube. Never really use process database as there is often more than one cube in the database and i am only ever interested in one of the cubes.

Hope that helps

Matt

Cube Processing : Diff ''Tween "Database" and "Cube" Type?

In short, I notice that when creating a SSIS package for processing the cube, you can process the "database" and/or the "cube". Is it redundant to have both being processed in a SSIS task? I assume processing the "database" will also process the "cube"?

I assume that if I want everything to be refreshed, then I should simply process the database as the only step in the task, as well as making sure it's "Process Full" for the Processing Options.

Can anyone explain the different processing types?

Thanks!

If you're doing ProcessFull, then do ProcessFull on the database. ProcessFull on the database will process all dimensions then process all cubes.

If you did ProcessFull on the cube, then it will not process the dimensions (except the first time since they would be unprocessed the first time you process the cube). So all the new facts that point to new dimension members will either blow up or be assigned to the unknown member.

Anyway... ProcessFull on the database.

|||

Hi,

Depends on your setup but have a look at http://msdn2.microsoft.com/en-us/library/ms345142.aspx may help you.

And it is worth reading the performance guide as well

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

It is really up to you to decide what is best for you, we break it up into little parts. So if it does goes wrong, it is not going to roll back the entire transaction. Most of ours, unprocess, process dimensions, process a cube. Others are update dimensions, then process the cube. Never really use process database as there is often more than one cube in the database and i am only ever interested in one of the cubes.

Hope that helps

Matt

Cube Actions, how can I create a Command line action

In Analysis 2000 there was a command line action type, how can I do the same action in Analysis Services 2005?

Analysis 2000 command line action:

Defines an MDX statement that can be executed as a command line and displays the contents of the current directory:

"cmd /k dir"

Peter,

The DDL for AS2K5 still supports HTM and Command Line actions, but they are not exposed in the Business Intelligence Development Studio (BIDS). One work around that I have used is to create a URL action in BIDS and then save the cube definition and deploy it. Then you use SQL Server Management Studio to generate an ALTER CUBE script to an XMLA query window. Do a search on the script to find your action and then just change the type as show here:

Original script (segment with action definition)

<Action xsi:type="StandardAction">

<ID>Action</ID>

<Name>Command Line Action</Name>

<TargetType>Cells</TargetType>

<Type>URL</Type>

<Expression>"cmd /k dir"</Expression>

</Action>

Modified script

<Action xsi:type="StandardAction">

<ID>Action</ID>

<Name>Command Line Action</Name>

<TargetType>Cells</TargetType>

<Type>CommandLine</Type>

<Expression>"cmd /k dir"</Expression>

</Action>

Then just execute your script and the action type will be set to Command Line.

HTH,

- Steve

|||

Steve,

When I modify the script It still not works?

<Action xsi:type="StandardAction" dwd:design-time-name="f542b1b2-b2b6-4bf8-9903-63150db6ed15">
<ID>Action 1</ID>
<Name>CMD</Name>
<Description></Description>
<Caption></Caption>
<TargetType>Cells</TargetType>
<Target></Target>
<Condition></Condition>
<Type>CommandLine</Type>
<Application></Application>
<Expression>"cmd /k /dir"</Expression>
</Action>

When I change it back into URL the action works fine.

<Action xsi:type="StandardAction" dwd:design-time-name="f542b1b2-b2b6-4bf8-9903-63150db6ed15">
<ID>Action 1</ID>
<Name>CMD</Name>
<Description></Description>
<Caption></Caption>
<TargetType>Cells</TargetType>
<Target></Target>
<Condition></Condition>
<Type>Url</Type>
<Application></Application>
<Expression>"http://www.mywebsite.com"</Expression>
</Action>

|||

Peter,

I believe the problem is in the "Expression" you supplied to the command line action. It should be "cmd /k dir" and you have "cmd /k /dir"

<Expression>"cmd /k /dir"</Expression>

CTE and data type

I have a recursive CTE and within it is a concatenated string field. It fails with this message

Msg 240, Level 16, State 1, Procedure spGetBill, Line 14

Types don't match between the anchor and the recursive part in column "BID" of recursive query "ExplodedParts".

Here is the T-Sql:

WITH ExplodedParts (PID, CID, BID, PBOMID)

AS (SELECT ParentID, ChildID, convert(varchar,ParentID) as BID, PBOMID

FROM dbo.vwLatestPBOMIDs

WHERE ParentID=@.ParentID

UNION ALL

SELECT NextLevel.ParentID, NextLevel.ChildID, EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID, NextLevel.PBOMID

FROM dbo.vwLatestPBOMIDs AS NextLevel INNER JOIN

ExplodedParts AS EP ON NextLevel.ParentID = EP.CID)

SELECT PID, CID, BID, PBOMId

FROM ExplodedParts AS ExplodedParts_1

ORDER BY PID

The offending Sql is "EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID", which if I change to "convert(varchar,NextLevel.ParentID) AS BID" runs fine. I have tried using the Convert fn in the source view, no difference.

Does anyone have a clue why this is causing the datatype issue?

Any help is appreciated.

In anchor part try changing:

convert(varchar,ParentID) as BID

to:

convert(varchar(max),ParentID) as BID

|||

Change:

convert(varchar, ParentID)

to:

cast(ParentID as varchar(255)) -- or whatever maximum length you need

And change:

EP.PID + '.' + convert(varchar,NextLevel.ParentID)

to:

cast(EP.PID + '.' + convert(varchar,NextLevel.ParentID) as varchar(255))

By default if you don't specify the length for varchar/char/nchar/nvarchar/varbinary/binary in convert, the resulting length will be 30. So your anchor member column is of length 30 whereas the recursive member is 30 + 1 + length(PID). It is good rule to always use explicit cast around expressions so that you know the resulting type and this is more important to follow with recursive CTEs.

|||Thank you, that solved the issue.

CTE and data type

I have a recursive CTE and within it is a concatenated string field. It fails with this message

Msg 240, Level 16, State 1, Procedure spGetBill, Line 14

Types don't match between the anchor and the recursive part in column "BID" of recursive query "ExplodedParts".

Here is the T-Sql:

WITH ExplodedParts (PID, CID, BID, PBOMID)

AS (SELECT ParentID, ChildID, convert(varchar,ParentID) as BID, PBOMID

FROM dbo.vwLatestPBOMIDs

WHERE ParentID=@.ParentID

UNION ALL

SELECT NextLevel.ParentID, NextLevel.ChildID, EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID, NextLevel.PBOMID

FROM dbo.vwLatestPBOMIDs AS NextLevel INNER JOIN

ExplodedParts AS EP ON NextLevel.ParentID = EP.CID)

SELECT PID, CID, BID, PBOMId

FROM ExplodedParts AS ExplodedParts_1

ORDER BY PID

The offending Sql is "EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID", which if I change to "convert(varchar,NextLevel.ParentID) AS BID" runs fine. I have tried using the Convert fn in the source view, no difference.

Does anyone have a clue why this is causing the datatype issue?

Any help is appreciated.

In anchor part try changing:

convert(varchar,ParentID) as BID

to:

convert(varchar(max),ParentID) as BID

|||

Change:

convert(varchar, ParentID)

to:

cast(ParentID as varchar(255)) -- or whatever maximum length you need

And change:

EP.PID + '.' + convert(varchar,NextLevel.ParentID)

to:

cast(EP.PID + '.' + convert(varchar,NextLevel.ParentID) as varchar(255))

By default if you don't specify the length for varchar/char/nchar/nvarchar/varbinary/binary in convert, the resulting length will be 30. So your anchor member column is of length 30 whereas the recursive member is 30 + 1 + length(PID). It is good rule to always use explicit cast around expressions so that you know the resulting type and this is more important to follow with recursive CTEs.

|||Thank you, that solved the issue.