restrict access to database: return

Showing posts with label return. Show all posts

Friday, March 30, 2012

How To Script Jobs In SQL Server ?

How to create SQL script out of job in SQL Server ?
I know there is a way thru EM, but is there any stored procedure or any function that will return the SQL script of any job.
I want to script all jobs on my server just as a part backing up mechanism.
Thanks
Decasto.After starting Profiler, going into EM and scripting a job I noticed two procs being called...

msdb..sp_help_jobstep @.job_id = XXX
and
msdb..sp_help_jobschedule @.job_id = XXX

soooo I suppose you could step through msdb..sysjobs and process each job you find with one or both of the above procs.

Wednesday, March 21, 2012

How to run an insert without returning anything

I need to run a select statement in a sproc and at the end insert into a
history table without having the insert return anything to the sproc is is
embedded in. How do I do this? Thank you.If that is all the proc does, you can say:
INSERT INTO HistoryTable EXEC myProcedure;
"JT" <xtf@.microsoft.com> wrote in message
news:eUw3771VGHA.4952@.TK2MSFTNGP09.phx.gbl...
>I need to run a select statement in a sproc and at the end insert into a
>history table without having the insert return anything to the sproc is is
>embedded in. How do I do this? Thank you.
>|||Problem is that I do a select first and then insert into history and I am
getting the reults of my insert not my select. Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u9cLMC2VGHA.2444@.TK2MSFTNGP14.phx.gbl...
> If that is all the proc does, you can say:
> INSERT INTO HistoryTable EXEC myProcedure;
>
>
> "JT" <xtf@.microsoft.com> wrote in message
> news:eUw3771VGHA.4952@.TK2MSFTNGP09.phx.gbl...
>|||> Problem is that I do a select first and then insert into history and I am
> getting the reults of my insert not my select. Thanks.
I don't know what all of this means. Could you provide some real code, a
simple repro, and explain in detail what you want to really happen? All
these word problems are not very easy to follow.|||JT wrote:
> I need to run a select statement in a sproc and at the end insert into a
> history table without having the insert return anything to the sproc is is
> embedded in. How do I do this? Thank you.
CREATE PROC usp_insert
AS
SET NOCOUNT ON ;
INSERT INTO tbl (col1, col2, ...)
SELECT col1, col2, ...
FROM ... ;
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||JT (xtf@.microsoft.com) writes:
> Problem is that I do a select first and then insert into history and I am
> getting the reults of my insert not my select. Thanks.
If this is a quiz, my guess is that your table has a trigger with a
SELECT statement in it.
If it not a quiz, please be more detailed...
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

Monday, March 19, 2012

How to return xml datatype column values ?

Hi ,

I want to return the xml datatype values in the output of my query.

I am querrying on one table which has xml datatype by selelcting the column in select clause and would like to get the values in output.

How do i get output with out/with including the column name in group by clause?

Can some one help me here.

--Smita.

You have a XML column and you want to break the XML as columns ? is this what you want ?|||

Rick,

I have a coulmn with xml datatype and want to get entire column value in one output.

-Smitha.

|||

Something like this ?

declare @.x xml

set @.x='<Root>

<Node>

</Node>

<Node>

</Node>

</Root>'

select cast(r.query('for $data in ./Node return data($data) ') as varchar(max)) as [Column]

from @.x.nodes('/Root/Node') as X(r)

How to return varchar(MAX) from a CLR function?

Hi,

I am trying to return [string/SqlString] from a CLR function, but it was truncated at 8000 characters.

How can I solve this problem and return varchar(MAX)?

Thanks

? The Visual Studio deployment tool maps SqlString to VARCHAR(8000). Use SqlChars instead -- it gets mapped to VARCHAR(MAX). -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Bill YU@.discussions.microsoft.com> wrote in message news:2a21db0a-f54b-42e2-88b9-fb4cab26d356@.discussions.microsoft.com... Hi, I am trying to return [string/SqlString] from a CLR function, but it was truncated at 8000 characters. How can I solve this problem and return varchar(MAX)? Thanks|||Thanks Adam, it works.|||Came here to ask a similar question and saw your response to this question and it fixed my problem also. When deploying my CLR SP, studio was mapping the SP'S string parameters to varchar(4000) and my xml data was being truncated. Changed the data type of the sp parameter to SqlChars and everything worked fine after that, thanks!
|||? Visual Studio deployment unfortunately has a lot of quirks. In case you're interested, I discuss another one in the following blog post: http://www.amazon.com/gp/plog/post.html/ref=cm_blog_pl/104-6385614-0075127?%5Fencoding=UTF8&pt=personalBlog&aid=PlogMyCustomersAgent&ot=customer&pd=1147803274.225&pid=PMCAALIINL2LPJELat1147801908&iid=AALIINL2LPJEL -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Bo416@.discussions.microsoft.com> wrote in message news:2343e9db-ce1c-4f2a-9b47-2d471a0ddb44@.discussions.microsoft.com...Came here to ask a similar question and saw your response to this question and it fixed my problem also. When deploying my CLR SP, studio was mapping the SP'S string parameters to varchar(4000) and my xml data was being truncated. Changed the data type of the sp parameter to SqlChars and everything worked fine after that, thanks!

How to return value from Stored Procedure(SQL Server) into c

Hi,
Yes. Exactly we are using the ADODB parameters. Here with i have
attached the coded snippet.
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("strSalesOrderNo"), adVarChar,
adParamInput, 64, vstrSalesOrderNo));
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("strToolName"), adVarChar,
adParamInput,
200, vstrToolName));
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("strToolAddress"), adVarChar,
adParamInput, 64, vstrToolAddress));
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("Output"), adInteger, adParamOutput, 4
,
&viResult));
pRs = pCommand->Execute( &vNull, &vNull, adCmdStoredProc);
pRs->Close();
_variant_t v;
v.vt = VT_BSTR;
v.bstrVal =_bstr_t("Output");
_variant_t vResult;
vResult = pCommand->Parameters->GetItem(v)->Value;
Here when we debug vResult, it shows VT_EMPTY.
Does this code has issues with this ?
Thanks in Advance,
Ravindran.Hi Ravi
i think u can try vResult = pCommand(0);
i am not sure if it works in VC, but this is the was i do in VB
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ravip" wrote:

> Hi,
> Yes. Exactly we are using the ADODB parameters. Here with i have
> attached the coded snippet.
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("strSalesOrderNo"), adVarChar,
> adParamInput, 64, vstrSalesOrderNo));
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("strToolName"), adVarChar,
> adParamInput,
> 200, vstrToolName));
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("strToolAddress"), adVarChar,
> adParamInput, 64, vstrToolAddress));
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("Output"), adInteger, adParamOutput, 4
> ,
> &viResult));
>
> pRs = pCommand->Execute( &vNull, &vNull, adCmdStoredProc);
> pRs->Close();
> _variant_t v;
> v.vt = VT_BSTR;
> v.bstrVal =_bstr_t("Output");
> _variant_t vResult;
> vResult = pCommand->Parameters->GetItem(v)->Value;
> Here when we debug vResult, it shows VT_EMPTY.
> Does this code has issues with this ?
> Thanks in Advance,
> Ravindran.
>
>

How to return value from dynamic query or set the value

Hii I am Varun i have a problem with the dynamic stored procedure

This is my stored procedure

ALTER PROCEDUREdbo.sp_TimeTableAdjustment1

(

@.TeacherID_OnLeavenumeric(9),

@.DateFromdatetime,

@.DateTodatetime, @.UserIDnumeric(9)

)

declare@.flagas numeric(9)

declare@.yearas varchar(4)

set@.year=(selectbatchfrombatchmasterwhereiscurrent=1andisdeleted=0)

if( @.year=null or len(@.year)=0)

set@.year =year(getdate())

exec('if not exists(select * from timetableadjustments_'+@.year+' where datefrom='''+@.datefrom+''' and dateto='''+@.dateto+''' and teacherid_onleave='+@.teacherid_onleave+')

begin

insert into TimeTableAdjustments_'+@.year+' (TeacherID_OnLeave,DateFrom,DateTo,UserID) values ('+@.TeacherID_OnLeave+','''+@.DateFrom+''','''+@.DateTo+''','+@.UserID+');

end')

else

set@.flag=(selecttimetableadjustmentidfromtimetableadjustments_2007wheredatefrom=@.datefromanddateto=@.datetoandteacherid_onleave=@.teacherid_onleave)

return@.flag

--exec('select timetableadjustmentid from timetableadjustments_'+@.year+' where datefrom='''+@.DateFrom+''' and dateto='''+@.DateTo+''' and teacherid_onleave='+@.TeacherID_OnLeave+'

--')

--return @.flag

--exec('@.flag=select timetableadjustmentid from timetableadjustments_'+@.year+' where datefrom='''+@.DateFrom+''' and dateto='''+@.DateTo+''' and teacherid_onleave='+@.TeacherID_OnLeave+'')

And the problem?...

how to return UTF 8 string from nvarchar field

hi
i have connected my ms sql 2000 with C using ODBC
can u help me to return the utf 8 string from nvarchar field ??
how should i do it
please help!!!!!!!http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q232/5/80.ASP&NoWebContent=1 information about storing UTF8 data.|||hi

thanx for u r help but it did not solve my problem
i want to retrive the utf 8 stirngs stored in nvarchar
but this does not help

thanx hope u can help me|||In SQL you can use READTEXt/WRITETEXT/UPDATETEXT to retrieve such data and refre to books online for more information.|||hi
thanx a lot
can u tell me which book to look for and

what i am saying is that the UTF 8 data that i have stored in the nvarchar field and retrieved it from C and store in a txt file
it should give ?? it only gives me ? which means it is not reading the whole data
so can u help in that plzzz
thanx a lot
looking forward to u r reply

thanx|||IN which language you're trying to retrieve C or SQL?
Ensure the windows locale does match to the collation set on SQL server.

Books online is the utility installed alongwith SQL server.
If not get the latest from here (http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp)|||i am retrieving the data from MS SQL(back end) through ODBC using C language(front end)
in win2k envoironment and storing the data in a text file
the field is a nvarchar field which has UTF 8 chars in it
and i want to retrieve the data from it and but it is not working accordingly
what should i do ??

how to return truncated field

Hi,
I'd like to select a field of my table and also a truncated version of
that field:
SELECT Field1, Field1_truncated FROM myTable
This field is of type nvarchar. Field1 should contain the entire string
valule whereas Field1_truncated should only contain the first 10
characters of the original field, followed by 3 dots (...)
So I would get this kind of result:
Field1 Field1_truncated
some long string some long ...
Can you help?SELECT Field1, left(Field1,10) + '...' as Field1_truncated FROM myTable
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi, Samuel
Try something like this:
SELECT Col1,
CASE
WHEN Len(Col1)>13
THEN LEFT(Col1,10)+'...'
ELSE Col1
END as Col1_truncated
FROM YourTable
Razvan|||Brilliant.
Thanks to you guys !

How to return time & number format that has set in the regional setting using stored proce

How to return time & number format that has set in the regional setting using stored procedure.

Following is my sp for getting current date format from Sql Server.

if exists

(select*fromsysobjectswhereid =object_id(N'[HSP_GetDateFormat]')andOBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure[HSP_GetDateFormat]

create procedure

HSP_GetDateFormat

(

@.strDateFormat nvarchar(64) out,

@.iErr

intout

)

begin

set nocount on

set@.strDateFormat = (selectdateformat frommaster..syslanguageswherelangid = (selectvaluefrommaster..sysconfigureswherecomment = 'default language'))

set@.iErr = @.@.Error

set nocount off

end

Now, I want to know what would I write if I want to get currenttime &numberformat from Sql Server.

Hi,

From your description, it seems that you want to get the current date time in stored procedure, right?

TSQL has provided several built-in functions which is used for datetime and datetiem calculating. You can get the current date and time by using GetDate() function, also, you can use such functions like DATEADD,DATEDIFF to calculate the date and time. See the following codes:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() ))

Besides, if you want to output the value, just create a datetime typed variable and set it as OUTPUT, and then create a parameter in .NET side, which shares the same type, set the direction as OUTPUT, then you can receive the datetime from your stored procedure. As for the number type, you can use INT,FLOAT,DOUBLE and etc in your stored procedure.

Thanks.

How to return the result of an EXEC from a function

Hi,

I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute.

This is my function:

ALTER FUNCTION [dbo].[ReturnPickItemValue]
(
-- Add the parameters for the function here
@.TypeID int,
@.CaseID int
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @.RTN varchar(max)

IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @.TypeID) = 1
BEGIN
SET @.RTN = 'SELECT PickItem I +
CASE D.IsStartDateEstimated
WHEN 0 THEN CAST(StartDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)
END +
CASE D.IsEndDateEstimated
WHEN 0 THEN CAST(EndDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)
END

FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID
LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID
WHERE L.PickTypeID = ' + CAST(@.TypeID as varchar) + '
AND P.CaseID = ' + CAST(@.CaseID as varchar)
END
ELSE
BEGIN
SET @.RTN=
'SELECT I.PickItem
FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID
WHERE L.PickTypeID = ' + CAST(@.TypeID as varchar) + '
AND CaseID = ' + CAST(@.CaseID as varchar)
END

RETURN @.RTN

END

Each time I try " RETURN EXEC(@.RTN) " or something similar I get an error.

I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error.

Anyone with any ideas?

You need to give a look to the CREATE FUNCTION article in books online. There are a number of constraints on the DML that can be used inside of a function. In this case you are being stopped by the constraint that does not allow use of the EXEC ( @.anSQLString ) inside of a function. You are also not allowed to execute stored procedures from inside the body of a function. I would suggest that if you want to run an EXEC ( @.someKindOfString ) that you really ought to use a stored procedure instead of a function. Since the problem is dynamic SQL you might want to first see if you can eliminate the dynamic SQL.

Kent

|||

Following on from Kent, in looking at your query do you actually need to build a dynamic string at all?

Code Snippet

CREATE FUNCTION [dbo].[ReturnPickItemValue]

(

-- Add the parameters for the function here

@.TypeID int,

@.CaseID int

)

RETURNS varchar(max)

BEGIN

-- Declare the return variable here

DECLARE @.RTN varchar(max)

IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @.TypeID) = 1

BEGIN

SET @.Rtn = (SELECT I.PickItem +

CASE D.IsStartDateEstimated

WHEN 0 THEN CAST(StartDate as varchar)

ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)

END +

CASE D.IsEndDateEstimated

WHEN 0 THEN CAST(EndDate as varchar)

ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)

END

FROM TBL_LU_PICK L

INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID

INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID

LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID

WHERE L.PickTypeID = @.TypeID

AND P.CaseID = @.CaseID)

END

ELSE

BEGIN

SET @.Rtn = ( SELECT I.PickItem

FROM TBL_LU_PICK L

INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID

INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID

WHERE L.PickTypeID = @.TypeID

AND CaseID = @.CaseID)

END

RETURN @.Rtn

END

HTH!

|||cheers richbrownesq

That works a treat!!

How to return the result (XML) with FOR XML EXPLICIT

Dear SQLXML specialists and other animals,
I got the following SP, that runs a SQL statement with FOR XML
EXPLICIT:
**** SQL STATEMENT *****
CREATE PROCEDURE SP_F06_GetActions
(
@.MSISDN as varchar(20),
@.SessionId as varchar(64)
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
,NULL as [Action!3!ActionVideoURL!Element]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=@.MSISDN
--ORDER BY 3, 1
FOR XML EXPLICIT
RETURN
END
GO
**** END OF SQL ***********
Well, it returns the following XML:
***** XML *********************
<RESPONSE>
<Flow_Name>F06</Flow_Name>
<MSISDN>9053*********</MSISDN>
<Timestamp>2007-11-15T14:37:24.467</Timestamp>
<SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
<Status>0</Status>
<Actions>
<NumOfActions>3</NumOfActions>
<Action Id="10001">
<ActionId>10001</ActionId>
<ActionName>Acme</ActionName>
<ActionType>1</ActionType>
<From>00:00:00</From>
<To>12:00:00</To>
<ActionStart>2007-11-15T09:52:56.513</ActionStart>
<ActionStop>2007-11-25T09:52:56.513</ActionStop>
<ActionDurationThreshold>8</ActionDurationThreshold>
<ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
ActionVideoURL>
</Action>
<Action Id="10002">
<ActionId>10002</ActionId>
<ActionName>Pepsi</ActionName>
<ActionType>1</ActionType>
<From>12:00</From>
<To>16:00:00</To>
<ActionStart>2007-11-15T09:53:17.640</ActionStart>
<ActionStop>2007-11-25T09:53:17.640</ActionStop>
<ActionDurationThreshold>7</ActionDurationThreshold>
<ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
ActionVideoURL>
</Action>
<Action Id="10003">
<ActionId>10003</ActionId>
<ActionName>Momo Deterjan</ActionName>
<ActionType>1</ActionType>
<From>18:00:00</From>
<To>22:00:00</To>
<ActionStart>2007-11-15T09:56:32.950</ActionStart>
<ActionStop>2007-11-25T09:56:32.950</ActionStop>
<ActionDurationThreshold>6</ActionDurationThreshold>
<ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
ActionVideoURL>
</Action>
</Actions>
</RESPONSE>
**** END OF XML ************
Therefore, what I want is to assign this XML to a variable. To do
that, I tried the following (but failed):
Exec @.RC=SP_F06_GetActions
@.MSISDN,
@.SessionId
So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
Thanks in advance for your help and interest.
Regards,
Ali<ali.koyuncu@.gmail.com> wrote in message
news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
> Dear SQLXML specialists and other animals,
> I got the following SP, that runs a SQL statement with FOR XML
> EXPLICIT:
> **** SQL STATEMENT *****
> CREATE PROCEDURE SP_F06_GetActions
> (
> @.MSISDN as varchar(20),
> @.SessionId as varchar(64)
> )
> AS
> BEGIN
> SELECT
> 1 as tag
> ,NULL as parent
> ,'F06' as [RESPONSE!1!Flow_Name!Element]
> ,[MSISDN] as [RESPONSE!1!MSISDN!Element]
> ,getdate() as [RESPONSE!1!Timestamp!Element]
> ,@.SessionId as [RESPONSE!1!SessionId!Element]
> ,0 as [RESPONSE!1!Status!Element]
> ,NULL as [Actions!2!NumOfActions!Element]
> ,NULL as [Action!3!Id]
> ,NULL as [Action!3!ActionId!Element]
> ,NULL as [Action!3!ActionName!Element]
> ,NULL as [Action!3!ActionType!Element]
> ,NULL as [Action!3!From!Element]
> ,NULL as [Action!3!To!Element]
> ,NULL as [Action!3!ActionStart!Element]
> ,NULL as [Action!3!ActionStop!Element]
> ,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
> ,NULL as [Action!3!ActionVideoURL!Element]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 2
> ,1
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> , dbo.getNumOfActionsByMSISDN(MSISDN)
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 3
> ,2
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,[ActionId]
> ,[ActionId]
> ,[ActionName]
> ,[ActionType]
> ,[From]
> ,[To]
> ,[ActionStart]
> ,[ActionStop]
> ,[ActionDurationThreshold]
> ,[ActionVideoURL]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> WHERE [MSISDN]=@.MSISDN
> --ORDER BY 3, 1
> FOR XML EXPLICIT
> RETURN
> END
> GO
> **** END OF SQL ***********
> Well, it returns the following XML:
> ***** XML *********************
> <RESPONSE>
> <Flow_Name>F06</Flow_Name>
> <MSISDN>9053*********</MSISDN>
> <Timestamp>2007-11-15T14:37:24.467</Timestamp>
> <SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
> <Status>0</Status>
> <Actions>
> <NumOfActions>3</NumOfActions>
> <Action Id="10001">
> <ActionId>10001</ActionId>
> <ActionName>Acme</ActionName>
> <ActionType>1</ActionType>
> <From>00:00:00</From>
> <To>12:00:00</To>
> <ActionStart>2007-11-15T09:52:56.513</ActionStart>
> <ActionStop>2007-11-25T09:52:56.513</ActionStop>
> <ActionDurationThreshold>8</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10002">
> <ActionId>10002</ActionId>
> <ActionName>Pepsi</ActionName>
> <ActionType>1</ActionType>
> <From>12:00</From>
> <To>16:00:00</To>
> <ActionStart>2007-11-15T09:53:17.640</ActionStart>
> <ActionStop>2007-11-25T09:53:17.640</ActionStop>
> <ActionDurationThreshold>7</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10003">
> <ActionId>10003</ActionId>
> <ActionName>Momo Deterjan</ActionName>
> <ActionType>1</ActionType>
> <From>18:00:00</From>
> <To>22:00:00</To>
> <ActionStart>2007-11-15T09:56:32.950</ActionStart>
> <ActionStop>2007-11-25T09:56:32.950</ActionStop>
> <ActionDurationThreshold>6</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> </Actions>
> </RESPONSE>
> **** END OF XML ************
> Therefore, what I want is to assign this XML to a variable. To do
> that, I tried the following (but failed):
> Exec @.RC=SP_F06_GetActions
> @.MSISDN,
> @.SessionId
> So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
> Thanks in advance for your help and interest.
> Regards,
> Ali
Which version of SQL Server? In 2000 you can't do what you want, in 2005 you
need to use the TYPE directive.
http://technet.microsoft.com/en-us/...y/ms345110.aspx
Joe Fawcett (MVP - XML)
http://joe.fawcett.name|||I use SQL Server 2005.
How to use TYPE? Can you give me simple example?
Thanks,
Ali
On 15 Kas=FDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> <ali.koyu...@.gmail.com> wrote in message
> news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Which version of SQL Server? In 2000 you can't do what you want, in 2005 y=[/color
]
ou
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/=[/color
]
ms345110.aspx
> --
> Joe Fawcett (MVP - XML)
> http://joe.fawcett.name|||I use SQL Server 2005.
How to use TYPE? Can you give me simple example?
Thanks,
Ali
On 15 Kas=FDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> <ali.koyu...@.gmail.com> wrote in message
> news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Which version of SQL Server? In 2000 you can't do what you want, in 2005 y=[/color
]
ou
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/=[/color
]
ms345110.aspx
> --
> Joe Fawcett (MVP - XML)
> http://joe.fawcett.name|||I use SQL Server 2005.
How to use TYPE? Can you give me simple example?
Thanks,
Ali
On 15 Kas=FDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> <ali.koyu...@.gmail.com> wrote in message
> news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Which version of SQL Server? In 2000 you can't do what you want, in 2005 y=[/color
]
ou
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/=[/color
]
ms345110.aspx
> --
> Joe Fawcett (MVP - XML)
> http://joe.fawcett.name|||When I use TYPE, it gave me the following error :( *damn*
Msg 1086, Level 15, State 1, Line 72
The FOR XML clause is invalid in views, inline functions, derived
tables, and subqueries when they contain a set operator. To work
around, wrap the SELECT containing a set operator using derived table
syntax and apply FOR XML on top of it.
On 15 Kas=C4=B1m, 16:08, ali.koyu...@.gmail.com wrote:
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kas=C3=BDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote=[/color
]
:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
you
y/ms345110.aspx
>
>
>|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDN as varchar(20),
@.SessionId as varchar(64),
@.ResultXML as xml=3DNULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=3D@.MSISDN
SET @.ResultXML=3D(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas=C4=B1m, 16:08, ali.koyu...@.gmail.com wrote:
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kas=C3=BDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote=[/color
]
:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
you
y/ms345110.aspx
>
>
>|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDN as varchar(20),
@.SessionId as varchar(64),
@.ResultXML as xml=3DNULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=3D@.MSISDN
SET @.ResultXML=3D(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas=C4=B1m, 16:08, ali.koyu...@.gmail.com wrote:
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kas=C3=BDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote=[/color
]
:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
you
y/ms345110.aspx
>
>
>|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDN as varchar(20),
@.SessionId as varchar(64),
@.ResultXML as xml=3DNULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=3D@.MSISDN
SET @.ResultXML=3D(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas=C4=B1m, 16:08, ali.koyu...@.gmail.com wrote:
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kas=C3=BDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote=[/color
]
:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
you
y/ms345110.aspx
>
>
>|||If you're using SQL 2005, how about using FOR XML PATH instead? Much
simpler.
<ali.koyuncu@.gmail.com> wrote in message
news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
> Dear SQLXML specialists and other animals,
> I got the following SP, that runs a SQL statement with FOR XML
> EXPLICIT:
> **** SQL STATEMENT *****
> CREATE PROCEDURE SP_F06_GetActions
> (
> @.MSISDN as varchar(20),
> @.SessionId as varchar(64)
> )
> AS
> BEGIN
> SELECT
> 1 as tag
> ,NULL as parent
> ,'F06' as [RESPONSE!1!Flow_Name!Element]
> ,[MSISDN] as [RESPONSE!1!MSISDN!Element]
> ,getdate() as [RESPONSE!1!Timestamp!Element]
> ,@.SessionId as [RESPONSE!1!SessionId!Element]
> ,0 as [RESPONSE!1!Status!Element]
> ,NULL as [Actions!2!NumOfActions!Element]
> ,NULL as [Action!3!Id]
> ,NULL as [Action!3!ActionId!Element]
> ,NULL as [Action!3!ActionName!Element]
> ,NULL as [Action!3!ActionType!Element]
> ,NULL as [Action!3!From!Element]
> ,NULL as [Action!3!To!Element]
> ,NULL as [Action!3!ActionStart!Element]
> ,NULL as [Action!3!ActionStop!Element]
> ,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
> ,NULL as [Action!3!ActionVideoURL!Element]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 2
> ,1
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> , dbo.getNumOfActionsByMSISDN(MSISDN)
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 3
> ,2
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,[ActionId]
> ,[ActionId]
> ,[ActionName]
> ,[ActionType]
> ,[From]
> ,[To]
> ,[ActionStart]
> ,[ActionStop]
> ,[ActionDurationThreshold]
> ,[ActionVideoURL]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> WHERE [MSISDN]=@.MSISDN
> --ORDER BY 3, 1
> FOR XML EXPLICIT
> RETURN
> END
> GO
> **** END OF SQL ***********
> Well, it returns the following XML:
> ***** XML *********************
> <RESPONSE>
> <Flow_Name>F06</Flow_Name>
> <MSISDN>9053*********</MSISDN>
> <Timestamp>2007-11-15T14:37:24.467</Timestamp>
> <SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
> <Status>0</Status>
> <Actions>
> <NumOfActions>3</NumOfActions>
> <Action Id="10001">
> <ActionId>10001</ActionId>
> <ActionName>Acme</ActionName>
> <ActionType>1</ActionType>
> <From>00:00:00</From>
> <To>12:00:00</To>
> <ActionStart>2007-11-15T09:52:56.513</ActionStart>
> <ActionStop>2007-11-25T09:52:56.513</ActionStop>
> <ActionDurationThreshold>8</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10002">
> <ActionId>10002</ActionId>
> <ActionName>Pepsi</ActionName>
> <ActionType>1</ActionType>
> <From>12:00</From>
> <To>16:00:00</To>
> <ActionStart>2007-11-15T09:53:17.640</ActionStart>
> <ActionStop>2007-11-25T09:53:17.640</ActionStop>
> <ActionDurationThreshold>7</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10003">
> <ActionId>10003</ActionId>
> <ActionName>Momo Deterjan</ActionName>
> <ActionType>1</ActionType>
> <From>18:00:00</From>
> <To>22:00:00</To>
> <ActionStart>2007-11-15T09:56:32.950</ActionStart>
> <ActionStop>2007-11-25T09:56:32.950</ActionStop>
> <ActionDurationThreshold>6</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> </Actions>
> </RESPONSE>
> **** END OF XML ************
> Therefore, what I want is to assign this XML to a variable. To do
> that, I tried the following (but failed):
> Exec @.RC=SP_F06_GetActions
> @.MSISDN,
> @.SessionId
> So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
> Thanks in advance for your help and interest.
> Regards,
> Ali

How to return the result (XML) with FOR XML EXPLICIT

*damn*
Msg 1086, Level 15, State 1, Line 72
The FOR XML clause is invalid in views, inline functions, derived
tables, and subqueries when they contain a set operator. To work
around, wrap the SELECT containing a set operator using derived table
syntax and apply FOR XML on top of it.
On 15 Kas?m, 16:08, ali.koyu...@.gmail.com wrote:[vbcol=seagreen]
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kasym, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
>
>
>
>
>
>
>
>
>
>
>
|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDNas varchar(20),
@.SessionIdas varchar(64),
@.ResultXMLas xml=NULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Element]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=@.MSISDN
SET @.ResultXML=(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas?m, 16:08, ali.koyu...@.gmail.com wrote:[vbcol=seagreen]
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kasym, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
>
>
>
>
>
>
>
>
>
>
>
|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDNas varchar(20),
@.SessionIdas varchar(64),
@.ResultXMLas xml=NULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Element]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=@.MSISDN
SET @.ResultXML=(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas?m, 16:08, ali.koyu...@.gmail.com wrote:[vbcol=seagreen]
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kasym, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
>
>
>
>
>
>
>
>
>
>
>
|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDNas varchar(20),
@.SessionIdas varchar(64),
@.ResultXMLas xml=NULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Element]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=@.MSISDN
SET @.ResultXML=(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas?m, 16:08, ali.koyu...@.gmail.com wrote:[vbcol=seagreen]
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kasym, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
>
>
>
>
>
>
>
>
>
>
>
|||If you're using SQL 2005, how about using FOR XML PATH instead? Much
simpler.
<ali.koyuncu@.gmail.com> wrote in message
news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
> Dear SQLXML specialists and other animals,
> I got the following SP, that runs a SQL statement with FOR XML
> EXPLICIT:
> **** SQL STATEMENT *****
> CREATE PROCEDURE SP_F06_GetActions
> (
> @.MSISDN as varchar(20),
> @.SessionId as varchar(64)
> )
> AS
> BEGIN
> SELECT
> 1 as tag
> ,NULL as parent
> ,'F06' as [RESPONSE!1!Flow_Name!Element]
> ,[MSISDN] as [RESPONSE!1!MSISDN!Element]
> ,getdate() as [RESPONSE!1!Timestamp!Element]
> ,@.SessionId as [RESPONSE!1!SessionId!Element]
> ,0 as [RESPONSE!1!Status!Element]
> ,NULL as [Actions!2!NumOfActions!Element]
> ,NULL as [Action!3!Id]
> ,NULL as [Action!3!ActionId!Element]
> ,NULL as [Action!3!ActionName!Element]
> ,NULL as [Action!3!ActionType!Element]
> ,NULL as [Action!3!From!Element]
> ,NULL as [Action!3!To!Element]
> ,NULL as [Action!3!ActionStart!Element]
> ,NULL as [Action!3!ActionStop!Element]
> ,NULL as [Action!3!ActionDurationThreshold!Element]
> ,NULL as [Action!3!ActionVideoURL!Element]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 2
> ,1
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> , dbo.getNumOfActionsByMSISDN(MSISDN)
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 3
> ,2
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,[ActionId]
> ,[ActionId]
> ,[ActionName]
> ,[ActionType]
> ,[From]
> ,[To]
> ,[ActionStart]
> ,[ActionStop]
> ,[ActionDurationThreshold]
> ,[ActionVideoURL]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> WHERE [MSISDN]=@.MSISDN
> --ORDER BY 3, 1
> FOR XML EXPLICIT
> RETURN
> END
> GO
> **** END OF SQL ***********
> Well, it returns the following XML:
> ***** XML *********************
> <RESPONSE>
> <Flow_Name>F06</Flow_Name>
> <MSISDN>9053*********</MSISDN>
> <Timestamp>2007-11-15T14:37:24.467</Timestamp>
> <SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
> <Status>0</Status>
> <Actions>
> <NumOfActions>3</NumOfActions>
> <Action Id="10001">
> <ActionId>10001</ActionId>
> <ActionName>Acme</ActionName>
> <ActionType>1</ActionType>
> <From>00:00:00</From>
> <To>12:00:00</To>
> <ActionStart>2007-11-15T09:52:56.513</ActionStart>
> <ActionStop>2007-11-25T09:52:56.513</ActionStop>
> <ActionDurationThreshold>8</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10002">
> <ActionId>10002</ActionId>
> <ActionName>Pepsi</ActionName>
> <ActionType>1</ActionType>
> <From>12:00</From>
> <To>16:00:00</To>
> <ActionStart>2007-11-15T09:53:17.640</ActionStart>
> <ActionStop>2007-11-25T09:53:17.640</ActionStop>
> <ActionDurationThreshold>7</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10003">
> <ActionId>10003</ActionId>
> <ActionName>Momo Deterjan</ActionName>
> <ActionType>1</ActionType>
> <From>18:00:00</From>
> <To>22:00:00</To>
> <ActionStart>2007-11-15T09:56:32.950</ActionStart>
> <ActionStop>2007-11-25T09:56:32.950</ActionStop>
> <ActionDurationThreshold>6</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> </Actions>
> </RESPONSE>
> **** END OF XML ************
> Therefore, what I want is to assign this XML to a variable. To do
> that, I tried the following (but failed):
> Exec @.RC=SP_F06_GetActions
> @.MSISDN,
> @.SessionId
> So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
> Thanks in advance for your help and interest.
> Regards,
> Ali

How to return the first row of each group of rows

I have a group of rows that grouped by host and user. I want to return the first row of each group of hosts. I have struggled with this all day and need some help. With this T-SQL I have built the table:

USE Test

IF OBJECT_ID(N'[dbo].Sample') IS NOT NULL

DROP TABLE [dbo].Sample

-- Create the Sample table

CREATE TABLE [dbo].Sample(

ID [int] IDENTITY NOT NULL,

Host [varchar](128) NOT NULL,

Username [varchar](128) NOT NULL,

Seconds [int] NOT NULL)

SET NOCOUNT ON

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Smith',120012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Jone',100012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Doe',90012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Cole',80012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Lee',70012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C002','Lee',8012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C002','Cole',5012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C002','Smith',4012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Doe',90012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Lee',80012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Cole',7012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Smith',60012);

SET NOCOUNT OFF

I want to write a select statement that will return the first row of each host group:

C001 Smith 120012

C002 Lee 8012

C003 Doe 90012

Thanks for pointing me in the right direction.

Ken

Give a look to the ROW_NUMBER() function in books online; partition by host and order by seconds.|||

Try:

select

a.*

from

dbo.Sample as a

where

not exists(

select

from

dbo.Sample as b

where

b.Host = a.Host

and b.[ID] < a.[ID]

)

-- 2005

;with cte

(

select

Host, Username, Seconds,

row_number() over(partition by Host order by [ID]) as rn

from

dbo.Sample

)

select

from

cte

where

rn = 1

AMB

|||

Also,

select

a.*

from

dbo.Sample as a

where

a.[ID] = (

select

min(b.[ID])

from

dbo.Sample as b

where

b.Host = a.Host

)

AMB

How to return the difference between simple xml on SQL Server 2005?

I have two simple xml as:
<row>
<a>1</a>
3
<c>5</c>
</row>
<row>
<a>2</a>
3
<c>7</c>
</row>
What statement can return the difference between xml as result as:
<row>
<a>Yes</a>
No
<c>Yes</c>
</row>
-- This works, but it's ugly...
DECLARE @.x1 XML, @.x2 XML, @.x XML, @.xv varchar(MAX)
SET @.x1 = '<row><a>1</a>3<c>5</c></row>'
SET @.x2 = '<row><a>2</a>3<c>7</c></row>'
SET @.xv = ''
;WITH x1 AS
(SELECT
T.C.value('.', 'int') AS rowValue
, T.C.value('local-name(.)', 'varchar(10)') AS rowName
FROM @.x1.nodes('row/*') AS T(C))
,x2 AS
(SELECT
T.C.value('.', 'int') AS rowValue
, T.C.value('local-name(.)', 'varchar(10)') AS rowName
FROM @.x2.nodes('row/*') AS T(C))
SELECT @.xv = @.xv + ('<' + x1.rowName + '>'
+ CASE x1.rowValue WHEN x2.rowValue THEN 'Yes' ELSE 'No' END
+ '</' + x1.rowName + '>')
FROM x1 INNER JOIN x2 ON x1.rowName = x2.rowName
SELECT @.x = CAST('<row>' + @.xv + '</row>' AS XML)
SELECT @.x
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"ABC" <abc@.abc.com> wrote in message
news:ecz7aPjuHHA.1496@.TK2MSFTNGP06.phx.gbl...
>I have two simple xml as:
> <row>
> <a>1</a>
> 3
> <c>5</c>
> </row>
>
> <row>
> <a>2</a>
> 3
> <c>7</c>
> </row>
>
> What statement can return the difference between xml as result as:
> <row>
> <a>Yes</a>
> No
> <c>Yes</c>
> </row>
>
|||Note: If we could dynamically construct elements, there would be a much
easier solution...
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"ABC" <abc@.abc.com> wrote in message
news:ecz7aPjuHHA.1496@.TK2MSFTNGP06.phx.gbl...
>I have two simple xml as:
> <row>
> <a>1</a>
> 3
> <c>5</c>
> </row>
>
> <row>
> <a>2</a>
> 3
> <c>7</c>
> </row>
>
> What statement can return the difference between xml as result as:
> <row>
> <a>Yes</a>
> No
> <c>Yes</c>
> </row>
>
|||Thanks you for helpful, I think it can help me.
How about dynamically construct elements can be easiler solution?
"Peter W. DeBetta" <debettap@.hotmail.com> wrote in message
news:eP%23NzKQvHHA.3364@.TK2MSFTNGP02.phx.gbl...
> Note: If we could dynamically construct elements, there would be a much
> easier solution...
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "ABC" <abc@.abc.com> wrote in message
> news:ecz7aPjuHHA.1496@.TK2MSFTNGP06.phx.gbl...
>

How to return the difference between simple xml on SQL Server 2005?

I have two simple xml as:
<row>
<a>1</a>
3
<c>5</c>
</row>
<row>
<a>2</a>
3
<c>7</c>
</row>
What statement can return the difference between xml as result as:
<row>
<a>Yes</a>
No
<c>Yes</c>
</row>-- This works, but it's ugly...
DECLARE @.x1 XML, @.x2 XML, @.x XML, @.xv varchar(MAX)
SET @.x1 = '<row><a>1</a>3<c>5</c></row>'
SET @.x2 = '<row><a>2</a>3<c>7</c></row>'
SET @.xv = ''
;WITH x1 AS
(SELECT
T.C.value('.', 'int') AS rowValue
, T.C.value('local-name(.)', 'varchar(10)') AS rowName
FROM @.x1.nodes('row/*') AS T(C))
,x2 AS
(SELECT
T.C.value('.', 'int') AS rowValue
, T.C.value('local-name(.)', 'varchar(10)') AS rowName
FROM @.x2.nodes('row/*') AS T(C))
SELECT @.xv = @.xv + ('<' + x1.rowName + '>'
+ CASE x1.rowValue WHEN x2.rowValue THEN 'Yes' ELSE 'No' END
+ '</' + x1.rowName + '>')
FROM x1 INNER JOIN x2 ON x1.rowName = x2.rowName
SELECT @.x = CAST('<row>' + @.xv + '</row>' AS XML)
SELECT @.x
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"ABC" <abc@.abc.com> wrote in message
news:ecz7aPjuHHA.1496@.TK2MSFTNGP06.phx.gbl...
>I have two simple xml as:
> <row>
> <a>1</a>
> 3
> <c>5</c>
> </row>
>
> <row>
> <a>2</a>
> 3
> <c>7</c>
> </row>
>
> What statement can return the difference between xml as result as:
> <row>
> <a>Yes</a>
> No
> <c>Yes</c>
> </row>
>|||Note: If we could dynamically construct elements, there would be a much
easier solution...
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"ABC" <abc@.abc.com> wrote in message
news:ecz7aPjuHHA.1496@.TK2MSFTNGP06.phx.gbl...
>I have two simple xml as:
> <row>
> <a>1</a>
> 3
> <c>5</c>
> </row>
>
> <row>
> <a>2</a>
> 3
> <c>7</c>
> </row>
>
> What statement can return the difference between xml as result as:
> <row>
> <a>Yes</a>
> No
> <c>Yes</c>
> </row>
>|||Thanks you for helpful, I think it can help me.
How about dynamically construct elements can be easiler solution?
"Peter W. DeBetta" <debettap@.hotmail.com> wrote in message
news:eP%23NzKQvHHA.3364@.TK2MSFTNGP02.phx.gbl...
> Note: If we could dynamically construct elements, there would be a much
> easier solution...
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "ABC" <abc@.abc.com> wrote in message
> news:ecz7aPjuHHA.1496@.TK2MSFTNGP06.phx.gbl...
>

How to return text data type from stored procedure.

Hi all,
I am having one stored procedure which is returing parameter having
text data type.
This paramter has to take value from table which have column with
datatype as text.
How will i set value to parameter having text datatype to value
present in table?
Any help will be truely appreciated.> I am having one stored procedure which is returing parameter having
> text data type.
> This paramter has to take value from table which have column with
> datatype as text.
> How will i set value to parameter having text datatype to value
> present in table?
You would just SELECT it, not set it to a variable. You can't have a
variable of type TEXT. And you can't RETURN anything other than an INT, so
I assume you meant OUTPUT, not RETURN.
In SQL Server 2005 you can use VARCHAR(MAX) which is a first-class
citizen -- meaning you can DECLARE @.foo VARCHAR(MAX) and proceed with
storing 2GB of text in there if you want.
A|||> I am having one stored procedure which is returing parameter having
> text data type.
You can't return anything but an INT, and it is *NOT* meant to return
*DATA* -- return values are meant to return error/status. Single data
elements that are not part of a resultset should be "returned" via an OUTPUT
parameter.
And you can't store TEXT as a local variable... maybe you could use
VARCHAR(MAX) in SQL Server 2005. Otherwise all you can do is SELECT
TextColumn FROM table and have the application consume the result that way.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Monday, March 12, 2012

How to return text data type from stored procedure.

How to Return SqlDataReader and return value (page count) from SPROC

This is my function, it returns SQLDataReader to DATALIST control. Howto return page number with the SQLDataReader set ? sql server 2005,asp.net 2.0

Function get_all_events() As SqlDataReader
Dim myConnection As NewSqlConnection(ConfigurationManager.AppSettings("........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim parameterState As New SqlParameter("@.State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)

Dim parameterPagesize As New SqlParameter("@.pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)

Dim parameterPagenum As New SqlParameter("@.pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)

Dim parameterPageCount As New SqlParameter("@.pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)

myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@.pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function

Variable Pages is global integer.

This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

please any help ? finally following code works without error, but still not returning any return value (pagecount) along with the list of records.

Dim MyReader As SqlDataReader
Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("Dx918Aveb8ax81"))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim parameterregion As New SqlParameter("@.Region", SqlDbType.VarChar, 5)
parameterregion.Value = Request.Params("reg")
myCommand.Parameters.Add(parameterregion)

Dim parameterPagesize As New SqlParameter("@.pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 10
myCommand.Parameters.Add(parameterPagesize)

Dim parameterPagenum As New SqlParameter("@.pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)

Dim parameterPageCount As New SqlParameter("@.pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)

myConnection.Open()
MyReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
pages = myCommand.Parameters("@.pagecount").Value
DataList1.DataSource = MyReader
DataList1.DataBind()
myCommand.Dispose()
myConnection.Dispose()
Response.Write(pages)

|||Output parameters and return values are not available until after aDataReader is closed (which you should add after your DataBind()).|||thank you. I will try that today!!

How to return specific Row in sql server

Hi,
Suppose I have 100 rows in my table, I want to retrieve say row no 5(I just want any thing on row no 5), I don't want to use identity column. Is there any other way by which I can retrieve specified row.
Thanks,
Sajidin relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/|||Originally posted by r937
in relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/

Thanks for answering my question.

Say suppose I have Name column and i want the 5th record on name column.

Thanks
Sajid|||Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW|||Originally posted by GWilliy
Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW

Yes I need the same.|||select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy|||Originally posted by r937
select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy

Thanks, It works.

If suppose i want to retrieve all columns, then do I need to specific each column.|||U could actualy do a sraight select into a cursor & then go straight to the Row

DECLARE @.a VarChar(20),@.b,@.c

DECLARE myCursor CURSOR SCROLL
FOR
SELECT a,b,c FROM myTable
OPEN myCursor
FETCH ABSOLUTE 5 INTO @.a,@.b,@.c
BEGIN
PRINT @.a
PRINT @.b
PRINT @.c
END
CLOSE myCursor
DEALLOCATE myCursor

example Code to give U an Idea - There are many options available when declaring Cursors etc.

May be more suitable for whatever Sajidrep is Doing

GW

How to return sets by filtering those tuples with null values in a MDX statement?

Hi, dear friends,

How can we return the result by filtering tuples with null values? (e.g. I want to return the result for tuples specified by dimention DimA, measures B,C. therefore I want to filter the result with nulls values of B,C for DimA).

Hope it is clear for your help.

And I am looking forward to hearing from you shortly for your help.

Thanks a lot.

With kind regards,

Yours sincerely,

It depends what you mean by "filter" - do you want the null values included or excluded?

And what does B,C mean? Do you mean if either is null or both?

The following is one solution if you want to return members from DimA where both measures are empty (null).

Filter(DimA.members, IsEmtpy(Measures.B) AND IsEmpty(Measures.C)

but I would expect somthing like this could be faster as it could use block computation instead of cell-by-cell evaluation.

INTERSECT(NonEmpty(DimA.Members, Measures.B),NonEmpty(DimA.members, Measures.C))

|||

Hi, Darren,

Thanks a lot for your help.

With kind regards,

Yours sincerely,

Friday, March 30, 2012

How To Script Jobs In SQL Server ?

Wednesday, March 21, 2012

How to run an insert without returning anything

Monday, March 19, 2012

How to return xml datatype column values ?

How to return varchar(MAX) from a CLR function?

How to return value from Stored Procedure(SQL Server) into c

How to return value from dynamic query or set the value

how to return UTF 8 string from nvarchar field

how to return truncated field

How to return time & number format that has set in the regional setting using stored proce

How to return the result of an EXEC from a function

How to return the result (XML) with FOR XML EXPLICIT

How to return the result (XML) with FOR XML EXPLICIT

How to return the first row of each group of rows

How to return the difference between simple xml on SQL Server 2005?

How to return the difference between simple xml on SQL Server 2005?

How to return text data type from stored procedure.

Monday, March 12, 2012

How to return text data type from stored procedure.

How to Return SqlDataReader and return value (page count) from SPROC

How to return specific Row in sql server

How to return sets by filtering those tuples with null values in a MDX statement?

restrict access to database

Blog Archive

About Me