Adding a leading character to a string

I'm looking for a string function (or any other quick way) for adding a leading zero to make a string two characters long. For example, if the input is '12' the result will be '12' but if the input is '1', the result will be '01'.

It's actually about making a month number two characters long but I've understood there is no way to make Datediff() to fix it for me.

I thought there might be a string function for this, like there is in many programming languages, but I can't find anything in BOL. I want to keep it simple, because it will be used in a Select and a Group By for aggregating data based on time intervals (year + month).

And, I assume I can't make Datepart() to return year + month directly, only one of the parts at a time.SELECT Convert(CHAR(7), GetDate(), 121)
Use the date expresion of your choice instead of GetDate()

-PatP|||I use this ;)

create function dbo.FN_INT_TOSTRING(@.codigo int, @.length int) returns varchar(10)
begin
declare @.res varchar(10)
set @.res = cast(@.codigo as varchar)
while len(@.res) < @.length
begin
set @.res = '0' + @.res
end
return @.res
end|||What about a generic function like:

Right('0' + cast(@.MyInt as varchar(2)), 2)

But Pat really has the best answer here.

Regards,

hmscott|||Thanks everyone. Simpler than I first thought! I'll play with it at work on Monday morning (9 PM over here now).

PS. Sorry for replying from another alias - I now realize I'm logged on as Coolberg from my work and as Nabucco from my home computer. I'll correct that. ;-)
DS.|||So finding an answer at 21:00 on Friday night doesn't inspire you to run right down to the office to try it ? Well, what kind of geek are you anyway ? Next thing you know, you'll be telling us that you're going to enjoy a cold beer and a warm bed!

-PatP|||select convert(varchar(2),getdate(),101)|||> Next thing you know, you'll be telling us that you're going to enjoy a cold beer and a warm bed!

I missed the beer; the beer shop here closes at 6 PM ;-)|||select convert(varchar(2),getdate(),101)

I'll test this one tomorrow.

By the way, SELECT Convert(CHAR(7), GetDate(), 121) didn't work.

I tried Right('0' + cast(@.MyInt as varchar(2)), 2) today, it worked fine.|||waddyamean it didn't work...of course it worked...read the hint sticky at the top of the forum|||The problem is, I'm doing a (for example)

select convert(varchar(2),datepart(mm,'2006-01-30'),101)

where you'll still get "1" since datepart() returns "1" , not "01".|||select right(convert(char(7),yourdate,120),2)|||And, I assume I can't make Datepart() to return year + month directly, only one of the parts at a time.select convert(char(7),yourdate,120)|||Select convert(varchar(2),convert(datetime,'2006-01-30'),101)|||Thanks everybody!

add_months function

Hi - I am running an SQL on my oracle database.

I have used the add_months function in my select statement and that is all working fine.

I now want a parameter to show me only rows where the result of the add_months function is 1st July.

I am getting an error message "ORA-01841: (full) year must be between -4713 and +9999, and not be 0". Does anyone know the exact format of how I should enter my date? I have tried a few different ways.

SQL is as follows

select acc_account_no,acc_term_band_start_date,acc_term_i nterval,add_months(acc_term_band_start_date,(acc_t erm_interval*12))
from accounts
where add_months(acc_term_band_start_date,(acc_term_inte rval*12) = '01-JUL-2003'
;

Any advise would be appreciated.
Regards,
BethOriginally posted by elisabeth
Hi - I am running an SQL on my oracle database.

I have used the add_months function in my select statement and that is all working fine.

I now want a parameter to show me only rows where the result of the add_months function is 1st July.

I am getting an error message "ORA-01841: (full) year must be between -4713 and +9999, and not be 0". Does anyone know the exact format of how I should enter my date? I have tried a few different ways.

SQL is as follows

select acc_account_no,acc_term_band_start_date,acc_term_i nterval,add_months(acc_term_band_start_date,(acc_t erm_interval*12))
from accounts
where add_months(acc_term_band_start_date,(acc_term_inte rval*12) = '01-JUL-2003'
;

Any advise would be appreciated.
Regards,
Beth
Never use a character string literal like '01-JUL-2003' where a DATE is required. Instead, use TO_DATE with an explicit format mask to properly convert it to a date:

TO_DATE('01-JUL-2003','DD-MON-YYYY')

add working days to a date

I want to be able to pass a date to a function and add 4 days to it. The
quirk is that it needs to be 4 working days ie. Mon - Fri excl holidays.
so
I have a table of Holidays with 2 records in 2/1/2006 (2nd Jan) and 3/1/2005
(3rd Jan)
The 31/12/05 and 1/1/06 are a Sat and Sun
I have a date 30/12/2005 to which I want to add 4 working days (this to be
variable) therefore the date returned would be 9/1/06 (9th Jan)
In vb I made use of the Wday function and DLookup
How would I do the same thing in SQL
Thanks
CREATE TABLE [dbo].[tblHols] (
[Holiday] [datetime] NOT NULL
) ON [PRIMARY]
GO>I want to be able to pass a date to a function and add 4 days to it. The
>quirk is that it needs to be 4 working days ie. Mon - Fri excl holidays.
Your best bet is a calendar table. Please see http://www.aspfaq.com/2519
for a thorough treatment (I think one of the examples even shows you how to
estimate delivery date, which sounds pretty much like what you're asking
for).|||I have created the calendar table as suggested but the select query takes 43
secs to run. How could I speed this up? A similar function in VB takes far
less time.
Here are the scripts
CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED,
isWday BIT,
isHoliday BIT,
Y SMALLINT,
FY SMALLINT,
Q TINYINT,
M TINYINT,
D TINYINT,
DW TINYINT,
monthname VARCHAR(9),
dayname VARCHAR(9),
W TINYINT
)
GO
SET NOCOUNT ON
DECLARE @.dt SMALLDATETIME
SET @.dt = '20060101'
WHILE @.dt < '20300101'
BEGIN
INSERT dbo.Calendar(dt) SELECT @.dt
SET @.dt = @.dt + 1
END
UPDATE dbo.Calendar SET
isWday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
isHoliday = 0
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 1
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 2
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 3
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 4
Declare @.dte datetime
SET @.dte = '20060101'
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23nltssPAGHA.2984@.TK2MSFTNGP09.phx.gbl...
> Your best bet is a calendar table. Please see http://www.aspfaq.com/2519
> for a thorough treatment (I think one of the examples even shows you how
> to estimate delivery date, which sounds pretty much like what you're
> asking for).
>|||On Thu, 15 Dec 2005 09:52:05 -0000, Newbie wrote:

>I have created the calendar table as suggested but the select query takes 4
3
>secs to run. How could I speed this up? A similar function in VB takes fa
r
>less time.
Hi Newbie,
Just to clarify: you ARE aware that the Calendar table should be a
permanent one, aren't you? Create and populate it once, then just use
it. Don't drop after use and re-create before the next use.

>Declare @.dte datetime
>SET @.dte = '20060101'
>SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
To speed this up, try this modification:
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
For a real speed gain, find a reasonable ratio for non-business days vs
total days and round up to be on the safe side. To be on the safe side,
I'll use a ratio of 1 to 2:
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 9 * 2, @.dte)
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Having just posted a different project regarding dates, I thought I would gi
ve this a shoot. It turned into more work then expected, but should be nice
for a toolbox. This solution appears to run in the millisecond range and I
could probably get it faster. Currently, for holidays, I am calculating th
em each call. I could cache using a hashtable or something, but the speed s
till seems to be ok in any event. Attached is a class that contains many bus
iness date functions such as:
public static DateTime AddQuarters(DateTime date, int quarters);
public static DateTime AddWorkDays(DateTime startDate, int workDays, Holiday
[] holidays);
public static DateTime AddWorkDays(DateTime startDate, int workDays, DateTim
e[] holidays);
public static DateTime AddWorkDays(DateTime startDate, int workDays, bool ho
lidaysAreWorkDays);
public static string DaysHoursMinutesSecondsMilliseconds(Date
Time start, Dat
eTime end);
public static DateTime[] GetDayOfWInMonth(DateTime date, DayOfW[] days
OfTheW);
public static DateTime[] GetDayOfWInMonth(DateTime date, DayOfW dayOfW
);
public static DateTime[] GetDayOfWInRange(DateTime startDate, DateTime en
dDate, DayOfW dayOfW);
public static DateTime[] GetDayOfWInRange(DateTime startDate, DateTime en
dDate, DayOfW[] daysOfW);
public static DateTime GetEndOf(PeriodType periodType, DateTime date);
public static DateTime GetEndOfDay(DateTime date);
public static DateTime GetEndOfMinute(DateTime date);
public static DateTime GetEndOfMonth(DateTime date);
public static DateTime GetEndOfQuarter(DateTime date);
public static DateTime GetEndOfQuarter(int year, int quarter);
public static DateTime GetEndOfW(DateTime date);
public static DateTime GetEndOfYear(DateTime date);
public static DateTime GetEndOfYear(int Year);
public static Holiday[] GetHolidays(int year);
public static Holiday[] GetHolidays(DateTime startDate, DateTime endDate);
private static DateTime GetNextQuarter(DateTime date);
private static DateTime GetPriorQuarter(DateTime date);
public static int GetQuarter(DateTime date);
public static DateTime GetStartOf(PeriodType periodType, DateTime date);
public static DateTime GetStartOfDay(DateTime date);
public static DateTime GetStartOfMinute(DateTime date);
public static DateTime GetStartOfMonth(DateTime date);
public static DateTime GetStartOfQuarter(DateTime date);
public static DateTime GetStartOfQuarter(int year, int quarter);
public static DateTime GetStartOfW(DateTime date);
public static DateTime GetStartOfYear(DateTime date);
public static DateTime GetStartOfYear(int Year);
public static DateTime[] GetWorkDaysInRange(DateTime startDate, DateTime end
Date, Holiday[] holidays);
public static DateTime[] GetWorkDaysInRange(DateTime startDate, DateTime end
Date, DateTime[] holidays);
public static DateTime[] GetWorkDaysInRange(DateTime startDate, DateTime end
Date, bool holidaysAreWorkDays);
public static string HoursAndMinutes(DateTime start, DateTime end);
public static bool IsBetweenDay(DateTime date, DateTime day);
public static bool IsBetweenDays(DateTime date, DateTime startDate, DateTime
endDate);
public static bool IsBetweenMonth(DateTime date, DateTime month);
public static bool IsBetweenQuarter(DateTime date, int quarter);
public static bool IsBetweenQuarter(DateTime date, int year, int quarter);
public static bool IsHoliday(DateTime date);
public static bool IsHoliday(DateTime date, Holiday[] holidays);
public static bool IsHoliday(DateTime date, DateTime[] holidays);
public static bool IsWorkDay(DateTime date);
// The following is a CLR UDF that calls this class for AddWorkDays. This e
xample udf calls DateFunctions.AddWorkDays() and skips any of the 10 Federal
holidays. You could also add your own holidays (i.e. non-work days) add ca
ll a different overload. Other UDFs that call the different static methods
on the DateFunctions class could be written as well.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlUtils;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static DateTime AddWorkDays(DateTime date, int workDays)
{
DateTime start = DateTime.Now;
DateTime day = DateFunctions.AddWorkDays(date, workDays, false);
DateTime end = DateTime.Now;
TimeSpan ts = end - start;
Console.WriteLine(ts.ToString());
return day;
}
};
Please let me know if you have any questions. I still need more testing on
everything, but the AddWorkDays seems to be working pretty well. Let me kno
w if you questions. Cheers!
--
William Stacey [MVP]|||Brilliant! It now takes less than 1 second when using the second mod
Thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:o0v3q1t8ckqoo7em98ull7k3s0vnrglksg@.
4ax.com...
> On Thu, 15 Dec 2005 09:52:05 -0000, Newbie wrote:
>
> Hi Newbie,
> Just to clarify: you ARE aware that the Calendar table should be a
> permanent one, aren't you? Create and populate it once, then just use
> it. Don't drop after use and re-create before the next use.
>
> To speed this up, try this modification:
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
>
> For a real speed gain, find a reasonable ratio for non-business days vs
> total days and round up to be on the safe side. To be on the safe side,
> I'll use a ratio of 1 to 2:
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 9 * 2, @.dte)
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is another fun little thing you can do with the lib. Print a calendar o
f any month(s). This basically works by enumerating the Calendar ws for a
ny month using GetMonthCalendarWs as shown in code below:
Output
---
January 2006
SU MO TU WE TH FR SA
01 02 03 04 05 06 07
08 09 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
February 2006
SU MO TU WE TH FR SA
01 02 03 04
05 06 07 08 09 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28
March 2006
SU MO TU WE TH FR SA
01 02 03 04
05 06 07 08 09 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
...
// Put in windows or console app. Reference the sqlutils.dll and run.
private void button2_Click(object sender, EventArgs e)
{
Console.WriteLine();
for (int i = 1; i <= 12; i++)
{
PrintMonthCalendar(new DateTime(2006, i, 1));
Console.WriteLine();
}
}
private void PrintMonthCalendar(DateTime date)
{
Console.WriteLine(date.ToString("MMMM yyyy"));
Console.WriteLine("SU MO TU WE TH FR SA");
DateRange[] cal = DateRange.GetMonthCalendarWs(date, false);
for (int i = 0; i < cal.Length; i++)
{
DateRange day = cal[i];
if (day == null)
Console.Write(" " + " ");
else
Console.Write(day.StartDate.Day.ToString().PadLeft(2, '0') + " ");
if ((i != 0) && ((i + 1) % 7 == 0))
Console.WriteLine();
}
}
--
William Stacey [MVP]

Add Temp Parameters to a function

I am writing a set of functions and then a stored procedure to allow me to view some data in Reporting Services. I have written the first part of the function as shown ;

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[fnWTRTerrierData]

(@.vch_site_ref nvarchar (3), @.dt_src_date datetime)

RETURNS @.WeeklyTerrierRSPI TABLE

(Areacode varchar(2),siteref nvarchar(3),

estatename nvarchar(100), Securitised nvarchar(255),

unitref nvarchar(15), unittype nvarchar(30),

unittype_count int, tenantname nvarchar(100),

tenantstatus nvarchar(25), tenantstatus_count int,

unitstatus nvarchar(15), unitstatus_count int,

floortotal float, floortotocc float,

initialvacarea float, initialvacnet float,

TotalRent float, NetRent float,

FinalRtLsincSC float, DiscEndDate datetime,

ErvTot float, Leaseterm int,

leasestart datetime, rentreview nvarchar(255),

leaseend datetime, breakclause datetime,

tenancyterm datetime, landact nvarchar(255),

datadate datetime)

AS

BEGIN

INSERT @.WeeklyTerrierRSPI

SELECT Areacode, siteref, estatename, Securitised, unitref, unittype, unittype_count, tenantname,

tenantstatus, tenantstatus_count, unitstatus, unitstatus_count, floortotal, floortotocc,

initialvacarea, initialvacnet, TotalRent, NetRent, FinalRtLsincSC, DiscEndDate, ErvTot,

Leaseterm, leasestart, rentreview, leaseend, breakclause, tenancyterm, landact, datadate

FROM dbo.src_terrier

WHERE (datadate = @.dt_src_date) AND (siteref = @.vch_site_ref)

RETURN

END

I have then written a stored procedure which picks up the result set from the function and ultimately will deliver it to Reporting Services. The problem I have is that as soon as I run the CREATE PROCEDURE script, I get an error saying ;

Msg 216, Level 16, State 1, Procedure spWTRWeeklyTerrierData, Line 16

Parameters were not supplied for the function 'fnWTRTerrierData'.

How can I add parameters to the function sufficently so that I can run the Create procedure element of my code?

Regards

Post your Stored Procedure codes.|||

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[spWTRWeeklyTerrierData]

(@.vch_site_ref nvarchar(3), @.dt_src_date datetime)

AS

BEGIN

SET NOCOUNT ON;

SELECT fnWTRTerrierData.Areacode, fnWTRTerrierData.siteref, fnWTRTerrierData.estatename, fnWTRTerrierData.Securitised, fnWTRTerrierData.unitref, fnWTRTerrierData.unittype, fnWTRTerrierData.unittype_count, fnWTRTerrierData.tenantname,fnWTRTerrierData.tenantstatus, fnWTRTerrierData.tenantstatus_count, fnWTRTerrierData.unitstatus, fnWTRTerrierData.unitstatus_count, fnWTRTerrierData.floortotal, fnWTRTerrierData.floortotocc, fnWTRTerrierData.initialvacarea, fnWTRTerrierData.initialvacnet, fnWTRTerrierData.TotalRent, fnWTRTerrierData.NetRent, fnWTRTerrierData.FinalRtLsincSC, fnWTRTerrierData.ErvTot, fnWTRTerrierData.tenancyterm, fnWTRTerrierData.landact, fnWTRTerrierData.datadate

FROM fnWTRTerrierData

RETURN

END

|||

Looking at a stored procedure I pulled over from a SQL 2000 DB, I took out the elements that seems to resemble the function side

BEGIN

SET NOCOUNT ON;

RETURN

END

But I am still met with the same parameters message when I try to execute the SQL code to create the SP.

Regards

|||

ALTER FUNCTION [dbo].[fnWTRTerrierData]

(

@.vch_site_ref nvarchar (3),

@.dt_src_date datetime

)

Your table function is expecting 2 input parameter which you did not supply.
You will need to supply these 2 value to the function
for example

SELECT ...
FROM fnWTRTerrierData ('?', '2006-01-01')

add cariage return

I'm trying to add a carriage return in the below function, but nothing is
happening.
The end result is to cut-n-paste into notepad with carriage returns.
What am I doing wrong?
thanks!
CREATE FUNCTION dbo.fctConcatTitles
(
@.O VARCHAR(32)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.r VARCHAR(8000)
SELECT @.r = ISNULL(@.r+ Char(13) , '') + Title + ' - ' + Artist
FROM Titles
WHERE OrderNo = @.O
RETURN @.r
ENDshank wrote on Wed, 11 Jan 2006 05:15:01 -0500:

> I'm trying to add a carriage return in the below function, but nothing is
> happening.
> The end result is to cut-n-paste into notepad with carriage returns.
> What am I doing wrong?
Try CHAR(13) + CHAR(10). Windows uses a Carriage Return + Line Feed for end
of line termination, not just a CR.
Dan|||The above code works and an optional solution is manually adding a
linefeed,
like this:
SELECT @.r = ISNULL(@.r + '
' , '') + Title + ' - ' + Type
FROM Titles
It's not pretty - but efficient...
/ola|||shank (shank@.tampabay.rr.com) writes:
> I'm trying to add a carriage return in the below function, but nothing is
> happening.
> The end result is to cut-n-paste into notepad with carriage returns.
> What am I doing wrong?
> thanks!
> CREATE FUNCTION dbo.fctConcatTitles
> (
> @.O VARCHAR(32)
> )
> RETURNS VARCHAR(8000)
> AS
> BEGIN
> DECLARE @.r VARCHAR(8000)
> SELECT @.r = ISNULL(@.r+ Char(13) , '') + Title + ' - ' + Artist
> FROM Titles
> WHERE OrderNo = @.O
> RETURN @.r
> END
Beside the CR issue, note that this piece of code relies on undefined
behaviour, so there is no guarantee that you will get the result you are
looking for.
In SQL 2000, the only guaranteed way is to run a cursor. And most
probably you want an ORDER BY as well, so that you don't the data
in some funny order.
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|||I've tried this...
SELECT @.r = ISNULL(@.r+ Char(13) + Char(10), '') + Title + ' - ' + Artist
Then this...
SELECT @.r = ISNULL(@.r+ '
', '') + Title + ' - ' + Artist
...without any luck.
To be clear, I'm expecting to see results in QA.
I'm also cut-n-pasting from QA into notepad and there's no carriage returns.
Can you explain more about the cursor?
I don't believe I've ever used code that uses the cursor.
thanks to all!
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns974881545AC39Yazorman@.127.0.0.1...
> shank (shank@.tampabay.rr.com) writes:
> Beside the CR issue, note that this piece of code relies on undefined
> behaviour, so there is no guarantee that you will get the result you are
> looking for.
> In SQL 2000, the only guaranteed way is to run a cursor. And most
> probably you want an ORDER BY as well, so that you don't the data
> in some funny order.
> --
> 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|||Use the "Results to Text" option.
ML
http://milambda.blogspot.com/|||shank (shank@.tampabay.rr.com) writes:
> I've tried this...
> SELECT @.r = ISNULL(@.r+ Char(13) + Char(10), '') + Title + ' - ' + Artist
> Then this...
> SELECT @.r = ISNULL(@.r+ '
> ', '') + Title + ' - ' + Artist
> ...without any luck.
> To be clear, I'm expecting to see results in QA.
> I'm also cut-n-pasting from QA into notepad and there's no carriage
> returns.
To echo ML's post: you are using text more, aren't you? In grid mode
you will not see any CRs.

> Can you explain more about the cursor?
> I don't believe I've ever used code that uses the cursor.
That's good! Too many inexperienced programmers use cursors when they
shouldn't, so I almost feel bad for showing you, but since this only
works with cursors (in SQL 2000), there is not much choice:
DECLARE @.r varchar(8000),
@.item varchar(50)
DECLARE thiscur CURSOR LOCAL FAST_FORWARD FOR
SELECT title + '-' + artist
FROM Titles
WHERE OrderNo = @.OrderNo
ORDER BY title, artist
OPEN thiscur
WHILE 1 = 1
BEGIN
FETCH thiscur INTO @.item
IF @.@.fetch_status <> 0
BREAK
SELECT @.r = CASE WHEN @.r IS NULL THEN ''
ELSE @.r + char(10) + char(13)
END + @.item
END
DEALLOCATE thiscur
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|||Erland Sommarskog:
> To echo ML's post: you are using text mode, aren't you? In grid mode
> you will not see any CRs
Well that's not true is it? The CRs is shown as "squares" in grid mode
and when c-n-p
from QA to notepad they tag along and do result in CRs.
shank:
I cannot understand why you don't get the expected result - can you try
the
following code (NB you have to have example database "pubs" installed)
-- code start
use pubs
declare @.r varchar(8000)
SELECT @.r = ISNULL(@.r+ Char(13) + Char(10), '') + title + ' - ' + type
>From titles
Select @.r
-- code end
/o

Add admin user to reporting services

Hi, we have a webpage which makes a call to the reporting services
function GetSystemPermissions via C# to get permissions for our
intranet.
My question is how to I add an admin user to this group? Is it done
via Active Directory and if so what rights does the person need?
We are running MSSQL 2005 and Windows Server 2003.
Thanks for any help
Regards
MarkusReporting Services by default is integrated with Windows integrated
security. But, you have to map a user or group to a role in RS. By default
any member of the local adminstrators group is an admin in RS.
What I do is add appropriate users to the local adminstrators group on the
server running RS for adminstrative rights. But you could just assign
another group to the appropriate role in RS. For browsing I add a local
group and then to that local group I add individual users and domain groups.
That local group is then assigned to the appropriate role.
Read up on roles in RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<MarkusJNZ@.gmail.com> wrote in message
news:1183531995.432312.93700@.m37g2000prh.googlegroups.com...
> Hi, we have a webpage which makes a call to the reporting services
> function GetSystemPermissions via C# to get permissions for our
> intranet.
> My question is how to I add an admin user to this group? Is it done
> via Active Directory and if so what rights does the person need?
> We are running MSSQL 2005 and Windows Server 2003.
> Thanks for any help
> Regards
> Markus
>

Add a function a report

How i can add a function to a report and use to put in a textbox

Like

Public Function Area(radius)

return 3.14*radius*radius

End Function

Thanks

vgta,

from your report click on Report
then choose Report Properties...

From there you will see a CODE tab. Enter your function here.

The expression of your textbox will be Code.functionName
Using your example it would be Code.Area(radius)

Bret

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Hi all,

I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.

It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.
You must access this provider through a linked server."

I am using VB 6.0 as front end. Is there anyway i can overcome this error?

Please help.

Dhiraj

I just started having this issue too. I had this working in SQLEXPRESS, but now I am moving to a new SQL Server (Version 3054) and started getting this error.

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Hi all,

I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.

It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.
You must access this provider through a linked server."

I am using VB 6.0 as front end. Is there anyway i can overcome this error?

Please help.

Dhiraj

I just started having this issue too. I had this working in SQLEXPRESS, but now I am moving to a new SQL Server (Version 3054) and started getting this error.