Current Time of SQL-Server

Hi

is there a "SQL string" to obtain date and time of a SQLServer machine ?

thank uOn Mon, 15 Sep 2003 17:45:48 GMT in comp.databases.ms-sqlserver,
"Solli" <fabioslm@.tiscali.it> wrote:

>Hi
>is there a "SQL string" to obtain date and time of a SQLServer machine ?

GetDate()

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)

Currency Exporting to Excel

Hi,
In one of my reports I have some fields that are currency fields displayed
with the currency format string (C). However, when I export these to Excel
(2003) I get the little yellow warning that says Number stored as text. And
if I try to do a sum by dragging across the cells and clicking the sigma
button at the top, it causes it to put an odd formula in the top row.
Is there an easy fix so that #'s exported as currency remain tied to excel
as currency?
Thanks,
ChrisI tried this on a matrix in RS2005 and it worked OK.
The format was c0. I do remember it being an issue in 2000. I don't
think there was a way round it in the report definition.
The only thing you could try is, in Excel, highlight the column and
format the cell to currency. If this works you will be able to do a SUM.
Cheers
Chris
Chris Taylor wrote:
> Hi,
> In one of my reports I have some fields that are currency fields
> displayed with the currency format string (C). However, when I
> export these to Excel (2003) I get the little yellow warning that
> says Number stored as text. And if I try to do a sum by dragging
> across the cells and clicking the sigma button at the top, it causes
> it to put an odd formula in the top row.
> Is there an easy fix so that #'s exported as currency remain tied to
> excel as currency?
> Thanks,
> Chris|||Tried formatting C0, stlil getting the text formatted as #.
MS any insight on this?
"Chris" <mr.mcgoo@.gmail.com> wrote in message
news:uPH9wVvQGHA.2704@.TK2MSFTNGP15.phx.gbl...
>I tried this on a matrix in RS2005 and it worked OK.
> The format was c0. I do remember it being an issue in 2000. I don't
> think there was a way round it in the report definition.
> The only thing you could try is, in Excel, highlight the column and
> format the cell to currency. If this works you will be able to do a SUM.
> Cheers
> Chris
>
> Chris Taylor wrote:
>> Hi,
>> In one of my reports I have some fields that are currency fields
>> displayed with the currency format string (C). However, when I
>> export these to Excel (2003) I get the little yellow warning that
>> says Number stored as text. And if I try to do a sum by dragging
>> across the cells and clicking the sigma button at the top, it causes
>> it to put an odd formula in the top row.
>> Is there an easy fix so that #'s exported as currency remain tied to
>> excel as currency?
>> Thanks,
>> Chris
>
> --
>|||Hi Chris,
Please modify the column width in Excel. I have tested in my side on RS
2005, it works fine.
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Nope. I just tried this; both FormatCurrency and Format c0 send a
number as text, causing Excel to just put a Smart Tag on it and ignore
it in functions. I know that converting numbers to text is the entire
purpose of those two functions, but making them the sole means of
formatting numbers makes the Excel export function about as useless as
nipples on the Batsuit.
Wei Lu: I think Chris Taylor means "text formatted as number" and not
"the column's too narrow, so Excel puts in pound signs to tell you to
resize the column."

Currency - not $

Hi

I have a cube with a sales amount measure. When I choose the format string to currency i get a $ infront of it when i browse the cube.

What should I do to get a ￡ infront or maybe SEK in the back (SEK = Swedish Kronor)

Thanks for answers

//Patrik

Try changing the format string to #,#.00 kr.

/Per

|||

Well, that worked.

But it seems to be a work around, and not the final solution. You must be able to select what type of currency the currency format code is.

/Patrik

|||

Yes, I agree. Should be possible - calculated members work fine.

Maybe Frederik knows - can ask him during the coffe break.

/Per

|||Change the language setting in the properties section for the cube in Business Intelligence Development Studio. Then you will get the correct regional display of the currencies.|||

Thanks man...that did it.

I think thats the only Language setting that I didn't change :P

//Patrik

Ctype from string to double not behaving as expected.

I tried posting this earlier, but it never showed up. Hopefully I don't
double post.
I was writing code for a custom dll earlier this morning where I ran into
some strange behavior for the IsNumeric function.
IsNumeric("$1,0562.00") was returning false
I thought that maybe this behavior was being cause by one of the "features"
of the IsNumeric function so I decided to do some more testing.
I created a web app on the same machine and ran the same code there
IsNumeric("$1,0562.00") was returning true with the web app.
I delved a little deeper and decided to research just what all IsNumeric
does to check for a numeric value. I didn't have to go far.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
states
"It also returns True if Expression is a String that can be successfully
converted to a Double"
So I decided to test the following code in each environment.
ctype("$1,0562.00",double)
Sure enough within the web app it worked fine, but within a reporting
services custom dll it threw an exception.
Both the instance of reporting services and the webapp are being run and
compiled on the same machine. They are both running under the 1.1 framework.
It would make more sense to me that the code didn't behave the way I thought
it should if it was consistently misbehaving. :-)
It's quite possible there is something I'm missing here, but I'm just trying
to figure out the reason for the inconsistency.
I'm running Reporting Services 2003 with sp2 installed.
Anyone have any ideas?
Thanks,
NathanThat is odd. I tried in SSRS 2005 preview window just for giggles, returned
true as expected.
=IsNumeric("$1,0562.00")
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:4B24FE69-BCA8-48CF-8ECC-40F8FCCC5448@.microsoft.com...
>I tried posting this earlier, but it never showed up. Hopefully I don't
> double post.
> I was writing code for a custom dll earlier this morning where I ran into
> some strange behavior for the IsNumeric function.
> IsNumeric("$1,0562.00") was returning false
> I thought that maybe this behavior was being cause by one of the
> "features"
> of the IsNumeric function so I decided to do some more testing.
> I created a web app on the same machine and ran the same code there
> IsNumeric("$1,0562.00") was returning true with the web app.
> I delved a little deeper and decided to research just what all IsNumeric
> does to check for a numeric value. I didn't have to go far.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
> states
> "It also returns True if Expression is a String that can be successfully
> converted to a Double"
> So I decided to test the following code in each environment.
> ctype("$1,0562.00",double)
> Sure enough within the web app it worked fine, but within a reporting
> services custom dll it threw an exception.
> Both the instance of reporting services and the webapp are being run and
> compiled on the same machine. They are both running under the 1.1
> framework.
> It would make more sense to me that the code didn't behave the way I
> thought
> it should if it was consistently misbehaving. :-)
> It's quite possible there is something I'm missing here, but I'm just
> trying
> to figure out the reason for the inconsistency.
> I'm running Reporting Services 2003 with sp2 installed.
> Anyone have any ideas?
> Thanks,
> Nathan
>|||Perhaps this was something that was fixed with 2005? Although I'm still not
ruling out that I'm missing something. When you say in the preview window do
you mean you used â'ctype("$1,0562.00",double)â' in the custom code window
there? Iâ'll have to give that a try and see if it has the same behavior as
calling it within a custom dll.
"Steve MunLeeuw" wrote:
> That is odd. I tried in SSRS 2005 preview window just for giggles, returned
> true as expected.
> =IsNumeric("$1,0562.00")
> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> news:4B24FE69-BCA8-48CF-8ECC-40F8FCCC5448@.microsoft.com...
> >I tried posting this earlier, but it never showed up. Hopefully I don't
> > double post.
> >
> > I was writing code for a custom dll earlier this morning where I ran into
> > some strange behavior for the IsNumeric function.
> > IsNumeric("$1,0562.00") was returning false
> > I thought that maybe this behavior was being cause by one of the
> > "features"
> > of the IsNumeric function so I decided to do some more testing.
> > I created a web app on the same machine and ran the same code there
> > IsNumeric("$1,0562.00") was returning true with the web app.
> >
> > I delved a little deeper and decided to research just what all IsNumeric
> > does to check for a numeric value. I didn't have to go far.
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
> > states
> > "It also returns True if Expression is a String that can be successfully
> > converted to a Double"
> >
> > So I decided to test the following code in each environment.
> > ctype("$1,0562.00",double)
> > Sure enough within the web app it worked fine, but within a reporting
> > services custom dll it threw an exception.
> >
> > Both the instance of reporting services and the webapp are being run and
> > compiled on the same machine. They are both running under the 1.1
> > framework.
> > It would make more sense to me that the code didn't behave the way I
> > thought
> > it should if it was consistently misbehaving. :-)
> > It's quite possible there is something I'm missing here, but I'm just
> > trying
> > to figure out the reason for the inconsistency.
> >
> > I'm running Reporting Services 2003 with sp2 installed.
> >
> > Anyone have any ideas?
> >
> > Thanks,
> >
> > Nathan
> >
>
>|||I just entered it as an expression in a textbox and clicked the Preview tab
in the Report Designer (VS IDE)
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:E814C5A0-E41D-4261-8A37-7ECB30C98D68@.microsoft.com...
> Perhaps this was something that was fixed with 2005? Although I'm still
> not
> ruling out that I'm missing something. When you say in the preview window
> do
> you mean you used "ctype("$1,0562.00",double)" in the custom code window
> there? I'll have to give that a try and see if it has the same behavior
> as
> calling it within a custom dll.
> "Steve MunLeeuw" wrote:
>> That is odd. I tried in SSRS 2005 preview window just for giggles,
>> returned
>> true as expected.
>> =IsNumeric("$1,0562.00")
>> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> news:4B24FE69-BCA8-48CF-8ECC-40F8FCCC5448@.microsoft.com...
>> >I tried posting this earlier, but it never showed up. Hopefully I don't
>> > double post.
>> >
>> > I was writing code for a custom dll earlier this morning where I ran
>> > into
>> > some strange behavior for the IsNumeric function.
>> > IsNumeric("$1,0562.00") was returning false
>> > I thought that maybe this behavior was being cause by one of the
>> > "features"
>> > of the IsNumeric function so I decided to do some more testing.
>> > I created a web app on the same machine and ran the same code there
>> > IsNumeric("$1,0562.00") was returning true with the web app.
>> >
>> > I delved a little deeper and decided to research just what all
>> > IsNumeric
>> > does to check for a numeric value. I didn't have to go far.
>> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
>> > states
>> > "It also returns True if Expression is a String that can be
>> > successfully
>> > converted to a Double"
>> >
>> > So I decided to test the following code in each environment.
>> > ctype("$1,0562.00",double)
>> > Sure enough within the web app it worked fine, but within a reporting
>> > services custom dll it threw an exception.
>> >
>> > Both the instance of reporting services and the webapp are being run
>> > and
>> > compiled on the same machine. They are both running under the 1.1
>> > framework.
>> > It would make more sense to me that the code didn't behave the way I
>> > thought
>> > it should if it was consistently misbehaving. :-)
>> > It's quite possible there is something I'm missing here, but I'm just
>> > trying
>> > to figure out the reason for the inconsistency.
>> >
>> > I'm running Reporting Services 2003 with sp2 installed.
>> >
>> > Anyone have any ideas?
>> >
>> > Thanks,
>> >
>> > Nathan
>> >
>>|||Putting the "ctype("$1,0562.00",double)" directly in the cell on a page seems
to work fine. It seems to be when the code is run in a custom dll that the
problem occurs.
Can anyone else reproduce this?
Thanks,
-Nathan
"Steve MunLeeuw" wrote:
> I just entered it as an expression in a textbox and clicked the Preview tab
> in the Report Designer (VS IDE)
>
> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> news:E814C5A0-E41D-4261-8A37-7ECB30C98D68@.microsoft.com...
> > Perhaps this was something that was fixed with 2005? Although I'm still
> > not
> > ruling out that I'm missing something. When you say in the preview window
> > do
> > you mean you used "ctype("$1,0562.00",double)" in the custom code window
> > there? I'll have to give that a try and see if it has the same behavior
> > as
> > calling it within a custom dll.
> > "Steve MunLeeuw" wrote:
> >
> >> That is odd. I tried in SSRS 2005 preview window just for giggles,
> >> returned
> >> true as expected.
> >> =IsNumeric("$1,0562.00")
> >>
> >> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> >> news:4B24FE69-BCA8-48CF-8ECC-40F8FCCC5448@.microsoft.com...
> >> >I tried posting this earlier, but it never showed up. Hopefully I don't
> >> > double post.
> >> >
> >> > I was writing code for a custom dll earlier this morning where I ran
> >> > into
> >> > some strange behavior for the IsNumeric function.
> >> > IsNumeric("$1,0562.00") was returning false
> >> > I thought that maybe this behavior was being cause by one of the
> >> > "features"
> >> > of the IsNumeric function so I decided to do some more testing.
> >> > I created a web app on the same machine and ran the same code there
> >> > IsNumeric("$1,0562.00") was returning true with the web app.
> >> >
> >> > I delved a little deeper and decided to research just what all
> >> > IsNumeric
> >> > does to check for a numeric value. I didn't have to go far.
> >> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
> >> > states
> >> > "It also returns True if Expression is a String that can be
> >> > successfully
> >> > converted to a Double"
> >> >
> >> > So I decided to test the following code in each environment.
> >> > ctype("$1,0562.00",double)
> >> > Sure enough within the web app it worked fine, but within a reporting
> >> > services custom dll it threw an exception.
> >> >
> >> > Both the instance of reporting services and the webapp are being run
> >> > and
> >> > compiled on the same machine. They are both running under the 1.1
> >> > framework.
> >> > It would make more sense to me that the code didn't behave the way I
> >> > thought
> >> > it should if it was consistently misbehaving. :-)
> >> > It's quite possible there is something I'm missing here, but I'm just
> >> > trying
> >> > to figure out the reason for the inconsistency.
> >> >
> >> > I'm running Reporting Services 2003 with sp2 installed.
> >> >
> >> > Anyone have any ideas?
> >> >
> >> > Thanks,
> >> >
> >> > Nathan
> >> >
> >>
> >>
> >>
>
>|||Sorry about that, I missed the whole point.
I added the following method to a custom assembly and referenced this in a
2005 report and used the preview window. I was going to try the same in VS
2003, but my 2.0 assembly doesn't appear that it will work with the 1.1
framework in 2003. My custom assembly is in C#, referenced the VB.Net dll
to get the IsNumeric function.
public bool NewsGroup_CtypeFromString()
{
return Microsoft.VisualBasic.Information.IsNumeric("$1,0562.00");
}
Expression:
=Code.loc.NewsGroup_CtypeFromString
Returns True
Steve MunLeeuw
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:AC81C8D6-6E64-4867-A15E-72C034F3F511@.microsoft.com...
> Putting the "ctype("$1,0562.00",double)" directly in the cell on a page
> seems
> to work fine. It seems to be when the code is run in a custom dll that
> the
> problem occurs.
> Can anyone else reproduce this?
> Thanks,
> -Nathan
> "Steve MunLeeuw" wrote:
>> I just entered it as an expression in a textbox and clicked the Preview
>> tab
>> in the Report Designer (VS IDE)
>>
>> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> news:E814C5A0-E41D-4261-8A37-7ECB30C98D68@.microsoft.com...
>> > Perhaps this was something that was fixed with 2005? Although I'm
>> > still
>> > not
>> > ruling out that I'm missing something. When you say in the preview
>> > window
>> > do
>> > you mean you used "ctype("$1,0562.00",double)" in the custom code
>> > window
>> > there? I'll have to give that a try and see if it has the same
>> > behavior
>> > as
>> > calling it within a custom dll.
>> > "Steve MunLeeuw" wrote:
>> >
>> >> That is odd. I tried in SSRS 2005 preview window just for giggles,
>> >> returned
>> >> true as expected.
>> >> =IsNumeric("$1,0562.00")
>> >>
>> >> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> >> news:4B24FE69-BCA8-48CF-8ECC-40F8FCCC5448@.microsoft.com...
>> >> >I tried posting this earlier, but it never showed up. Hopefully I
>> >> >don't
>> >> > double post.
>> >> >
>> >> > I was writing code for a custom dll earlier this morning where I ran
>> >> > into
>> >> > some strange behavior for the IsNumeric function.
>> >> > IsNumeric("$1,0562.00") was returning false
>> >> > I thought that maybe this behavior was being cause by one of the
>> >> > "features"
>> >> > of the IsNumeric function so I decided to do some more testing.
>> >> > I created a web app on the same machine and ran the same code there
>> >> > IsNumeric("$1,0562.00") was returning true with the web app.
>> >> >
>> >> > I delved a little deeper and decided to research just what all
>> >> > IsNumeric
>> >> > does to check for a numeric value. I didn't have to go far.
>> >> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
>> >> > states
>> >> > "It also returns True if Expression is a String that can be
>> >> > successfully
>> >> > converted to a Double"
>> >> >
>> >> > So I decided to test the following code in each environment.
>> >> > ctype("$1,0562.00",double)
>> >> > Sure enough within the web app it worked fine, but within a
>> >> > reporting
>> >> > services custom dll it threw an exception.
>> >> >
>> >> > Both the instance of reporting services and the webapp are being run
>> >> > and
>> >> > compiled on the same machine. They are both running under the 1.1
>> >> > framework.
>> >> > It would make more sense to me that the code didn't behave the way I
>> >> > thought
>> >> > it should if it was consistently misbehaving. :-)
>> >> > It's quite possible there is something I'm missing here, but I'm
>> >> > just
>> >> > trying
>> >> > to figure out the reason for the inconsistency.
>> >> >
>> >> > I'm running Reporting Services 2003 with sp2 installed.
>> >> >
>> >> > Anyone have any ideas?
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Nathan
>> >> >
>> >>
>> >>
>> >>
>>|||Got off my lazy horse and tried in VS 2003, I don't have VB installed, I
think using regular expressions would defeat the whole purpose of your case.
Maybe using RegExpr instead of IsNumeric would be a valid workaround?
sorry, C#
deonvs said:
Even easier:
static bool IsNumeric(string inputString)
{
return Regex.IsMatch(inputString, "^[0-9]+$");
}
Reference: http://dotnet.org.za/deonvs/archive/2004/07/06/2579.aspx
Steve MunLeeuw
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:AC81C8D6-6E64-4867-A15E-72C034F3F511@.microsoft.com...
> Putting the "ctype("$1,0562.00",double)" directly in the cell on a page
> seems
> to work fine. It seems to be when the code is run in a custom dll that
> the
> problem occurs.
> Can anyone else reproduce this?
> Thanks,
> -Nathan
> "Steve MunLeeuw" wrote:
>> I just entered it as an expression in a textbox and clicked the Preview
>> tab
>> in the Report Designer (VS IDE)
>>
>> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> news:E814C5A0-E41D-4261-8A37-7ECB30C98D68@.microsoft.com...
>> > Perhaps this was something that was fixed with 2005? Although I'm
>> > still
>> > not
>> > ruling out that I'm missing something. When you say in the preview
>> > window
>> > do
>> > you mean you used "ctype("$1,0562.00",double)" in the custom code
>> > window
>> > there? I'll have to give that a try and see if it has the same
>> > behavior
>> > as
>> > calling it within a custom dll.
>> > "Steve MunLeeuw" wrote:
>> >
>> >> That is odd. I tried in SSRS 2005 preview window just for giggles,
>> >> returned
>> >> true as expected.
>> >> =IsNumeric("$1,0562.00")
>> >>
>> >> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> >> news:4B24FE69-BCA8-48CF-8ECC-40F8FCCC5448@.microsoft.com...
>> >> >I tried posting this earlier, but it never showed up. Hopefully I
>> >> >don't
>> >> > double post.
>> >> >
>> >> > I was writing code for a custom dll earlier this morning where I ran
>> >> > into
>> >> > some strange behavior for the IsNumeric function.
>> >> > IsNumeric("$1,0562.00") was returning false
>> >> > I thought that maybe this behavior was being cause by one of the
>> >> > "features"
>> >> > of the IsNumeric function so I decided to do some more testing.
>> >> > I created a web app on the same machine and ran the same code there
>> >> > IsNumeric("$1,0562.00") was returning true with the web app.
>> >> >
>> >> > I delved a little deeper and decided to research just what all
>> >> > IsNumeric
>> >> > does to check for a numeric value. I didn't have to go far.
>> >> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
>> >> > states
>> >> > "It also returns True if Expression is a String that can be
>> >> > successfully
>> >> > converted to a Double"
>> >> >
>> >> > So I decided to test the following code in each environment.
>> >> > ctype("$1,0562.00",double)
>> >> > Sure enough within the web app it worked fine, but within a
>> >> > reporting
>> >> > services custom dll it threw an exception.
>> >> >
>> >> > Both the instance of reporting services and the webapp are being run
>> >> > and
>> >> > compiled on the same machine. They are both running under the 1.1
>> >> > framework.
>> >> > It would make more sense to me that the code didn't behave the way I
>> >> > thought
>> >> > it should if it was consistently misbehaving. :-)
>> >> > It's quite possible there is something I'm missing here, but I'm
>> >> > just
>> >> > trying
>> >> > to figure out the reason for the inconsistency.
>> >> >
>> >> > I'm running Reporting Services 2003 with sp2 installed.
>> >> >
>> >> > Anyone have any ideas?
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Nathan
>> >> >
>> >>
>> >>
>> >>
>>

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.

CSV string as a SQL In Parameter

Hi Guys,

I am having SQL query whith "IN"

SELECT * FROM Table1 Where ID in ( 1, 2,3)

how can i pass '1,2,3' as a SP parameter.

Thanks

There is a simple way to resolve this
exec ('select *from tblwhere id in (' + @.csv + ')' )
 
where @.csv is your parameter with comma sparated value.
might this helps you.
 
Thanks
 

|||

Hi, 

There is a simple way to resolve this

exec ('select * from tbl where id in ( ' + @.csv + ')' )

where @.csv is your parameter with comma sparated value.

might this helps you.

Thanks

CSV String

Hi,

I have a variable like @.states=NY,VA,AL,CA.

Now i want to use the above string in the IN clause.How to separate those string and use.

Select * from xxx where @.State IN('NY','VA','AL','CA')

Thanks in advance

Here are a couple of pretty good references:

http://www.sommarskog.se/arrays-in-sql.html
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

csv file

how to set the connection string to connect a csv file with reporting
service.
TIA.
DonaldI have not used csv files, but you should be able to setup an ODBC datasource
with the text driver. You can then use this source to use your csv file as
table. I have successfully done this with txt files. You might need to
rename the files to be .txt versus .csv.
Good Luck
Stan
"Donald" wrote:
> how to set the connection string to connect a csv file with reporting
> service.
> TIA.
> Donald
>
>

CSV export with single quotations for string fields

Hi

I need to export data to CSV file from Sql Reporting Services.

I am including single quotations in my view to display in SRS for string fields.

After export if we open in Notepad, for string field it is adding multiple quotations.

How to add single quotations for string fields - CSV files,

Please let me know if anybody knows.

Thx

Vijji

Yes. You can specify this value (it's called the "Qualifier") for the CSV renderer -- see http://msdn2.microsoft.com/en-us/library/ms155365.aspx

-- and I believe you can set the default for the value in the rsreportserver.config file as well -- see http://msdn2.microsoft.com/en-us/library/ms156281.aspx

>L<

|||

Hi Lisa Nicholls

Thank you so much for the response,

I changed in the config file as below

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
<NoHeader>True</NoHeader>
<Qualifier>False</Qualifier>
</DeviceInfo>
</Configuration>
</Extension>

It is working fine.

I have another issue, any suggestions will be very helpful.

In my data string field having comma, while importing it is treating as seperate column.

How do we control with in the string comma values.

Thank you again

Vijji

|||

hi there,

This is on the same page of the docs I've already sent you to <s>. Use a different value for FieldDelimiter along with Qualifier, if your data contains a comma.

(Actually I think that they got the terms backwards -- it should be that the item for which the RS default is a comma is termed a "Field Separator" and the item for which the RS default is quotation mark is called the "Field Delimiter" -- but you get the idea <s>.)

HTH,

>L<

CSV

Hi,

I have a variable like @.states=NY,VA,AL,CA.

Now i want to use the above string in the IN clause.How to separate those string and use.

Select * from xxx where @.State IN('NY','VA','AL','CA')

Thanks in advance

Create Function dbo.ListOfvalues(@.values varchar(8000),@.delimeter char) Returns @.list table (Item varchar(1000))
as
Begin
While CharIndex(@.delimeter,@.values) >0
Begin
Insert Into @.list
Select Ltrim(RTrim(Left(@.values,CharIndex(@.delimeter,@.values)-1)))
Select @.values=RTRIM(Ltrim(Right(@.values,len(@.values)-CharIndex(@.delimeter,@.values))))
End
Insert Into @.list
Select @.values
Return;
End

go

Set @.States = 'NY,VA,AL,CA'

Select * from xxx where State IN (Select item from dbo.ListOfvalues(@.States,','))

|||

Thank you very much.