addlinkedserver to 2005

-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'LONDON Payroll',
@.provider = 'MSDASQL',
@.provstr = 'DRIVER={SQL
Server};SERVER=MyServer;UID=sa;PWD=;'
GO
Is there anything different to link to SQL2005? How can
create a link to 2005?
Thx.
Probably better to post this question in the SQL Server 2005 groups?
http://www.aspfaq.com/
(Reverse address to reply.)
"C TO" <anonymous@.discussions.microsoft.com> wrote in message
news:98cc01c478b5$b0382620$a301280a@.phx.gbl...
> -- To use named parameters:
> EXEC sp_addlinkedserver
> @.server = 'LONDON Payroll',
> @.provider = 'MSDASQL',
> @.provstr = 'DRIVER={SQL
> Server};SERVER=MyServer;UID=sa;PWD=;'
> GO
> Is there anything different to link to SQL2005? How can
> create a link to 2005?
> Thx.
>

addlinkedserver to 2005

-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'LONDON Payroll',
@.provider = 'MSDASQL',
@.provstr = 'DRIVER={SQL
Server};SERVER=MyServer;UID=sa;PWD=;'
GO
Is there anything different to link to SQL2005? How can
create a link to 2005?
Thx.Probably better to post this question in the SQL Server 2005 groups?
http://www.aspfaq.com/
(Reverse address to reply.)
"C TO" <anonymous@.discussions.microsoft.com> wrote in message
news:98cc01c478b5$b0382620$a301280a@.phx.gbl...
> -- To use named parameters:
> EXEC sp_addlinkedserver
> @.server = 'LONDON Payroll',
> @.provider = 'MSDASQL',
> @.provstr = 'DRIVER={SQL
> Server};SERVER=MyServer;UID=sa;PWD=;'
> GO
> Is there anything different to link to SQL2005? How can
> create a link to 2005?
> Thx.
>

addlinkedserver to 2005

-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'LONDON Payroll',
@.provider = 'MSDASQL',
@.provstr = 'DRIVER={SQL
Server};SERVER=MyServer;UID=sa;PWD=;'
GO
Is there anything different to link to SQL2005? How can
create a link to 2005?
Thx.Probably better to post this question in the SQL Server 2005 groups?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"C TO" <anonymous@.discussions.microsoft.com> wrote in message
news:98cc01c478b5$b0382620$a301280a@.phx.gbl...
> -- To use named parameters:
> EXEC sp_addlinkedserver
> @.server = 'LONDON Payroll',
> @.provider = 'MSDASQL',
> @.provstr = 'DRIVER={SQL
> Server};SERVER=MyServer;UID=sa;PWD=;'
> GO
> Is there anything different to link to SQL2005? How can
> create a link to 2005?
> Thx.
>

Additional info

Hi,
I have one client and one server. In both hosts I created an OS-user named User1.
The user have been given various role under the prefix SQLServer2005. The default instance of SQL Server and instance (background-process) of Analysis Service are also run under User1.
I connected via Management Studio to Analysis Service directly in the server. I set the permission to access Analysis Service to Everyone. This means everyone should be able to use Analysis Service. I have set ODBC data source to the server. Testing was successful.
I created a project to create a cube. The tables are fairly small (total 300 MB, biggest one 290).
On project setting (created at client) I set the server host as deployment target. I managed to deploy project when all I have are Data Source and Data Source Views.
But on deployment I always failed. The errors remain the same, begins with
-
OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.; 08001.

I have tried 4 combinations of Impersonation :
(A) Use a specific username and password
* in which I supply User1 and the password of User1
(B) Use the service account
(C) Use the credentials of the current user
(D) Default
Why is this? The server does certainly exist. I think the error message is wrong.
I have seen 3 posting that seems related. One of them say the solution is giving the right impersonation, but gave no detail. About the privilege, User1 are granted the role of
SQLServer2005MSOLAP. What is the solution?
Thank you,
Bernaridho
For additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho
|||I also encountered this error. I workaround by performing a remote connection to the server and create the Business Intelligence project directly on the server itself.|||Hi Sheah,

I'm not sure how often this error will occur. But trying other server, I don't have problem with one of previously failed try : impersonation using specific user. I supplied username User1 and its password as the credential for Data Source.

In my case, one of my server has the problem. Is it a bug or something I don't know but certainly the exact same setting on my two server machines give two different results : fail and success. Nevertheless, you may try setting the impersonation that uses specific username. I tried using OS-user (Windows-user), not SQL Server user. It worked.

Bernaridho
|||

Bernaridho wrote:

For additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho

sql

Additional info

Hi,

I have one client and one server. In both hosts I created an OS-user named User1.

The user have been given various role under the prefix SQLServer2005.

The default instance of SQL Server and instance (background-process) of

Analysis Service are also run under User1.

I connected via Management Studio to Analysis Service directly in the

server. I set the permission to access Analysis Service to Everyone.

This means everyone should be able to use Analysis Service. I have set

ODBC data source to the server. Testing was successful.

I created a project to create a cube. The tables are fairly small (total 300 MB, biggest one 290).

On project setting (created at client) I set the server host as

deployment target. I managed to deploy project when all I have are Data

Source and Data Source Views.

But on deployment I always failed. The errors remain the same, begins with

-

OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.; 08001.
I have tried 4 combinations of Impersonation :

(A) Use a specific username and password

* in which I supply User1 and the password of User1

(B) Use the service account

(C) Use the credentials of the current user

(D) Default

Why is this? The server does certainly exist. I think the error message is wrong.
I have seen 3 posting that seems related. One of them say the solution is giving the right impersonation, but gave no detail. About the privilege, User1 are granted the role of
SQLServer2005MSOLAP. What is the solution?

Thank you,

BernaridhoFor additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho|||I also encountered this error. I workaround by performing a remote connection to the server and create the Business Intelligence project directly on the server itself.|||Hi Sheah,

I'm not sure how often this error will occur. But trying other server, I don't have problem with one of previously failed try : impersonation using specific user. I supplied username User1 and its password as the credential for Data Source.

In my case, one of my server has the problem. Is it a bug or something I don't know but certainly the exact same setting on my two server machines give two different results : fail and success. Nevertheless, you may try setting the impersonation that uses specific username. I tried using OS-user (Windows-user), not SQL Server user. It worked.

Bernaridho|||

Bernaridho wrote:

For additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho

Adding the date to a subscription report name

Does anyone have information on how I can add a file date to my named report when a subscription writes that file to a network folder? I am exporting as .pdf files. For example, today I have filename abc.pdf but I'd like it to be abc03312006.pdf. Likewise, tomorrow's file would be abc04012006.pdf.

Any help is greatly appreciated.

You have to go for reporting services programming and create a subscription programatically and pass the date as a value to subscription's description parameter like this.

service.CreateSubscription(reportpath, extSettings, description, eventType, matchData, parameters );

|||

Can you please give more details on it? Where to go and how to do this?

Thanks,

-Rohit

Adding the date to a subscription report name

Does anyone have information on how I can add a file date to my named report when a subscription writes that file to a network folder? I am exporting as .pdf files. For example, today I have filename abc.pdf but I'd like it to be abc03312006.pdf. Likewise, tomorrow's file would be abc04012006.pdf.

Any help is greatly appreciated.

You have to go for reporting services programming and create a subscription programatically and pass the date as a value to subscription's description parameter like this.

service.CreateSubscription(reportpath, extSettings, description, eventType, matchData, parameters );

|||

Can you please give more details on it? Where to go and how to do this?

Thanks,

-Rohit

adding text to the column data in a query result

If I had a table with 3 columns in it, named "ID", "TITLE" and "CLASS_ID" and, in a query result, I wanted to add a fourth column named "URL" that would be the result of concatenating a file name and the value in "ID", how would I do that?

so a table that looked like:
ID TITLE CLASS_ID
1 "Hello" 137
3 "Goodbye" 587
19 "Whatever" 1028

could return a result set that looked like:
ID TITLE CLASS_ID URL

1 "Hello" 137 "hardcodedfilename.aspx?id=1"

3 "Goodbye" 587 "hardcodedfilename.aspx?id=3"

19 "Whatever" 1028 "hardcodedfilename.aspx?id=19"

I have looked through my SQL book, and scanned the usual help files and google search options, but I haven't seen an example of this. Can it be done, and if so, how?

Thanks in advance for your help.
roger

There are a few approaches, two different ways are shown below.

Chris

DECLARE @.URLTemplate VARCHAR(100)

SET @.URLTemplate = '"hardcodedfilename.aspx?id=**"'

SELECT ID,

TITLE,

CLASS_ID,

REPLACE(@.URLTemplate, '**', CAST(ID AS VARCHAR(10))) AS URL

FROM ...

--or

DECLARE @.prefix VARCHAR(100)

SET @.prefix = '"hardcodedfilename.aspx?id='

SELECT ID,

TITLE,

CLASS_ID,

@.prefix + CAST(ID AS VARCHAR(10)) + '"' AS URL

FROM ...

|||Thank you for your quick response!
I inserted the code you suggested into my existing, recursive function. The original function (which does work as it is) is:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN

SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END

now, it looks like:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN XML

DECLARE @.prefix VARCHAR(100)
SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + " " as "@.url",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END

But, I get an error:
Msg 156, Level 15, State 1, Procedure fn_WPMTREE, Line 18
Incorrect syntax near the keyword 'FOR'.

Any suggestions? And thanks again for your help.|||

I've made a couple of corrections, see the example below.

I wasn't sure whether you needed the trailing space in the following:

CAST(ID AS VARCHAR(10)) + ' ' as "@.url",

If not, then simply replace the above with the following:

CAST(ID AS VARCHAR(10)) as "@.url",

Chris

CREATE FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)

RETURNS XML

WITH RETURNS NULL ON NULL INPUT

BEGIN

DECLARE @.prefix VARCHAR(100)

SET @.prefix = 'rightframe.aspx?s='

RETURN (

SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + ' ' as "@.url",

CASE WHEN PARENT_ID=@.SceneID

THEN dbo.fn_WPMTREE(id)

END

FROM dbo.SCENE WHERE PARENT_ID=@.SceneID

FOR XML PATH('Scene'), TYPE

)

END

|||That works beautifully!

I can't thank you enough.

Now, all I have to do is get an INNER JOIN working in this, and I am in good shape!

Again, Thank you.|||

HI Chris,

I have a similar question. I have a query that I use for a letter that I create. I'm attaching the query below. I want to add the text "CRM" next to the output result for "AgentDesc". So if the output of "AgentDesc" is "Director"; then I want it to display as CRM Director. Am sure this is easy enough for you..:

Thanks

SELECT dbo.tblOffer.*, CRM_SQL_ADMIN.DtFormat(dbo.tblOffer.OfferDt, 'mm dd, yyyy') AS OfferDate, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.LName)) AS AnalName, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.LName)) AS AgentName, dbo.l_tblAgent.AgentDesc AS AgentDesc,
CRM_SQL_ADMIN.vwOfferAgent_Names.DCAName AS DCAName, CRM_SQL_ADMIN.vwOfferAgent_Names.MCAName AS MCAName,
CRM_SQL_ADMIN.vwOfferAgent_Names.DIRName AS DIRName, CRM_SQL_ADMIN.vwOfferAgent_Names.RMName AS RMName,
CRM_SQL_ADMIN.vwOfferAgent_Names.SReps AS SReps,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'PayTerms' AND [OPTION] = [tblOffer].[PayTerms]) AS PayTerms_Desc, dbo.l_tblCust.CustName AS CustName,
dbo.l_tblCust.StAddress AS StAddress, dbo.l_tblCust.City AS City, dbo.l_tblCust.State AS State, dbo.l_tblCust.ZipCode AS ZipCode,
dbo.l_tblCust.GPO AS GPO, dbo.l_tblCust.IsTargetCust AS IsTargetCust,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgBen' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgBen]) AS ProgBen_Desc,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgCrit' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgCrit]) AS ProgCrit_Desc, dbo.tblOffer.SpecProgType AS SpecProg,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt), 'mm dd, yyyy') AS EndOfQtr,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.WeekDayAdd(- 4, CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt)), 'mm dd, yyyy') AS EndOfQtrLess3d,
OSS.Tot_Qty AS Tot_Qty, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Purch) AS Tot_Purch, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Savings)
AS Tot_Savings
FROM dbo.l_tblCust INNER JOIN
dbo.tblOffer ON dbo.l_tblCust.CustNum = dbo.tblOffer.CustNum LEFT OUTER JOIN
dbo.l_tblBusAnalyst ON dbo.tblOffer.BusAnalystID = dbo.l_tblBusAnalyst.EmpNum LEFT OUTER JOIN
CRM_SQL_ADMIN.vwOfferAgent_Names ON dbo.tblOffer.OfferID = CRM_SQL_ADMIN.vwOfferAgent_Names.OfferID LEFT OUTER JOIN
dbo.l_tblAgent ON dbo.tblOffer.AgentID = dbo.l_tblAgent.AgentID INNER JOIN
CRM_SQL_ADMIN.vwOfferSums_Simple OSS ON dbo.tblOffer.OfferID = OSS.OfferID|||

Change:

dbo.l_tblAgent.AgentDesc AS AgentDesc,

to:

( 'CRM ' + dbo.l_tblAgent.AgentDesc ) AS AgentDesc,

|||Gr8...That works...Thanks a lot Arnie for your help...

adding text to the column data in a query result

If I had a table with 3 columns in it, named "ID", "TITLE" and "CLASS_ID" and, in a query result, I wanted to add a fourth column named "URL" that would be the result of concatenating a file name and the value in "ID", how would I do that?

so a table that looked like:
ID TITLE CLASS_ID
1 "Hello" 137
3 "Goodbye" 587
19 "Whatever" 1028

could return a result set that looked like:
ID TITLE CLASS_ID URL
1 "Hello" 137 "hardcodedfilename.aspx?id=1"
3 "Goodbye" 587 "hardcodedfilename.aspx?id=3"
19 "Whatever" 1028 "hardcodedfilename.aspx?id=19"

I have looked through my SQL book, and scanned the usual help files and google search options, but I haven't seen an example of this. Can it be done, and if so, how?

Thanks in advance for your help.
roger

There are a few approaches, two different ways are shown below.

Chris

DECLARE @.URLTemplate VARCHAR(100)

SET @.URLTemplate = '"hardcodedfilename.aspx?id=**"'

SELECT ID,

TITLE,

CLASS_ID,

REPLACE(@.URLTemplate, '**', CAST(ID AS VARCHAR(10))) AS URL

FROM ...

--or

DECLARE @.prefix VARCHAR(100)

SET @.prefix = '"hardcodedfilename.aspx?id='

SELECT ID,

TITLE,

CLASS_ID,

@.prefix + CAST(ID AS VARCHAR(10)) + '"' AS URL

FROM ...

|||Thank you for your quick response!
I inserted the code you suggested into my existing, recursive function. The original function (which does work as it is) is:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN

SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END

now, it looks like:
ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN XML

DECLARE @.prefix VARCHAR(100)
SET @.prefix = "rightframe.aspx?s="
(SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + " " as "@.url",
CASE WHEN PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(id)
END
FROM dbo.SCENE WHERE PARENT_ID=@.SceneID
FOR XML PATH('Scene'), TYPE)
END

But, I get an error:
Msg 156, Level 15, State 1, Procedure fn_WPMTREE, Line 18
Incorrect syntax near the keyword 'FOR'.

Any suggestions? And thanks again for your help.

|||

I've made a couple of corrections, see the example below.

I wasn't sure whether you needed the trailing space in the following:

CAST(ID AS VARCHAR(10)) + ' ' as "@.url",

If not, then simply replace the above with the following:

CAST(ID AS VARCHAR(10)) as "@.url",

Chris

CREATE FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)

RETURNS XML

WITH RETURNS NULL ON NULL INPUT

BEGIN

DECLARE @.prefix VARCHAR(100)

SET @.prefix = 'rightframe.aspx?s='

RETURN (

SELECT ID As "@.id", TITLE as "@.title", CLASS_ID as "@.clsID", @.prefix + CAST(ID AS VARCHAR(10)) + ' ' as "@.url",

CASE WHEN PARENT_ID=@.SceneID

THEN dbo.fn_WPMTREE(id)

END

FROM dbo.SCENE WHERE PARENT_ID=@.SceneID

FOR XML PATH('Scene'), TYPE

)

END

|||That works beautifully!

I can't thank you enough.

Now, all I have to do is get an INNER JOIN working in this, and I am in good shape!

Again, Thank you.
|||

HI Chris,

I have a similar question. I have a query that I use for a letter that I create. I'm attaching the query below. I want to add the text "CRM" next to the output result for "AgentDesc". So if the output of "AgentDesc" is "Director"; then I want it to display as CRM Director. Am sure this is easy enough for you..:

Thanks

SELECT dbo.tblOffer.*, CRM_SQL_ADMIN.DtFormat(dbo.tblOffer.OfferDt, 'mm dd, yyyy') AS OfferDate, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblBusAnalyst.LName)) AS AnalName, LTRIM(CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.FName)
+ ' ' + CRM_SQL_ADMIN.NZ(dbo.l_tblAgent.LName)) AS AgentName, dbo.l_tblAgent.AgentDesc AS AgentDesc,
CRM_SQL_ADMIN.vwOfferAgent_Names.DCAName AS DCAName, CRM_SQL_ADMIN.vwOfferAgent_Names.MCAName AS MCAName,
CRM_SQL_ADMIN.vwOfferAgent_Names.DIRName AS DIRName, CRM_SQL_ADMIN.vwOfferAgent_Names.RMName AS RMName,
CRM_SQL_ADMIN.vwOfferAgent_Names.SReps AS SReps,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'PayTerms' AND [OPTION] = [tblOffer].[PayTerms]) AS PayTerms_Desc, dbo.l_tblCust.CustName AS CustName,
dbo.l_tblCust.StAddress AS StAddress, dbo.l_tblCust.City AS City, dbo.l_tblCust.State AS State, dbo.l_tblCust.ZipCode AS ZipCode,
dbo.l_tblCust.GPO AS GPO, dbo.l_tblCust.IsTargetCust AS IsTargetCust,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgBen' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgBen]) AS ProgBen_Desc,
(SELECT Description
FROM l_tbl_Options
WHERE [Field] = 'ProgCrit' AND CONVERT(bit, [OPTION]) = [tblOffer].[ProgCrit]) AS ProgCrit_Desc, dbo.tblOffer.SpecProgType AS SpecProg,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt), 'mm dd, yyyy') AS EndOfQtr,
CRM_SQL_ADMIN.DtFormat(CRM_SQL_ADMIN.WeekDayAdd(- 4, CRM_SQL_ADMIN.EndOfQtr(dbo.tblOffer.OfferDt)), 'mm dd, yyyy') AS EndOfQtrLess3d,
OSS.Tot_Qty AS Tot_Qty, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Purch) AS Tot_Purch, CRM_SQL_ADMIN.GetUSDNo00(OSS.Tot_Savings)
AS Tot_Savings
FROM dbo.l_tblCust INNER JOIN
dbo.tblOffer ON dbo.l_tblCust.CustNum = dbo.tblOffer.CustNum LEFT OUTER JOIN
dbo.l_tblBusAnalyst ON dbo.tblOffer.BusAnalystID = dbo.l_tblBusAnalyst.EmpNum LEFT OUTER JOIN
CRM_SQL_ADMIN.vwOfferAgent_Names ON dbo.tblOffer.OfferID = CRM_SQL_ADMIN.vwOfferAgent_Names.OfferID LEFT OUTER JOIN
dbo.l_tblAgent ON dbo.tblOffer.AgentID = dbo.l_tblAgent.AgentID INNER JOIN
CRM_SQL_ADMIN.vwOfferSums_Simple OSS ON dbo.tblOffer.OfferID = OSS.OfferID|||

Change:

dbo.l_tblAgent.AgentDesc AS AgentDesc,

to:

( 'CRM ' + dbo.l_tblAgent.AgentDesc ) AS AgentDesc,

|||Gr8...That works...Thanks a lot Arnie for your help...sql