Manipulating Xml using Sql Server

Hi.
I need some help please...
There is a field, type text, in a table, that conatins xml
document.
Is it possible, using sql server to do the following?
1. Get the Xml from that field
2. Search the xml for a specific element ( by using its
name )
3. Search in this element, a certain attribute.
4. If the attribute exists, modify its value.
5. Save it bacl to the table
I guess that I am looking for some kind of an XML DOM
inside Sql server.
Is it possible?
Thanks a lot
RoyAt the server level SQL provides very limited support for manipulating the
XML DOM.
A better option is to use client side libraries like SQLXML to manipulate
DOM objects.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Roy" <roy@.smsolutions.co.nz> wrote in message
news:07ce01c3b30a$3d9cffd0$a401280a@.phx.gbl...
> Hi.
> I need some help please...
> There is a field, type text, in a table, that conatins xml
> document.
> Is it possible, using sql server to do the following?
> 1. Get the Xml from that field
> 2. Search the xml for a specific element ( by using its
> name )
> 3. Search in this element, a certain attribute.
> 4. If the attribute exists, modify its value.
> 5. Save it bacl to the table
> I guess that I am looking for some kind of an XML DOM
> inside Sql server.
>
> Is it possible?
> Thanks a lot
> Roy
>|||"Roy" <roy@.smsolutions.co.nz> wrote in message
news:07ce01c3b30a$3d9cffd0$a401280a@.phx.gbl...
Roy, you can use sp_OA procs to instantie XML Dom object. The basic idea is
to create stored proc that receives xml data through text type input
parameter. now you can (using sp_OA) create xmldocument object and work with
it. It's complicated, and i'm not sure if this is the best way to do it (or
even recomended), but you can do it this way.
Regards,
Tomislav Kralj
MCSD/.NET, MCDBA
tomislav.kralj1@.zg.tel.hr

Manipulating varchars as Datetime

Hi all,

I am a little weak at SQL, so bear with me.

Can anyone give me an SP which uses "Between" to Display all Dates

BETWEENa fromDate and toDate?

Two conditions

1.Both Dates are stored asvarchar.

2. I should be able to get the dates even if years are different, say between 20th December 2006 and 3rd January 2007

3.Is there a way to extract the yearDiff?

Regards,

Naveen

Hi

Try Datediff function in SQLSERVER

Bye

|||

You can use DateDiff as stated by deepakleo however there's a few things you need to make sure takes place.

1. Need to make sure that since your dates are being stored as varchar that they are being stored in a format that can be cast as datetime.

2. You need to cast them as datetime to determine the date diff.

datediff("YYYY", Cast('December 20 2006' as datetime), Cast('January 3 2007' As DateTime))

The above will give you the difference in years between the 2 dates. If you want the difference in dates then use "dd" instead of "YYYY" and "mm" for the difference in months.

Manipulating the result set of one stored procedure from another....

Hi,

I have one stored procedure that calls another ( EXEC proc_abcd ). I would
like to return a result set (a temporary table I have created in the
procedure proc_abcd) to the calling procedure for further manipulation. How
can I do this given that TABLE variables cannot be passed into, or returned
from, a stored procedure?

Thanks,

Robin

Example: (if such a thing were possible):

DECLARE @.myTempTable1 TABLE ( ID INT NOT NULL )
DECLARE @.myTempTable2 TABLE ( ID INT NOT NULL )

....
/*
Insert a test value into the first temporary table
*/

INSERT INTO @.myTempTable1 VALUES ( 1234 )
....

/*
Execute a stored procedure returning another temporary table of
values.
*/

EXEC proc_abcd @.myTempTable2 OUTPUT

...
...

/*
Insert the values from the second temporary table into the first.
*/

SELECT * INTO @.myTempTable1 FROM @.myTempTable2Robin Tucker (idontwanttobespammedanymore@.reallyidont.com) writes:
> I have one stored procedure that calls another ( EXEC proc_abcd ). I
> would like to return a result set (a temporary table I have created in
> the procedure proc_abcd) to the calling procedure for further
> manipulation. How can I do this given that TABLE variables cannot be
> passed into, or returned from, a stored procedure?

Have a look at http://www.sommarskog.se/share_data.html where I discuss
various techniques.

> SELECT * INTO @.myTempTable1 FROM @.myTempTable2

You cannot do a SELECT INTO with a table variable.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Manipulating Text,nText data types filed in tsql

I have to run a dynamic sql that i save in the database as a TEXT data type(due to a large size of the sql.) from a .NET app. Now i have to run this sql from the stored proc that returns the results back to .net app. I am running this dynamic sql with sp_executesql like this..

EXEC sp_executesql@.Statement,N'@.param1 varchar(3),@.param2 varchar(1)',@.param1,@.param2,
GO

As i can't declare text,ntext etc variables in T-Sql(stored proc), so i am using this method in pulling the text type field "Statement".

DECLARE@.Statement varbinary(16)
SELECT@.Statement = TEXTPTR(Statement)
FROM table1
READTEXT table1.statement@.Statement 0 16566

So far so good, the issue is how to convert @.Statment varbinary to nText to get it passed in sp_executesql.

Note:- i can't use Exec to run the dynamic sql becuase i need to pass the params from the .net app and Exec proc doesn't take param from the stored proc from where it is called.

I would appreciate if any body respond to this.

Yes, the limitation of using NTEXT as variable causes the problem. I know a workaround, but... you still need EXEC to warp the execution of sp_executesql. Read this:

sp_executesql and Long SQL Strings in SQL 2000
There is a limitation with sp_executesql on SQL 2000 and SQL 7, since you cannot use longer SQL strings than 4000 characters. (On SQL 2005, use nvarchar(MAX) to avoid this problem.) If you want to use sp_executesql despite you query string is longer, because you want to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in EXEC():

DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2)
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
EXEC('EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2)'',
@.state = ''' + @.state + '''')
This works, because the @.stmt parameter to sp_executesql is ntext, so by itself, it does not have any limitation in size.

You can even use output parameters by using INSERT-EXEC, as in this example:

CREATE TABLE #result (cnt int NOT NULL)
DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2),
@.mycnt int
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT @.cnt = COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
INSERT #result (cnt)
EXEC('DECLARE @.cnt int
EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2),
@.cnt int OUTPUT'',
@.state = ''' + @.state + ''',
@.cnt = @.cnt OUTPUT
SELECT @.cnt')
SELECT @.mycnt = cnt FROM #result
You have my understanding if you think this is too messy to be worth it.

So you can break the NTEXT statement from your table into several NVARCHAR(4000) strings, then pass the strings as parameter for sp_executesql. The original wonderful artilc can be found here:

http://www.sommarskog.se/dynamic_sql.html

|||Thanks, I had to break the query into pieces to get this working.

Manipulating SQL server databases dynamically

Hi friends,

I have problem in sending my T-SQL statements, which i generate dynamically with the help of "user entered attributes", to SQl Server.

I need my T-SQL statements to get passed to the SQl Server ,which i enter in a "Rich text Box " in my appication that i develop using Vc# .net 2005,when i click a button which i placed in my "winform".And the queries Should be executed exectly as it is, and i should get the output back in my Winform...

But,Make sure that the queries are being obtained from a rich text box placed in the Winform...

So, please assist me regarding this issue...

R.Rajaraman

Hi,

Here is an example:

string query = txt.Text;

string connStr = "";//your connection string..

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr);

System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = query;

cmd.CommandType = CommandType.Text;

System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();

//continue..

Another option is to use SqlDataAdapter that will fill a dataset. If you need help with that let me know.

Regards,

|||

hi

i found your answer very usefull .....

But ,i need the queries directly to be passed from my rich text box control that i use in my winform....

please help me with this issue...

|||

Hi,

Perhaps I don't undestand what you mean. What do you mean directly?

If I got it right it is when you click the button that the query should get executed.

If so you should add the sample i sumbitted to the onclick event.

Regards,

|||

hi,

Thanks for the help guy... actually what i want to be done is as follows..

"I am going to enter some T-SQL statements in my Rich text Box that i placed in my form.And, i have a button named "Execute" in my win form. If I click on that button, the entire set of statements placed in the rich text box should get passed to SQL Server Query analyzer and my query batch should get executed.And ,i have to get back the results in my winform itself back..."

In Short, i need the functionality of a "Query Analyzer"..

Could you please help me with this issue......

|||

Hi,

First let me say that way you execute a query in the query analyzer you get for each statement (select/insert and ect.) a table. You can look at it as dataSet.

So, you need to fill a dataset with the query result and display it. In order to do that you first need to use the SqlDataAdapter and set the commands for it. Then you can use the fill method in the adapter to fill the result. An other option that you can try is using the data application block (or enterprise libraries) and you will get a method ExecuteDataSet(...).

Let me know if that helps.

Regards,

|||

This thread was moved to this forum (SQL Server Data Access) as the topic is more relevant here. The forum where it originally was posted (.NET Framewotk Inside SQL Server) deals with writing and running .NET code inside SQL Server (stored procs, funtions etc).

Niels

Manipulating parameters passed into a report - errors with parameters

Hi,
I've discovered a problem with repserv sp1 that I just can't see a way
around at the moment.
Basically we have a custom front end that allows the user to select
the params from either dropdown controls or textboxes. The text boxes
allow four states:
1/ All records
2/ Exact match
3/ Partial Match
4/ Starts with
The partial match is causing me the problem. Basically in the code
behind i'm prepending/appending the like clause character % to the
contents of the textbox i.e.
user enters 0123 into the textbox, the param passed into the report is
%0123%
Thats where the problem lies, repserv doesnt seem to accept that as a
valid param. It doesnt give any errors but it doesnt display the
required resultset. In the params returned to the user on the report,
it shows
23% has been passed in. The same thing happens if I type the url in
manually i.e.
http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%0123%&NameRefType=3
NameRefValue is declared as a string param, NameRefType is declared as
an integer param
Anyone have any ideas on this? Is it possible to manipulate the params
passed into the report before the engine actually processes them? If
this is possible I could just pass in the string without the %
characters & based on NamRefType value, add the % in the report itself
before the dataset is returned.
Cheers
SiSi wrote:
> Hi,
> I've discovered a problem with repserv sp1 that I just can't see a way
> around at the moment.
> Basically we have a custom front end that allows the user to select
> the params from either dropdown controls or textboxes. The text boxes
> allow four states:
> 1/ All records
> 2/ Exact match
> 3/ Partial Match
> 4/ Starts with
> The partial match is causing me the problem. Basically in the code
> behind i'm prepending/appending the like clause character % to the
> contents of the textbox i.e.
> user enters 0123 into the textbox, the param passed into the report is
> %0123%
>
Just read this the other day:
"NOTE Wildcards in Your SQL The filters you specify for your reports
are based on Visual Basic .Net 2003 syntax, so you'll find that the
pattern matching with the Like operator uses * as a wildcard and not %,
as you might use in SQL Like expressions."
_Hitchhiker's Guide to SQL Server 2000 Reporting Services_ by Peter
Blackburn and William R. Vaughan, Addison-Wesley 2005, p. 272.
hth
--Mike|||Thanks Mike,
I've just tried it and although the strange corruption of the
parameter is now ok, I still get incorrect results returned.
I use the % syntax in the starting with section with no problems, it
just seems to fail on integers and partial matches
Cheers anyway, it gave me a couple of ideas there!
Si
On Mon, 14 Feb 2005 07:54:53 -0600, "Mike Donnellan"
<spamspamspamandspam@.AT@.donnellanDOTcom> wrote:
>Just read this the other day:
>"NOTE Wildcards in Your SQL The filters you specify for your reports
>are based on Visual Basic .Net 2003 syntax, so you'll find that the
>pattern matching with the Like operator uses * as a wildcard and not %,
>as you might use in SQL Like expressions."
>_Hitchhiker's Guide to SQL Server 2000 Reporting Services_ by Peter
>Blackburn and William R. Vaughan, Addison-Wesley 2005, p. 272.
>hth
>--Mike|||Percent character is used to URL encode/escape other characters, so it
should itself also be encoded.
Check if this url works:
http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%250123%25&NameRefType=3
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Si" <no@.spam.thanks> wrote in message
news:mn4111pcl3qv3p6803i6pbstmhpch4lcge@.4ax.com...
> Hi,
> I've discovered a problem with repserv sp1 that I just can't see a way
> around at the moment.
> Basically we have a custom front end that allows the user to select
> the params from either dropdown controls or textboxes. The text boxes
> allow four states:
> 1/ All records
> 2/ Exact match
> 3/ Partial Match
> 4/ Starts with
> The partial match is causing me the problem. Basically in the code
> behind i'm prepending/appending the like clause character % to the
> contents of the textbox i.e.
> user enters 0123 into the textbox, the param passed into the report is
> %0123%
> Thats where the problem lies, repserv doesnt seem to accept that as a
> valid param. It doesnt give any errors but it doesnt display the
> required resultset. In the params returned to the user on the report,
> it shows
> 23% has been passed in. The same thing happens if I type the url in
> manually i.e.
> http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%0123%&NameRefType=3
> NameRefValue is declared as a string param, NameRefType is declared as
> an integer param
> Anyone have any ideas on this? Is it possible to manipulate the params
> passed into the report before the engine actually processes them? If
> this is possible I could just pass in the string without the %
> characters & based on NamRefType value, add the % in the report itself
> before the dataset is returned.
> Cheers
> Si|||Thanks Lev,
Yes that does seem to work ok. IS there a specific way I should be
encoding these characters or simply replace % with %25 ?
Cheers
Si
On Mon, 14 Feb 2005 21:16:54 -0800, "Lev Semenets [MSFT]"
<levs@.microsoft.com> wrote:
>Percent character is used to URL encode/escape other characters, so it
>should itself also be encoded.
>Check if this url works:
>http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%250123%25&NameRefType=3|||As a rule, I always try to limit query-string parameter values to the
actual value, and do formatting elsewhere.
Instead of passing the value %0123% via the query-string, why don't you
just pass the 0123 and add the "%" characters via an expression within
the RDL. This should completely avoid the problem you encountered with
encoding.
~Lance
http://weblogs.asp.net/lhunt/|||Thanks Lance,
Thats what I originally wanted to do but was unsure as to how to
manipulate the parameters before passing through to the stored proc.
In the end i've passed it all through as you say but direct to the
stored proc for processing & it now works fine.
I'd still be interested to know how to do it the way you mention.
Regards
Si
On 15 Feb 2005 07:02:36 -0800, "Lance" <lancehunt@.gmail.com> wrote:
>As a rule, I always try to limit query-string parameter values to the
>actual value, and do formatting elsewhere.
>Instead of passing the value %0123% via the query-string, why don't you
>just pass the 0123 and add the "%" characters via an expression within
>the RDL. This should completely avoid the problem you encountered with
>encoding.
>~Lance
>http://weblogs.asp.net/lhunt/|||I would normally recommend using the exact solution you chose, except I
wasnt sure if you were using a StoredProcedure or SQL. You definitely
should stick with your current implementation.
However, there are some cases where you can't use Stored Procedures,
such as with many ODBC connections to legacy systems. In such cases, I
recommend dynamically building your value for the LIKE expression in
the RDL.
Here are the basic steps:
1. Open "Data" tab from designer.
2. Select your DataSet from dropdown
3. Click on the "..." to go to properties.
4. Click on the Parameters tab
5. Locate the parameter in question
6. Modify the parameter value (right hand column) to use an expression.
7. Use the expression:
"="%" & Parameters!MyParam.Value & "%"
Instead of the default expression
"=Parameters!MyParam.Value"
8. Close Properties and you're ready to go!
Enjoy!
Lance Hunt
http://weblogs.asp.net/lhunt/

Manipulating output

What can I do to accomplish the following:

Output 13 character values with leading zeros (599826 output as 0000000599826)

Outputting 30 char values with trailing blanks if necessary (20 char string with 10 char trailing blanks/spaces)

Can this be done in SQL?

Any help would be appreciated.Which DBMS?

For Oracle use LPAD and RPAD functions, e.g.

SQL> select lpad('599826',13,'0') from dual;

LPAD('599826'
----
0000000599826

SQL> select rpad('599826',13,'0') from dual;

RPAD('599826'
----
5998260000000|||I'm sorry, I'm using SQL Server 2000.|||I just took a look at the SQL Server docs and couldn't see equivalent functions - but then I'm no SQL Server expert. However, you could do it with a combination of other functions - something like:

left('0000000000000',13-len(string))||string|||What can I do to accomplish the following:

Output 13 character values with leading zeros (599826 output as 0000000599826)

Outputting 30 char values with trailing blanks if necessary (20 char string with 10 char trailing blanks/spaces)

Can this be done in SQL?

Any help would be appreciated.The leading zeros are relatively easy as long as you don't have to cope with negative numbers. To zero fill positive numbers on the left, you can use:SELECT Replace(Str(599826, 13), ' ', '0')Dealing with negative numbers is enough more complicated that I recommend a user-defined function to avoid the expression clutter.

To space fill a string on the right, you can use:SELECT Cast('xyzzy' AS Char(30))These can also be combined if you like.

-PatP|||PatP, thanks.

manipulating ntext in stored procedures

Hi,

I have been trying to write a SP where, in the result set is brought back, two ntext columns are combined with a text string, However I get an error when I click on apply;

"Error 403: Invalid operator for data type. Operator equals add, type equals ntext"

I can only assume that this means you can't add (or even manipulate) ntext columns in a SP.

Short of changing the sp to bring back 3 columns and to do the manipulation in my c# code I can't think of a way round this.

Anyone with any ideas?

thanks in advance

rich

SP code ( I have highlighted the problem area):

CREATE PROCEDURE [dbo].[usp_RCTReportQuery]

@.PersonType varchar(255),
@.Status varchar(255),
@.Oustanding varchar(255)

AS

DECLARE @.PersonTypeID int,
@.StatusID int,
@.OustandingID int

SELECT @.PersonTypeID = PersonTypeID FROM TBL_LU_People_Type WHERE PersonType = @.PersonType
SELECT @.StatusID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Status
SELECT @.OustandingID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Oustanding

SELECT surname as [Potential Claimant], s.thevalue + '<BR /><BR />' + o.thevalue as [Status]
FROM tbl_people p
INNER JOIN tbl_Lu_people lup on lup.personid = p.personid and persontypeid = @.PersonTypeID
LEFT JOIN tbl_memo s on s.caseID = p.caseID and s.memotypeid = @.StatusID
LEFT JOIN tbl_memo o on o.caseID = p.caseID and o.memotypeid = @.OustandingID
GO

Richie:

If you are running SQL Server 2005 you might be able to leverage a function to work around this issue. Perhaps a function like this:

alter function dbo.appendText
( @.prm_textIn nvarchar(max),
@.prm_appendString varchar (8000)
)
returns nvarchar (max)
as

begin

return ( @.prm_textIn + @.prm_appendString )

end

This is coded as a scalar function; you might be better off setting it up as an inline function so that you can potentially leverage it in the future with the CROSS JOIN operator for other purposes. Below is an example:

declare @.what nvarchar (max) set @.what = ''
declare @.firstPart varchar (40) set @.firstPart = ' ** This is'
declare @.secondPart varchar (40) set @.secondPart = ' a test. **'

select @.what = @.what + ' #' + convert (nvarchar(5), iter)
from small_iterator (nolock)

select len (@.what) as [len],
substring (dbo.appendText (@.what, @.firstPart + @.secondPart), len(@.what) - 6, 30)
as [righthand piece]

-- Sample Output: --

-- len righthand piece
-- -- --
-- 218263 #32767 ** This is a test.

Dave

|||if you are on 2005, you could just use a nvarchar(MAX) column instead of ntext.

www.elsasoft.org|||

Richie:

You might be able to get your query to work in SQL 2005 doing something like this:

SELECT surname as [Potential Claimant], cast (s.thevalue as nvarchar (max)) + '<BR /><BR />' + o.thevalue as [Status]

|||

Please specify the version of SQL Server in your posts so it is easy to suggest the correct solution.

1. If you are using SQL Server 2005 then use varchar(max), nvarchar(max), and varbinary(max). The text, ntext, and image data types have been deprecated. The newer max data types will allow you to manipulate the values on the server-side just like regular varchar, nvarchar or varbinary values. Also, for backward compatibility reasons any expression that involves string concatenation will return only a string of maximum length 8000 bytes. In order to concatenate larger length strings you have to cast at least one of the values to varchar(max) or nvarchar(max). So in your SELECT list, you could do something like CAST(s.thevalue as varchar(max)) + '<BR/><BR>' + o.thevalue.

2. If you are not using SQL Server 2005 then there is no way to concatenate strings larger than 8000 bytes. You have to either dump the rows into a temporary table and use UPDATETEXT to manipulate each row individually or simply return the values as multiple columns and concatenate on the client-side

Lastly, you should actually leave the presentation to the client side and do these type of operations on the server. What if you want to later introduce a paragraph break or ident the line? What if the formatting is more complex? You should just return the strings as is to the client and then format it there.

|||

Cheers for the tip. Unfortunately I am still using Sql Server 2000 :(

I tried creating a function but you can't have variables of type ntext in functions.

Is there anyway I can do this in SQL Server 2000?

|||I am using sql server 2000 and try using table temp.

Whats wrong?

ROBUST PLAN. Somebody help?

---

No

se puede crear una fila de tabla de trabajo más larga que el máximo

admitido. Vuelva a enviar la consulta con la sugerencia ROBUST PLAN.

TRANSLATION

Quote:

Originally Posted by

A

row of table of work cannot be created longer than the admitted

maximum. Return to send the consultation with suggestion ROBUST PLAN.

Cant create line in table work longer,

CREATE TABLE temp
(
Proc_id INT,
Proc_Name SYSNAME,
Definition NTEXT
)

-- get the names of the procedures that meet our criteria
INSERT temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

--Inicializar the NTEXT column
UPDATE temp SET Definition=''

CODE --

DECLARE

@.txtPval binary(16),

@.txtPidx INT,

@.curName SYSNAME,

@.curtext NVARCHAR(4000)
--

--
DECLARE C CURSOR

LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

SELECT OBJECT_NAME(id), text

FROM syscomments s

INNER JOIN #MYTEMP t

ON s.id = t.Proc_id

ORDER BY id, colid

OPEN c -> HERE TELL ME ERROR about robust plan.

FETCH NEXT FROM c INTO @.curname, @.curtext

--Start Loop

WHILE (@.@.FETCH_STATUS = 0 )
BEGIN
--get pointer for the current procedure name colid
SELECT @.txtPval = TEXTPTR(Definition)
FROM temp
WHERE Proc_Name = @.curname

--find out where to append the @.temp table′s value

SELECT @.txtPidx = DATALENGTH(Definition)/2
FROM temp
WHERE Proc_Name = @.curName

--Apply the append of the current 8kb chunk
UPDATETEXT temp.definition @.txtPval @.txtPidx 0 @.curtext
FETCH NEXT FROM c INTO @.curName, @.curtext
END

-- check what was produced
SELECT Proc_name, Definition, DATALENGTH(Definition)/2
FROM temp

-- check our filter
SELECT Proc_Name, Definition
FROM temp
WHERE definition LIKE '%foobar%'

--Clean up

DROP TABLE temp
CLOSE c
DEALLOCATE c
Thanks for try help.

manipulating ntext in stored procedures

Hi,

I have been trying to write a SP where, in the result set is brought back, two ntext columns are combined with a text string, However I get an error when I click on apply;

"Error 403: Invalid operator for data type. Operator equals add, type equals ntext"

I can only assume that this means you can't add (or even manipulate) ntext columns in a SP.

Short of changing the sp to bring back 3 columns and to do the manipulation in my c# code I can't think of a way round this.

Anyone with any ideas?

thanks in advance

rich

SP code ( I have highlighted the problem area):

CREATE PROCEDURE [dbo].[usp_RCTReportQuery]

@.PersonType varchar(255),
@.Status varchar(255),
@.Oustanding varchar(255)

AS

DECLARE @.PersonTypeID int,
@.StatusID int,
@.OustandingID int

SELECT @.PersonTypeID = PersonTypeID FROM TBL_LU_People_Type WHERE PersonType = @.PersonType
SELECT @.StatusID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Status
SELECT @.OustandingID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Oustanding

SELECT surname as [Potential Claimant], s.thevalue + '<BR /><BR />' + o.thevalue as [Status]
FROM tbl_people p
INNER JOIN tbl_Lu_people lup on lup.personid = p.personid and persontypeid = @.PersonTypeID
LEFT JOIN tbl_memo s on s.caseID = p.caseID and s.memotypeid = @.StatusID
LEFT JOIN tbl_memo o on o.caseID = p.caseID and o.memotypeid = @.OustandingID
GO

Richie:

If you are running SQL Server 2005 you might be able to leverage a function to work around this issue. Perhaps a function like this:

alter function dbo.appendText
( @.prm_textIn nvarchar(max),
@.prm_appendString varchar (8000)
)
returns nvarchar (max)
as

begin

return ( @.prm_textIn + @.prm_appendString )

end

This is coded as a scalar function; you might be better off setting it up as an inline function so that you can potentially leverage it in the future with the CROSS JOIN operator for other purposes. Below is an example:

declare @.what nvarchar (max) set @.what = ''
declare @.firstPart varchar (40) set @.firstPart = ' ** This is'
declare @.secondPart varchar (40) set @.secondPart = ' a test. **'

select @.what = @.what + ' #' + convert (nvarchar(5), iter)
from small_iterator (nolock)

select len (@.what) as [len],
substring (dbo.appendText (@.what, @.firstPart + @.secondPart), len(@.what) - 6, 30)
as [righthand piece]

-- Sample Output: --

-- len righthand piece
-- -- --
-- 218263 #32767 ** This is a test.

Dave

|||if you are on 2005, you could just use a nvarchar(MAX) column instead of ntext.

www.elsasoft.org|||

Richie:

You might be able to get your query to work in SQL 2005 doing something like this:

SELECT surname as [Potential Claimant], cast (s.thevalue as nvarchar (max)) + '<BR /><BR />' + o.thevalue as [Status]

|||

Please specify the version of SQL Server in your posts so it is easy to suggest the correct solution.

1. If you are using SQL Server 2005 then use varchar(max), nvarchar(max), and varbinary(max). The text, ntext, and image data types have been deprecated. The newer max data types will allow you to manipulate the values on the server-side just like regular varchar, nvarchar or varbinary values. Also, for backward compatibility reasons any expression that involves string concatenation will return only a string of maximum length 8000 bytes. In order to concatenate larger length strings you have to cast at least one of the values to varchar(max) or nvarchar(max). So in your SELECT list, you could do something like CAST(s.thevalue as varchar(max)) + '<BR/><BR>' + o.thevalue.

2. If you are not using SQL Server 2005 then there is no way to concatenate strings larger than 8000 bytes. You have to either dump the rows into a temporary table and use UPDATETEXT to manipulate each row individually or simply return the values as multiple columns and concatenate on the client-side

Lastly, you should actually leave the presentation to the client side and do these type of operations on the server. What if you want to later introduce a paragraph break or ident the line? What if the formatting is more complex? You should just return the strings as is to the client and then format it there.

|||

Cheers for the tip. Unfortunately I am still using Sql Server 2000 :(

I tried creating a function but you can't have variables of type ntext in functions.

Is there anyway I can do this in SQL Server 2000?

|||I am using sql server 2000 and try using table temp.

Whats wrong?

ROBUST PLAN. Somebody help?

---

No

se puede crear una fila de tabla de trabajo más larga que el máximo

admitido. Vuelva a enviar la consulta con la sugerencia ROBUST PLAN.

TRANSLATION

Quote:

Originally Posted by

A

row of table of work cannot be created longer than the admitted

maximum. Return to send the consultation with suggestion ROBUST PLAN.

Cant create line in table work longer,

CREATE TABLE temp
(
Proc_id INT,
Proc_Name SYSNAME,
Definition NTEXT
)

-- get the names of the procedures that meet our criteria
INSERT temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

--Inicializar the NTEXT column
UPDATE temp SET Definition=''

CODE --

DECLARE

@.txtPval binary(16),

@.txtPidx INT,

@.curName SYSNAME,

@.curtext NVARCHAR(4000)
--

--
DECLARE C CURSOR

LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

SELECT OBJECT_NAME(id), text

FROM syscomments s

INNER JOIN #MYTEMP t

ON s.id = t.Proc_id

ORDER BY id, colid

OPEN c -> HERE TELL ME ERROR about robust plan.

FETCH NEXT FROM c INTO @.curname, @.curtext

--Start Loop

WHILE (@.@.FETCH_STATUS = 0 )
BEGIN
--get pointer for the current procedure name colid
SELECT @.txtPval = TEXTPTR(Definition)
FROM temp
WHERE Proc_Name = @.curname

--find out where to append the @.temp table′s value

SELECT @.txtPidx = DATALENGTH(Definition)/2
FROM temp
WHERE Proc_Name = @.curName

--Apply the append of the current 8kb chunk
UPDATETEXT temp.definition @.txtPval @.txtPidx 0 @.curtext
FETCH NEXT FROM c INTO @.curName, @.curtext
END

-- check what was produced
SELECT Proc_name, Definition, DATALENGTH(Definition)/2
FROM temp

-- check our filter
SELECT Proc_Name, Definition
FROM temp
WHERE definition LIKE '%foobar%'

--Clean up

DROP TABLE temp
CLOSE c
DEALLOCATE c
Thanks for try help.

Manipulating Namespace Attributes

Hello All:
I have a fair amount of SQL 2000 experience and recentlty have begun
working with SQL 2005. SPecificall, I am working with the XML data
type in the following scenario:
-A third party vendor provides us with a data source containing a
column of datatype XML. I would like to search through the nodes of
the XML for specific elements and attributes. The problem is that some
of the XML fields contain namespaces (meaning that my xQuery must
declare the namespace as well), and some of the values in this same XML
column do not have namespaces declared. I have no control over the
data sent to me, but what I would like to do is remove the namespace
attribute where it exists. I have tried the modify method, but it does
not work on the xmlns attribute.
I can transform the field to nText and then parse this out, but I would
perfer to avoid any extra steps since it is a large quantity of data
and some of these xml fields are quite large.
I would be grateful for any advice on how to handle this.
Thanks in advance.
Rich FHave you tried to search using a namespace wildcard?
Like /*.foo/*:attr
namespace declarations are not exposed as attribute and cannot easily be
changed. If you need to change arbitrary namespaces to a specific namespace
you should probably write an XSLT transform.
Best regards
Michael
<eastegg_1970@.yahoo.com> wrote in message
news:1164911773.276725.95190@.j72g2000cwa.googlegroups.com...
> Hello All:
> I have a fair amount of SQL 2000 experience and recentlty have begun
> working with SQL 2005. SPecificall, I am working with the XML data
> type in the following scenario:
> -A third party vendor provides us with a data source containing a
> column of datatype XML. I would like to search through the nodes of
> the XML for specific elements and attributes. The problem is that some
> of the XML fields contain namespaces (meaning that my xQuery must
> declare the namespace as well), and some of the values in this same XML
> column do not have namespaces declared. I have no control over the
> data sent to me, but what I would like to do is remove the namespace
> attribute where it exists. I have tried the modify method, but it does
> not work on the xmlns attribute.
> I can transform the field to nText and then parse this out, but I would
> perfer to avoid any extra steps since it is a large quantity of data
> and some of these xml fields are quite large.
> I would be grateful for any advice on how to handle this.
> Thanks in advance.
> Rich F
>

Manipulating Namespace Attributes

Hello All:
I have a fair amount of SQL 2000 experience and recentlty have begun
working with SQL 2005. SPecificall, I am working with the XML data
type in the following scenario:
-A third party vendor provides us with a data source containing a
column of datatype XML. I would like to search through the nodes of
the XML for specific elements and attributes. The problem is that some
of the XML fields contain namespaces (meaning that my xQuery must
declare the namespace as well), and some of the values in this same XML
column do not have namespaces declared. I have no control over the
data sent to me, but what I would like to do is remove the namespace
attribute where it exists. I have tried the modify method, but it does
not work on the xmlns attribute.
I can transform the field to nText and then parse this out, but I would
perfer to avoid any extra steps since it is a large quantity of data
and some of these xml fields are quite large.
I would be grateful for any advice on how to handle this.
Thanks in advance.
Rich F
Have you tried to search using a namespace wildcard?
Like /*.foo/*:attr
namespace declarations are not exposed as attribute and cannot easily be
changed. If you need to change arbitrary namespaces to a specific namespace
you should probably write an XSLT transform.
Best regards
Michael
<eastegg_1970@.yahoo.com> wrote in message
news:1164911773.276725.95190@.j72g2000cwa.googlegro ups.com...
> Hello All:
> I have a fair amount of SQL 2000 experience and recentlty have begun
> working with SQL 2005. SPecificall, I am working with the XML data
> type in the following scenario:
> -A third party vendor provides us with a data source containing a
> column of datatype XML. I would like to search through the nodes of
> the XML for specific elements and attributes. The problem is that some
> of the XML fields contain namespaces (meaning that my xQuery must
> declare the namespace as well), and some of the values in this same XML
> column do not have namespaces declared. I have no control over the
> data sent to me, but what I would like to do is remove the namespace
> attribute where it exists. I have tried the modify method, but it does
> not work on the xmlns attribute.
> I can transform the field to nText and then parse this out, but I would
> perfer to avoid any extra steps since it is a large quantity of data
> and some of these xml fields are quite large.
> I would be grateful for any advice on how to handle this.
> Thanks in advance.
> Rich F
>

Manipulating dates in SQL

I need to generate a date range, based on the current date (or an input date). I can get the correct dates using VB, but I haven't worked out the TSQL Syntax for them yet. Can anyone tell me the TSQL syntax for manipulating dates in the following way...?

Start Date... This is the first day of the month, one year ago... in VB I worked it out as...

dateadd("yyyy",-1,(cdate(cstr(Year(now))+"-"+cstr(Month(now))+"-01")))

End Date... This is the last day of the previous month... in VB I worked this one out as...

dateadd("d",-1,(cdate(cstr(Year(now))+"-"+cstr(Month(now))+"-01")))

eg. for today 18/01/2007 I would get a Start date of 01/01/2006 and an End date of 31/12/2006

Any help would be appreciated.

I managed to work out a solution... I get my Start date by using the following...

(select dateadd(yyyy,-1,(select stuff(stuff((convert(varchar,
convert(varchar,datepart(year,getdate()))+
convert(varchar,datepart(Month,getdate()))+
convert(varchar,(convert(int,datepart(day,getdate())))- (convert(int,datepart(day,getdate()))-1))))
,5 ,0, '-'),7,0, '-'))))

|||

Hey Jon,

This should do what you want it to.

select dateadd(mm, datediff(mm, 0, dateadd(yy, -1, getdate())), 0) as StartDate,
dateadd(dd, -1, dateadd(mm, datediff(mm, 0, getdate()), 0)) as EndDate

Hope this helps.

Jarret

|||

That's great... thanks Jarret.

A much better solution than mine... especially since I noticed mine only works if the month is a single digit.

Manipulating dates in an expression

Does anyone know a way to subtract a date from a date
parameter (e.g. = Parameters!Date.Value - 1, which of
course does not work)
thanksYou might want to read the MSDN documentation for the DateTime class:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
E.g. subtracting one day would be =Parameters!Date.Value.AddDays(-1)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nick Caramello" <nick@.podconsulting.com> wrote in message
news:2ebc01c49fde$a16edd50$a501280a@.phx.gbl...
> Does anyone know a way to subtract a date from a date
> parameter (e.g. = Parameters!Date.Value - 1, which of
> course does not work)
> thanks|||Here is how I subtracted one date from another to get a number of days
between them:
=Fields!DESIRED_SHIP_DATE.Value.ToOADate() -
Fields!ORDER_DATE.Value.ToOADate()
This information below is not a direct solution for your problem, but the
information may help you with other things data/time related.
Here is how I get the current hour and manipate it:
The DateAndTime.Hour(Now) return a 24 hour clock output for the hour, so
this code turns hour 13 to 1 (like 1pm).
Dim cHour As Integer = DateAndTime.Hour(Now)
If cHour > 12 Then
StartHourBox.Text = cHour - 12
Else
StartHourBox.Text = cHour
End If
Look at all the different options available to you with "DateAndTime",
including Day, Month, and year.
Later,
Ed Hammond
--
"Nick Caramello" <nick@.podconsulting.com> wrote in message
news:2ebc01c49fde$a16edd50$a501280a@.phx.gbl...
> Does anyone know a way to subtract a date from a date
> parameter (e.g. = Parameters!Date.Value - 1, which of
> course does not work)
> thanks

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated
"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA
|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:

>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005
.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:

>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/200
5.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:
> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
> >Hi
> >
> >I have a field called paid to date and need to calculate the next date with
> >the same day of the month from getdate(). For example, paid to date of
> >13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
> > However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> >
> >Any help in achieving this would be greatly appreciated
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

manipulating data before grouping & displaying

I got data with recursive data. Additionally every entry is asssigned to a specific role. But not every related child & parent doesn't have to have the same role. Usually, every child should be displayed underneath its parent, but when I group on the role, the entries get split up.Like:

Role x:
Parent 1
Child 1.1
Child 1.2
Parent 2
Role y:
Parent 3
Child 2.1
Child 2.2
Parent 4

I think you get the picture. But It should look like:

Role x:
Parent 1
Child 1.1
Child 1.2
Parent 2
Child 2.1
Child 2.2
Role y:
Parent 3
Parent 4

The easiest way I thought of, was, to manipulate the data of the child entries in the role column, so it matches the same role as their parent. Anyone got any idea of how to accomplish such a thing?

write a recursive sql cursor. Create a temp table with the a new column. For each new level add a space to the new column. So the child would get a " " and the grandchildren would get a bigger space " " and so fourth. Make sure the cursor puts the elements in the correct order as in your example above. The cursor will transfer your dataset and will load the values in the temp table. Then when you print your tree you can do something like.

newcolumn.value & node.value

Manipulating Data

Hello all...

I have a table that inlcudes a field that represents a percentage value (like 1.23), but the data is presented as 000123 (hope that makes sense). I get the data from a location outside of my office and I have no say in how the data is formatted.

I need to run a query that multiplies one field by this above mentioned field.

Is there a way to convert the value to the way I need it programmatically (in a query) without putting it in a temporary table?

Example:

Table 1 - fields - ID, Amount
Table 2 - fields - Type, Percentage

Table 1 (one row of data) - 1 234
Table 2 (one row of data) - A 000123

I want to multiply the Amount from Table 1 (234) by the Percentage from Table 2 (000123) where the Type from Table 2 is equal to A.

This should be worked out as 234 * 1.23

Thanks for any help.

Quote:

Originally Posted by narpet

Hello all...

I have a table that inlcudes a field that represents a percentage value (like 1.23), but the data is presented as 000123 (hope that makes sense). I get the data from a location outside of my office and I have no say in how the data is formatted.

I need to run a query that multiplies one field by this above mentioned field.

Is there a way to convert the value to the way I need it programmatically (in a query) without putting it in a temporary table?

Example:

Table 1 - fields - ID, Amount
Table 2 - fields - Type, Percentage

Table 1 (one row of data) - 1 234
Table 2 (one row of data) - A 000123

I want to multiply the Amount from Table 1 (234) by the Percentage from Table 2 (000123) where the Type from Table 2 is equal to A.

This should be worked out as 234 * 1.23

Thanks for any help.

first, how are the two tables related? although these two tables may still be joined even if they are not related, it's a rare situation that you will join two unrelated tables.

try this:
select 1, amount, type, percentage, amount * cast(percentage as float)/100.00
from table1
full outer join table2 on cast(id as varchar(2)) = cast(type as varchar(2)).
and type = 'A'

since "full outer join" joins the two table wheather there matched records or not, it will always return the values on the right

i did not test this query, is this right?|||

Quote:

Originally Posted by ck9663

first, how are the two tables related? although these two tables may still be joined even if they are not related, it's a rare situation that you will join two unrelated tables.

try this:
select 1, amount, type, percentage, amount * cast(percentage as float)/100.00
from table1
full outer join table2 on cast(id as varchar(2)) = cast(type as varchar(2)).
and type = 'A'

since "full outer join" joins the two table wheather there matched records or not, it will always return the values on the right

i did not test this query, is this right?

I'm not at work right now. I will test this when I get in on Monday morning. Thanks for the info... I will post and let you know how this works. As an answer to your question... the two tables will be joined by a common account number.|||

Quote:

Originally Posted by narpet

I'm not at work right now. I will test this when I get in on Monday morning. Thanks for the info... I will post and let you know how this works. As an answer to your question... the two tables will be joined by a common account number.

then you use account number as the join key. whether it'll be an outer, left, right or inner join will be up to your requirement|||That worked perfectly. Thanks very much for the help!

manipulating binary data

To all,
I have a binary data type in my database. It is an array of doubles. I
would like to create a DTS package that can change this binary data type int
o
an array so I can retrieve one of the double values at a specific index.
Is there any way to do this?
How can I cast the binary data type in the database as an array of doubles?
Thanks in advance,
GloriaThere is no such thing in SQL as an array. So, to understand this clearly,
the data yoou have in the database, even though it was an array of doubles i
n
your client code before you sent it there, in the database it's just a byte
stream, or a long string of bytes...
To convert it to individual values, will most easily be done using client
side code in some programming language.
If the "array" was a delimited list of doubles represesnted as text, then it
miht be possible to do this parsing and separating using some SQL Code, but
even tis is medium to hard. If the data in your binary data column is the
actual binary byte stream generated by some client side code language to
represent an array of IEEE Double precision floats, then there's probably no
hope of parsing those individual values out in SQL Code...
"Gloria" wrote:

> To all,
> I have a binary data type in my database. It is an array of doubles. I
> would like to create a DTS package that can change this binary data type i
nto
> an array so I can retrieve one of the double values at a specific index.
> Is there any way to do this?
> How can I cast the binary data type in the database as an array of doubles
?
> Thanks in advance,
> Gloria|||There are no arrays in SQL, nor do we use it for bit level
manipulations. Indexes in SQL are not exposed toi the programmer; in
fact they are a implementattion method that not all products use
(Teradata is based on hashing; Nucleus uses bit vectors, etc.).
You have missed the point of SQL and have returned to a C style file
system in your mental model. And there is nothing wrong with a file or
programming at the machine level for certain problems. Databases are
not one of those problems.

Manipulating and deploying report models

All, we are reviewing the ad hoc report builder in SSRS 2005. This could be a big boon to our clients. But we have to be able to do some things first. First of all when we define our datasource view we need to be able to "filter" the available data in the view prior to being "delivered" to the report builder app the client gets to manipulate. We don't currently see a way to do this. But I'm working on it.

I'm thinking we may need to go the manual manipulation route using the Reporting Service2005 web service. So in a nutshell, I need to be able to pre filter the data a client gets prior to using the ad hoc report builder, we have too many clients to create views for each of them. So I've started giving it a go.

Thanks in advance

Marvin Hoffman

here's what I have so far, and it seems to be working, but I think maybe the URL is wrong, or I'm not calling the model name correctly. I'm getting this error "Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response."

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Net;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using ReportServiceAPI;

public partial class ReportBuilderExample : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

ReportingService2005 rs = new ReportingService2005();

rs.Url = "http://servername/reportserver/ReportingService2005.asmx";

NetworkCredential nc = new NetworkCredential("username", "password");

rs.Credentials = nc;

byte[] b = rs.GetModelDefinition("modelname");

}

}

I think that model name includes path and should start with slash, i.e.

byte[] b = rs.GetModelDefinition("/modelname");