Address field problem.

I have a number of text boxes (one on top of the other) to display the addresses in my report. I have Address_name, Address1, Address2, Address_city, Address_State, Address_PostalCode - Each with its own text box except for the city, state, and postal code for which I just combined the fields. However, I have many who do not have any data for Address2 and don't want an empty line in the address. Is there an expression I can use to display the next line or move/shift up the remaining parts of the address? Would the iif and isnothing expressions be helpful, and if so, how do I write it correctly?

If it is ok to put them all into one textbox, you could do this:

=Fields!Address_name.Value
& chr(10) & Fields!Address1.Value
& IIf(Len(Fields!Address2.Value) > 0, chr(10) & Fields!Address2.Value, "")
& chr(10) & Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value

If they have to have their own textbox and you just want to move it up if the Address2 is empty, then you could try something like this.

In the expression for the Address2 textbox:

=IIf(Len(Fields!Address2.Value) > 0, Fields!Address2.Value, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value)

Then, in the expression for the City/State/Postal Code:

=IIf(Len(Fields!Address2.Value) > 0, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value, "")

Hope this helps.

Jarret

|||

Thanks mate, I'll give it a try.!

What does "Len" do?

|||

Len gives you then length of the object you pass in. In this case, if the Address2 is NULL or an empty string, Len will return 0 and it will be skipped with the logic in the code.

Let me know if that fixes your issue.

Jarret

|||

I see, thanks for the info. I will try this in about an hour or so, I'll post back and give you an update.

Thanks again Jarret,

Bill

additional match information from CONTAINSTABLE

Is there a way to get additional match information from SQL Server
2000 CONTAINSTABLE? For example:
If my text data is:
The black bird and the blue bird...
01234567890123456789012678901234
And I search for bird, Can I get results that tell me that 2
occurances were found inside the data at locations 10 and 21?
no, there is no way to do this. You can get some of this functionality if
you dump your database content into the file system and then use Indexing
Services hit highlighting features to mark up your content in a web page.
"madmike" <mikek@.cs.cmu.edu> wrote in message
news:cb16b300.0407061229.c1f707d@.posting.google.co m...
> Is there a way to get additional match information from SQL Server
> 2000 CONTAINSTABLE? For example:
> If my text data is:
> The black bird and the blue bird...
> 01234567890123456789012678901234
> And I search for bird, Can I get results that tell me that 2
> occurances were found inside the data at locations 10 and 21?
|||MadMike,
While Hilary is correct in that you cannot get the specific info you're
requesting from CONTAINSTABLE or even CONTAINS, you *might* get somewhat
close to your requirements via using PatIndex. For example, if you could
work with getting a range of words, plus and minus distance from the search
word, you could do something like the below query using a table (pub_info)
in the Pubs database that is already FT-enabled on the TEXT column
(pr_info), you could use the following SQL code to get the results you want:
-- The following SQL FTS query on the pubs table pub_info will return rows
that match the FTS search word (books)
-- and the near by words from 20 characters before to 100 characters after
the searched keyword(books).
SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
FROM pub_info
WHERE Contains(pr_info, 'books')
/* returns the following results:
pub_id
-- ---
9952 t data for Scootney Books, publisher 9952 in the pubs database.
Scootney Books is located in New Yor
0736 t data for New Moon Books, publisher 0736 in the pubs database. New
Moon Books is located in Boston,
(2 row(s) affected)
*/
This might start you thinking in other terms on how to get your
requirements, as they say there are many ways to skin a cat!
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uPD$qt6YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> no, there is no way to do this. You can get some of this functionality if
> you dump your database content into the file system and then use Indexing
> Services hit highlighting features to mark up your content in a web page.

> "madmike" <mikek@.cs.cmu.edu> wrote in message
> news:cb16b300.0407061229.c1f707d@.posting.google.co m...
>
sql

adding various fonts/weights to values in a string

HI
I am creating a form that needs to have strings of text in the text box.
The strings have numbers included and the numbers need to be a different
font. The form is built in a table, so splitting the row will not work.
Example:
Textbox 10 has the following value:
8. Fax Number
The number 8 needs to be arial narrow bold 6.96 and the fax number needs to
be arrial narrow 6.96
Any help would be appreciated.
Thank youHi Susan,
One of the ways to do what you need is to place a rectangle object right in
the textbox of the table. Then place two (or more) individual textboxed
inside of the rectangle that is inside of the table cell. You can set the
font weights on the individual textboxes at that point so:
"8" will be in its own textbox and the Fax Number data field in the other,
but they can be together within a single cell. This might add a little more
work, but it should solve the problem.
Rodney Landrum
"Susan R" <SusanR@.discussions.microsoft.com> wrote in message
news:B55B1631-A886-45A0-AFA5-5533CB87C0BE@.microsoft.com...
> HI
> I am creating a form that needs to have strings of text in the text box.
> The strings have numbers included and the numbers need to be a different
> font. The form is built in a table, so splitting the row will not work.
> Example:
> Textbox 10 has the following value:
> 8. Fax Number
> The number 8 needs to be arial narrow bold 6.96 and the fax number needs
> to
> be arrial narrow 6.96
> Any help would be appreciated.
> Thank you
>sql

Adding Values in a Text Box

I have multiple values in text boxes based on Summed values. I would like to
add the values that are in the text boxes. What is the ref for Boxes? I
know fields are Fields!.
Here is an example of what I am running it the text boxes. They are in the
group footer. I would like to add them in the report footer, which is in a
different scope.
=IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
Fields!ItemLoss.Value), 0)
--
Thank You, LeoI would like to sum the values not just add them.
Thanks
"TrussworksLeo" wrote:
> I have multiple values in text boxes based on Summed values. I would like to
> add the values that are in the text boxes. What is the ref for Boxes? I
> know fields are Fields!.
> Here is an example of what I am running it the text boxes. They are in the
> group footer. I would like to add them in the report footer, which is in a
> different scope.
> =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> Fields!ItemLoss.Value), 0)
> --
> Thank You, Leo|||Take a look a the ReportItems!<TextboxName>.Value syntax. This syntax allows
you to reference values in a textbox.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"TrussworksLeo" <Leo@.noemail.noemail> wrote in message
news:509843C2-29B9-4B0E-B22A-5C497B99F0AB@.microsoft.com...
> I would like to sum the values not just add them.
> Thanks
> "TrussworksLeo" wrote:
> > I have multiple values in text boxes based on Summed values. I would
like to
> > add the values that are in the text boxes. What is the ref for Boxes?
I
> > know fields are Fields!.
> >
> > Here is an example of what I am running it the text boxes. They are in
the
> > group footer. I would like to add them in the report footer, which is
in a
> > different scope.
> >
> > =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> > Fields!ItemLoss.Value), 0)
> > --
> > Thank You, Leo|||Yes, But it will not allow me to us a aggregate like sum against it?
Leo
"Bruce Johnson [MSFT]" wrote:
> Take a look a the ReportItems!<TextboxName>.Value syntax. This syntax allows
> you to reference values in a textbox.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> news:509843C2-29B9-4B0E-B22A-5C497B99F0AB@.microsoft.com...
> > I would like to sum the values not just add them.
> >
> > Thanks
> >
> > "TrussworksLeo" wrote:
> >
> > > I have multiple values in text boxes based on Summed values. I would
> like to
> > > add the values that are in the text boxes. What is the ref for Boxes?
> I
> > > know fields are Fields!.
> > >
> > > Here is an example of what I am running it the text boxes. They are in
> the
> > > group footer. I would like to add them in the report footer, which is
> in a
> > > different scope.
> > >
> > > =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> > > Fields!ItemLoss.Value), 0)
> > > --
> > > Thank You, Leo
>
>

Adding to the Toolbar

Is there a way to add text to the toolbar (that contains the export choices,
refresh button, paging, etc)? I would just like to put a line that says
"You must export before you can print".
Thanks in advance,
MelissaSP1 allows some modifications to the HTML Viewer toolbar through a style
sheet. Check out
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Melissa" <a@.a.com> wrote in message
news:e3EDhsJgEHA.3192@.tk2msftngp13.phx.gbl...
> Is there a way to add text to the toolbar (that contains the export
choices,
> refresh button, paging, etc)? I would just like to put a line that says
> "You must export before you can print".
> Thanks in advance,
> Melissa
>

adding to text

If I have a varchar field, I can easily add to it. For instance
select 'Name: ' + fname as fname from Customers.

But what if I have a text field instead of varchar?

select 'Summary: ' + summary as Summary from Customers wont work at all.
Is there a way to accomplish this?TEXT columns really ought to be manipulated on the client, not the server. There are a number of reasons for this, most of which are design and performance issues.

If you really must manipulate a TEXT column on the server, you can use the UPDATETEXT (http://msdn2.microsoft.com/en-us/library/ms189466.aspx) statment, but I'll forewarn you that it is rather ugly.

You really ought to handle this on the client if you can't make the column a VARCHAR instead of a TEXT column.

-PatPsql

Adding to 2 different tables

Hi Everyone,

I have a page with a textbox and a dropdown list.
The user will enter a company name in the text box and select a number from 1 - 20 (number of delegates for that company) in the dropdown list.

I've got the text box and dropdown writing to tblCompany but I would also like it to write to tblUsers at the same time. The reason for this is that i need it to set up the number of users that have been selected in the dropdown list.

Here is the codebehind file:

Imports System.Data.SqlClientImports System.Web.ConfigurationPartialClass cms_Management_Company_NewCompanyInherits System.Web.UI.PageDim companyNameAs String Dim companyActiveAs Boolean Dim companyArchivedAs Boolean Dim companyDelegatesAs Integer Dim userForeNameAs String Dim userSurnameAs String Dim userEmailAs String Dim userUsernameAs String Dim userPasswordAs String Dim userActiveAs Boolean Dim userTypeIDAs Integer Dim companyIDAs Integer Dim iAs Integer Dim NoLoopsAs Integer Protected Sub btnSave_Click(ByVal senderAs Object,ByVal eAs System.Web.UI.ImageClickEventArgs)Handles btnSave.ClickDim conStringAs String = WebConfigurationManager.ConnectionStrings("General").ConnectionStringDim conAs New SqlConnection(conString)Dim cmdAs New SqlCommand("INSERT INTO tblCompany (CompanyName, CompanyActive, CompanyArchived, CompanyDelegates) VALUES (@.CompanyName, @.CompanyActive, @.CompanyArchived, @.CompanyDelegates)", con) cmd.Parameters.AddWithValue("@.CompanyName", companyName) cmd.Parameters.Item("@.CompanyName").Value = txtCompanyName.Text cmd.Parameters.AddWithValue("@.CompanyDelegates", companyDelegates) cmd.Parameters.Item("@.CompanyDelegates").Value = lstDel.SelectedValue cmd.Parameters.AddWithValue("@.CompanyActive", companyActive) cmd.Parameters.Item("@.CompanyActive").Value =True cmd.Parameters.AddWithValue("@.CompanyArchived", companyArchived) cmd.Parameters.Item("@.CompanyArchived").Value =False Using con con.Open() cmd.ExecuteNonQuery() con.Close()End UsingDim con2As New SqlConnection(conString)Dim cmd2As New SqlCommand("INSERT INTO tblUsers (UserForeName, UserSurname, UserEmail, UserUsername, UserPassword, UserActive, UserTypeID, CompanyID) VALUES (@.UserForeName, @.UserSurname, @.UserEmail, @.UserUsername, @.UserPassword, @.UserActive, @.UserTypeID, @.CompanyID)", con2) cmd2.Parameters.AddWithValue("@.UserForeName", userForeName) cmd2.Parameters.Item("@.UserForeName").Value ="First Name - Delegate 1" cmd2.Parameters.AddWithValue("@.UserSurname", userSurname) cmd2.Parameters.Item("@.UserSurname").Value ="Surname - Delegate 1" cmd2.Parameters.AddWithValue("@.UserEmail", userEmail) cmd2.Parameters.Item("@.UserEmail").Value ="Email Address - Delegate 1" cmd2.Parameters.AddWithValue("@.UserUsername", userUsername) cmd2.Parameters.Item("@.UserUsername").Value ="Username - Delegate 1" cmd2.Parameters.AddWithValue("@.UserPassword", userPassword) cmd2.Parameters.Item("@.UserPassword").Value ="Password - Delegate 1" cmd2.Parameters.AddWithValue("@.UserActive", userActive) cmd2.Parameters.Item("@.UserActive").Value =True cmd2.Parameters.AddWithValue("@.UserTypeID", userTypeID) cmd2.Parameters.Item("@.UserTypeID").Value = 2 cmd2.Parameters.AddWithValue("@.UserTypeID", userTypeID) cmd2.Parameters.Item("@.UserTypeID").Value = 1 Using con2 con2.Open()For i = 1To NoLoops cmd2.ExecuteNonQuery()Next i con2.Close()End Using Response.Redirect("~/cms/Management/Company/Company.aspx")End SubEnd Class

The other thing I am not sure of is getting the ID of the new company and assiging it to the delegates in tblUsers (to associate them with the new company)

I hope this makes sense.

Thank you very much guys.

Scott.

Hi,

To get the recently added record's ID use

SELECT SCOPE_IDENTITY()

and catch the returned value using

cmd.ExecuteScalar()

HTH
Regards

|||

Hi,

Thanks for the reply, where in the code would I put these elements? I am very new to .NET.

thanks again,

Scott.

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

Adding text to RS home page folder.aspx

I would like to be able to add some text to the SQL RS home page (folder.aspx I believe). Is this possible and if so, how? Also, what if I would like to add a graphic (logo) to it as well.

thanks!

Martha

Hi Martha,

Can you please clarify your question? What do you mean by SQL Reporting Service home page.

Sincerely,

Amde

|||What you can do is to tweak the CSS file which is included in Reporting Services to get a company look-a-like.

http://msdn2.microsoft.com/en-us/library/ms345247.aspx

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I would like to be able to go to the page that lists the SQL RS reports that can be run and add some text, for example, "Sales data last updated 6/23/06". The page where I would like to see this show up is http://servername/reports/Pages/Folder.aspx

thanks!

Adding text to a value?

Hello!

Hoping this is simple. I have a age SP that does the age from getdate and DOB. I also have it that does months if the year is zero. What I would like to do if the value is 10, and it's a month value, how would I add the "mo" to it. So it reads 10mo. Is this possible?

Thanks!

Rudy

use the "+" operator

@.Months_string = @.months + 'mo'

if @.months is a number field then cast it first:

@.Months_string = cast(@.months as varchar)+ 'mo'|||

You need to cast the number to a character and then just concatenate

selectconvert(varchar(3), @.age)+'mo.'

|||

Code Snippet

select case when yr > 0
then cast(yr as varchar) + ' Yr'
else cast(mo as varchar) + ' Mo'
end as theValue
from ( select 3 as yr, 3 as mo union all
select 1, 5 union all
select 0, 10
) a

/*
theValue
3 Yr
1 Yr
10 Mo
*/

|||

WOW!!

Thank you all! Great suggestions!

Rudy

|||

Ok!

I spoke way too soon! The first mistake I made was to trust I actually wrote the store procedure correct. LOL. It works, but it calculates the age wrong. I guess I was so excited it fiinally worked with out any errors, I didn't check if it was correct. My DOB field is date time, my Age field in nvarchar. He is my feeble attempt. I'm sure there is a better way to write this.

UPDATE Active_Orders

SET Age =CASEWHENdateadd(year,datediff(year, DOB,GetDate()), DOB)

<GetDate()THEN(datediff(year, DOB,GetDate()))- 1 ELSEdatediff(month, DOB,getdate())

% 12 ENDFROM Active_Orders

Any help on this would be greatly appreciated!

Thanks!!

Jim

|||

Jim:

Would something like this work for you:

Code Snippet

UPDATE Active_Orders
SET Age = CASE WHEN year(getdate() - cast(@.dob as datetime)) > 1900
THEN (datediff (year, DOB, GetDate())) - 1
ELSE datediff(month, DOB, getdate()) % 12
END
FROM Active_Orders

|||

Code Snippet

createtable #t1 (namevarchar(25), dob datetime)

insertinto #t1

select'Gomez','02/29/1908'union

select'Morticia','05/01/1936'union

select'Wednesday','10/31/1959'union

select'Friday','12/25/2006'

select*,

age =casewhendatediff(mm, dob,getdate())< 12

--months

thencasewhendatediff(mm, dob,getdate())< 0

then 0

elsedatediff(mm, dob,getdate())

end

else

-- years

datediff(mm, dob,getdate())/12

end

from #t1

So, the update statement would be:

Code Snippet

UPDATE Active_Orders

SET Age =casewhendatediff(mm, dob,getdate())< 12

--months

thencasewhendatediff(mm, dob,getdate())< 0

then 0

elsedatediff(mm, dob,getdate())

end

else

-- years

datediff(mm, dob,getdate())/12

end

FROM Active_Orders

Adding spaces inside text box values

I need to adding spaces inside the expression of a text box (in table header
or in page header).
For example: = "string 1" & " " & "string 2";
this expression returns only one space between string 1 and string 2.
Many thanksHi,
Just use =space(<no of spaces>) e.g space(10)
Amarnath
"Pasquale" wrote:
> I need to adding spaces inside the expression of a text box (in table header
> or in page header).
> For example: = "string 1" & " " & "string 2";
> this expression returns only one space between string 1 and string 2.
> Many thanks|||I have tried this suggest: it functions inside Visual Studio.NET environment
but then
his distribution I have seen only a single space!
How can I solve this issue?
Thanks
"Amarnath" wrote:
> Hi,
> Just use =space(<no of spaces>) e.g space(10)
> Amarnath
> "Pasquale" wrote:
> > I need to adding spaces inside the expression of a text box (in table header
> > or in page header).
> > For example: = "string 1" & " " & "string 2";
> > this expression returns only one space between string 1 and string 2.
> >
> > Many thanks|||Try adding the space inside the string that you need the space like
= "string 1 " & "string 2"; where the space is inserted aftter the 1.
"Pasquale" wrote:
> I need to adding spaces inside the expression of a text box (in table header
> or in page header).
> For example: = "string 1" & " " & "string 2";
> this expression returns only one space between string 1 and string 2.
> Many thanks|||I have used = string1 & " " & string2 and it has worked. But the room
used by the spaces is not the same as you see when you define it. Try to put
much more spaces between the strings and you will see the distance increase.
"Pasquale" wrote:
> I have tried this suggest: it functions inside Visual Studio.NET environment
> but then
> his distribution I have seen only a single space!
> How can I solve this issue?
> Thanks
>
> "Amarnath" wrote:
> > Hi,
> >
> > Just use =space(<no of spaces>) e.g space(10)
> >
> > Amarnath
> >
> > "Pasquale" wrote:
> >
> > > I need to adding spaces inside the expression of a text box (in table header
> > > or in page header).
> > > For example: = "string 1" & " " & "string 2";
> > > this expression returns only one space between string 1 and string 2.
> > >
> > > Many thanks|||I have posted this issue then executing some proofs to put some spaces in a
text box.
I have tried:
- = "string1 " & "string2";
- = "string1" & space(10) & "string2";
- = "string1" & " " & "string2";
- = "string1 " & " " & space(10) & "
string2".
These solutions function inside MS VisualStudio .NET (I see the results by
preview),
BUT NOT FUNCTION AFTER THEIR DISTRIBUTION (I see the results inside Internet
browser).
Many thanks
"PSM" wrote:
> I have used = string1 & " " & string2 and it has worked. But the room
> used by the spaces is not the same as you see when you define it. Try to put
> much more spaces between the strings and you will see the distance increase.
> "Pasquale" wrote:
> > I have tried this suggest: it functions inside Visual Studio.NET environment
> > but then
> > his distribution I have seen only a single space!
> >
> > How can I solve this issue?
> >
> > Thanks
> >
> >
> > "Amarnath" wrote:
> >
> > > Hi,
> > >
> > > Just use =space(<no of spaces>) e.g space(10)
> > >
> > > Amarnath
> > >
> > > "Pasquale" wrote:
> > >
> > > > I need to adding spaces inside the expression of a text box (in table header
> > > > or in page header).
> > > > For example: = "string 1" & " " & "string 2";
> > > > this expression returns only one space between string 1 and string 2.
> > > >
> > > > Many thanks|||The only thing it occur to me is to use non-breaking spaces. You can use
ChrW(160) as nonbreaking space and the Internet browser won't change them.
"Pasquale" wrote:
> I have posted this issue then executing some proofs to put some spaces in a
> text box.
> I have tried:
> - = "string1 " & "string2";
> - = "string1" & space(10) & "string2";
> - = "string1" & " " & "string2";
> - = "string1 " & " " & space(10) & "
> string2".
> These solutions function inside MS VisualStudio .NET (I see the results by
> preview),
> BUT NOT FUNCTION AFTER THEIR DISTRIBUTION (I see the results inside Internet
> browser).
> Many thanks
>
> "PSM" wrote:
> > I have used = string1 & " " & string2 and it has worked. But the room
> > used by the spaces is not the same as you see when you define it. Try to put
> > much more spaces between the strings and you will see the distance increase.
> >
> > "Pasquale" wrote:
> >
> > > I have tried this suggest: it functions inside Visual Studio.NET environment
> > > but then
> > > his distribution I have seen only a single space!
> > >
> > > How can I solve this issue?
> > >
> > > Thanks
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > Hi,
> > > >
> > > > Just use =space(<no of spaces>) e.g space(10)
> > > >
> > > > Amarnath
> > > >
> > > > "Pasquale" wrote:
> > > >
> > > > > I need to adding spaces inside the expression of a text box (in table header
> > > > > or in page header).
> > > > > For example: = "string 1" & " " & "string 2";
> > > > > this expression returns only one space between string 1 and string 2.
> > > > >
> > > > > Many thanks|||Excellent!
This is the solution! Many thanks
"PSM" wrote:
> The only thing it occur to me is to use non-breaking spaces. You can use
> ChrW(160) as nonbreaking space and the Internet browser won't change them.
>
> "Pasquale" wrote:
> > I have posted this issue then executing some proofs to put some spaces in a
> > text box.
> >
> > I have tried:
> > - = "string1 " & "string2";
> > - = "string1" & space(10) & "string2";
> > - = "string1" & " " & "string2";
> > - = "string1 " & " " & space(10) & "
> > string2".
> >
> > These solutions function inside MS VisualStudio .NET (I see the results by
> > preview),
> > BUT NOT FUNCTION AFTER THEIR DISTRIBUTION (I see the results inside Internet
> > browser).
> >
> > Many thanks
> >
> >
> >
> > "PSM" wrote:
> >
> > > I have used = string1 & " " & string2 and it has worked. But the room
> > > used by the spaces is not the same as you see when you define it. Try to put
> > > much more spaces between the strings and you will see the distance increase.
> > >
> > > "Pasquale" wrote:
> > >
> > > > I have tried this suggest: it functions inside Visual Studio.NET environment
> > > > but then
> > > > his distribution I have seen only a single space!
> > > >
> > > > How can I solve this issue?
> > > >
> > > > Thanks
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Just use =space(<no of spaces>) e.g space(10)
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "Pasquale" wrote:
> > > > >
> > > > > > I need to adding spaces inside the expression of a text box (in table header
> > > > > > or in page header).
> > > > > > For example: = "string 1" & " " & "string 2";
> > > > > > this expression returns only one space between string 1 and string 2.
> > > > > >
> > > > > > Many thanks|||Yeah, I just wasted 2 hours on trying to fix this, excellent :-)
In case you were wondering what is going on I can explain...
I have a SQL statement that returns a string of dates delimited by 8 spaces,
eg.
1-Mar-2006 2-Mar-2006 etc.
When in VS.Net IDE the preview window shows the spaces correctly but when
deployed to a website they are displayed as HTML and hence the multiple
spaces are ignored and displayed as a single space.
Try putting in and Reporting services sees the & and converts it to a html
&, so your source looks like , insert scream here.
CharW(160) gets past this and is rendered into HTML by RS as
Thanks again, I will be able to sleep tonight.
John
"Pasquale" wrote:
> Excellent!
> This is the solution! Many thanks
>
> "PSM" wrote:
> > The only thing it occur to me is to use non-breaking spaces. You can use
> > ChrW(160) as nonbreaking space and the Internet browser won't change them.

adding odbc to linked server

I have a odbc driver to a customized text file system (on aix), I am using
winsql to query from that sysem, does sql server allow to register it as a
linked server. I am guessing i should be able to register as i have odbc
driver to the system. Please suggest me if it is possible
When i try to do it, i am getting message like i mentioned here
Error 7399: OLE DB provider 'MSDASQL' reported an error.
Client unable to establish connection error:1408F0C6:SSL3_GET_RECORD: packet
length too long]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ],
Thanks,
Subbu.
Hi
ODBC datasource use the MSDASQL provider and require a System DSN. This
error can occur if it is a User DSN. See sp_addlinkedserver in books online
for an example. If you still have problems you may want to turn ODBC tracing
on to debug it.
John
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:%23XK7zvYzEHA.576@.TK2MSFTNGP14.phx.gbl...
>I have a odbc driver to a customized text file system (on aix), I am using
> winsql to query from that sysem, does sql server allow to register it as a
> linked server. I am guessing i should be able to register as i have odbc
> driver to the system. Please suggest me if it is possible
> When i try to do it, i am getting message like i mentioned here
> Error 7399: OLE DB provider 'MSDASQL' reported an error.
> Client unable to establish connection error:1408F0C6:SSL3_GET_RECORD:
> packet
> length too long]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
> returned 0x80004005: ],
>
> Thanks,
> Subbu.
>
|||There is a problem with odbc driver after i corrected it i am able to
register as linked server without any errors, and i can see the list of
tables in enterprise manager, but I am not able to run a query , see below
for error message i am getting for a query
query:
select CLI_ID, CLIENT_NAME
from IMPACT...CLI WHERE CLI_ID = '00001083'
error message:
Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.
Can you please give me more details how to to turn ODBC tracing on to debug.
Thanks,
Subbu.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:enu1IB0zEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Hi
> ODBC datasource use the MSDASQL provider and require a System DSN. This
> error can occur if it is a User DSN. See sp_addlinkedserver in books
online
> for an example. If you still have problems you may want to turn ODBC
tracing[vbcol=seagreen]
> on to debug it.
> John
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
> news:%23XK7zvYzEHA.576@.TK2MSFTNGP14.phx.gbl...
using[vbcol=seagreen]
a
>
|||Hi
In the ODBC Data Source Manager applet (either in control panel or on
the Administrators menu), you will see a trace tab, with a button to
start tracing. At a guess CLI_ID is being interpreted as text, ntext
or image and that you need to specify CLI_ID LIKE '00001083'.
John
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message news:<u8FDdVX0EHA.2624@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> There is a problem with odbc driver after i corrected it i am able to
> register as linked server without any errors, and i can see the list of
> tables in enterprise manager, but I am not able to run a query , see below
> for error message i am getting for a query
> query:
> select CLI_ID, CLIENT_NAME
> from IMPACT...CLI WHERE CLI_ID = '00001083'
> error message:
> Server: Msg 306, Level 16, State 1, Line 1
> The text, ntext, and image data types cannot be compared or sorted,
> except when using IS NULL or LIKE operator.
>
> Can you please give me more details how to to turn ODBC tracing on to debug.
> Thanks,
> Subbu.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:enu1IB0zEHA.1452@.TK2MSFTNGP11.phx.gbl...
> online
> tracing
> using
> a
|||I started odbc tracing , but could not understand the log. If you want to
see i can paste its contents but the log file size is 2MB. I dont have a
clue to proceed further, can you please help me.
Query:
select CLI_ID, CLIENT_NAME
from IMPACT...CLI WHERE CLI_ID like '00001083'
Error message:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'CLI_ID' (compile-time
ordinal 1) of object 'CLI' was reported to have a DBCOLUMNFLAGS_ISLONG of
128 at compile time and 0 at run time].
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3b81e6a.0411231254.6732697d@.posting.google.co m...
> Hi
>
> In the ODBC Data Source Manager applet (either in control panel or on
> the Administrators menu), you will see a trace tab, with a button to
> start tracing. At a guess CLI_ID is being interpreted as text, ntext
> or image and that you need to specify CLI_ID LIKE '00001083'.
> John
>
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:<u8FDdVX0EHA.2624@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
below[vbcol=seagreen]
debug.[vbcol=seagreen]
This[vbcol=seagreen]
it as[vbcol=seagreen]
odbc[vbcol=seagreen]
error:1408F0C6:SSL3_GET_RECORD:[vbcol=seagreen]
IDBInitialize::Initialize[vbcol=seagreen]
|||Hi
Searching google for "OLE DB provider 'MSDASQL' supplied inconsistent
metadata for a column" turns up quite a few posts, so you can gain
solace in that you are not alone! If you ran the query without the
where clause does it still cause a problem? Suggestions from other
posts include using OPENQUERY or using the Oracle OLEDB driver
instead. This post points you to a KB article on the error and how you
may gain more information:
http://tinyurl.com/55x93
HTH
John
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message news:<#wvDYOk0EHA.3500@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> I started odbc tracing , but could not understand the log. If you want to
> see i can paste its contents but the log file size is 2MB. I dont have a
> clue to proceed further, can you please help me.
> Query:
> select CLI_ID, CLIENT_NAME
> from IMPACT...CLI WHERE CLI_ID like '00001083'
> Error message:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' supplied inconsistent metadata for a column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column 'CLI_ID' (compile-time
> ordinal 1) of object 'CLI' was reported to have a DBCOLUMNFLAGS_ISLONG of
> 128 at compile time and 0 at run time].
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3b81e6a.0411231254.6732697d@.posting.google.co m...
> news:<u8FDdVX0EHA.2624@.TK2MSFTNGP11.phx.gbl>...
> below
> debug.
> This
> online
> tracing
> using
> it as
> a
> odbc
> error:1408F0C6:SSL3_GET_RECORD:
> IDBInitialize::Initialize

Adding new lines into results in the

I used to be able to enter new lines into the result pane cell for text
(and varchar) data in Enterprise Manager, but now that I am using SQL
2005 Management Studio, this feature is gone.

Is there any way to do this?

Also, copying to/from excel chops off part of the text in a cell and is
very infuriating.

Any help would be appreciated.

Dan(monkeyboydan@.gmail.com) writes:
> I used to be able to enter new lines into the result pane cell for text
> (and varchar) data in Enterprise Manager, but now that I am using SQL
> 2005 Management Studio, this feature is gone.
> Is there any way to do this?
> Also, copying to/from excel chops off part of the text in a cell and is
> very infuriating.

Time to learn to write INSERT and UPDATE statements, I see!

There are plenty of differences between the tools in SQL 2000 and SQL 2005.
Keep in mind that Open table is intended to be a fairly simple tool to
view and edit data. For more heavy-duty stuff, you would use an application.

--
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|||Thanks for the reply.

Your point is noted and I am quite happy to do insert and update
statements but there are occasions where a little ad-hoc editing and
copying and pasting is useful and this isn't possible any more and it
seems a bit silly 'cause it makes the 'open table' functionality
vritually pointless is a lot of occasions.

Adding Misc files to Solution Explorer

I assume this is the best forum for this quesiton; if not, please direct me.

I have noticed that I can add (for example) a text file to a misc folder in a SQL Server project by dragging and dropping the file from Windows Explorer onto the Misc folder inside the SQL Server project. Is this the only way to add a file?

I noticed, for example, I could not copy and past to the Misc file folder.
Is there another way? If so, what is it? what is the preferred way?

I noticed that there have bee 55 views of this thread. It was posted Monday morning. would someone take a stab at it?|||The other way to add a file is to right click on the Project, select Add Existing Item, then Browse to the file in question (you need to change the File Type at the bottom to "All" in order to see text files, etc.).

Thanks, MJ

Adding large amounts of text

this may seem like a simple question, but I have a report/lease agreement I need to put together and wanted to know the simpliest way to add large amounts of text. Basically its all the legal stuff most leases include in the amount of some 14 pages.

Should this be just one long string-- or does ssrs have another way to format this

thanks as always

KM

Have them all in one textbox and set the width of the textbox to the maximum required and set CanGrow=true to allow increase in height.

Then you can type your long string in the textbox and do som formatting in it. You can use Chr() function to do some formatting like carriage return, line feed, tab and many more.

Shyam

|||

thanks will give it a try and let ya know

km

Adding Full Text Indexing to Sharepoint in SQL Server

We had Sharepoint running on SBS with an MSDE database. We then upgraded
SBS to full blown SLQ Server.
I want to add full text indexing to SharePoint database but I cannot find
it. When I open Enterpise Manager I do not see a Sharepoint database.
Can anyone tell me how I add full text indexing to a Sharepoint database?
Hi Dave,
Thanks for your posting!
For using SQL Full-text indexing for WSS, you will need to migrate the
database from MSDE to SQL server; this WSS admin guide article will
introduce the detailed steps for you:
Migrating from WMSDE to SQL Server
http://www.microsoft.com/resources/d...inguide/en-us/
stsf17.mspx
Please ensure your SQL server has installed Full text indexing. No any
searching particular component is developed at WSS which depends SQL server
to perform the searching.
Then from the "Central Administration" page of WSS (start->Control
Panels->Administrative Tools->Sharepoint Central Administration), click the
link "Configure full-text search" under Component Configuration; then
select the checkbox "Enable full-text search and index component".
You are encourage to raise this question to Sharepoint newsgroup, which I
believe will let you get more quickly and better from Support Professionals
for SharePoint.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Adding Full Text Indexing to Sharepoint in SQL Server

We had Sharepoint running on SBS with an MSDE database. We then upgraded
SBS to full blown SLQ Server.
I want to add full text indexing to SharePoint database but I cannot find
it. When I open Enterpise Manager I do not see a Sharepoint database.
Can anyone tell me how I add full text indexing to a Sharepoint database?Hi Dave,
Thanks for your posting!
For using SQL Full-text indexing for WSS, you will need to migrate the
database from MSDE to SQL server; this WSS admin guide article will
introduce the detailed steps for you:
Migrating from WMSDE to SQL Server
http://www.microsoft.com/resources/...minguide/en-us/
stsf17.mspx
Please ensure your SQL server has installed Full text indexing. No any
searching particular component is developed at WSS which depends SQL server
to perform the searching.
Then from the "Central Administration" page of WSS (start->Control
Panels->Administrative Tools->Sharepoint Central Administration), click the
link "Configure full-text search" under Component Configuration; then
select the checkbox "Enable full-text search and index component".
You are encourage to raise this question to Sharepoint newsgroup, which I
believe will let you get more quickly and better from Support Professionals
for SharePoint.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Adding Full Text Indexing to Sharepoint in SQL Server

We had Sharepoint running on SBS with an MSDE database. We then upgraded
SBS to full blown SLQ Server.
I want to add full text indexing to SharePoint database but I cannot find
it. When I open Enterpise Manager I do not see a Sharepoint database.
Can anyone tell me how I add full text indexing to a Sharepoint database?Hi Dave,
Thanks for your posting!
For using SQL Full-text indexing for WSS, you will need to migrate the
database from MSDE to SQL server; this WSS admin guide article will
introduce the detailed steps for you:
Migrating from WMSDE to SQL Server
http://www.microsoft.com/resources/documentation/wss/2/all/adminguide/en-us/
stsf17.mspx
Please ensure your SQL server has installed Full text indexing. No any
searching particular component is developed at WSS which depends SQL server
to perform the searching.
Then from the "Central Administration" page of WSS (start->Control
Panels->Administrative Tools->Sharepoint Central Administration), click the
link "Configure full-text search" under Component Configuration; then
select the checkbox "Enable full-text search and index component".
You are encourage to raise this question to Sharepoint newsgroup, which I
believe will let you get more quickly and better from Support Professionals
for SharePoint.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Adding comments to TEXT field...

Newbie question:

Aside from the single quote (i.e. chr(39)) what other characters can cause
MS-SQL
server to cough-up the insertion / update back in your face?

TIAA. Nonymous (someone@.hotmail.com) writes:
> Aside from the single quote (i.e. chr(39)) what other characters can cause
> MS-SQL
> server to cough-up the insertion / update back in your face?

None, what I can think of. Well if you use the double " as delimiter,
then this is the odd one out. But you can only use " with certain
settings.

Anyway, to include the string delimiter in the string, you double it:

SELECT 'Three o''clock'

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp