how to capture o/p of SELECT .. FOR XML AUTO.

Hi,
Can u please tell me how can i store o/p of following query into a variable
and process it.
"SELECT TOP 1 FIRSTNAME, LASTNAME FROM PATIENT FOR XML AUTO"
Thanks
Gopinath M.
"Gopinath Munisifreddy" <Gopinath@.Microsoft.com> wrote in message
news:eaMr75zEEHA.3336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Can u please tell me how can i store o/p of following query into a
variable
> and process it.
> "SELECT TOP 1 FIRSTNAME, LASTNAME FROM PATIENT FOR XML AUTO"
You can't store the results of a for xml query just within SQL. The XML is
created by the provider.
Bryant
|||There are some ugly workarounds using the sp_OA stored procedures.
In SQL Server 2005 (beta2 comming soon, watch this space for nomination
registration), you will be able to do so.
Best regards
Michael
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:Ogsvi70EEHA.3568@.tk2msftngp13.phx.gbl...
> "Gopinath Munisifreddy" <Gopinath@.Microsoft.com> wrote in message
> news:eaMr75zEEHA.3336@.TK2MSFTNGP12.phx.gbl...
> variable
> You can't store the results of a for xml query just within SQL. The XML is
> created by the provider.
> --
> Bryant
>

How to cancel a processing request?

Hello ,

I've execute a wrong sql request from an ETL. This request is inserting lots of rows in a table. I want to cancel that process, is it possible to do this? If yes, how can I cancel that request?

Thanks,

Stop or 'kill' the client application that is doing the inserts.|||

Find out the SPID that the request is running on, and then run KILL <thespidnumber>

You can probably find the SPID by running the query below, or by using sp_who2

SETNOCOUNTON;

DECLARE @.SpID smallint

DECLARE spID_Cursor CURSOR

FORWARD_ONLYREAD_ONLYFOR

SELECTTOP 25 spid

FROM master..sysprocesses

WHEREstatus='runnable'

AND spid > 50 -- Eliminate system SPIDs

AND spid <> 102 -- Replace with your SPID

ORDERBY CPU DESC

OPEN spID_Cursor

FETCHNEXTFROM spID_Cursor

INTO @.spID

WHILE@.@.FETCH_STATUS= 0

BEGIN

PRINT'Spid #: '+STR(@.spID)

EXEC('DBCC INPUTBUFFER ('+ @.spID +')')

FETCHNEXTFROM spID_Cursor

INTO @.spID

END

-- Close and deallocate the cursor

CLOSE spID_Cursor

DEALLOCATE spID_Cursor

|||thx more than I expected

How to cancel a processing request?

Hello ,

I've execute a wrong sql request from an ETL. This request is inserting lots of rows in a table. I want to cancel that process, is it possible to do this? If yes, how can I cancel that request?

Thanks,

Stop or 'kill' the client application that is doing the inserts.|||

Find out the SPID that the request is running on, and then run KILL <thespidnumber>

You can probably find the SPID by running the query below, or by using sp_who2

SETNOCOUNTON;

DECLARE @.SpID smallint

DECLARE spID_Cursor CURSOR

FORWARD_ONLYREAD_ONLYFOR

SELECTTOP 25 spid

FROM master..sysprocesses

WHEREstatus='runnable'

AND spid > 50 -- Eliminate system SPIDs

AND spid <> 102 -- Replace with your SPID

ORDERBY CPU DESC

OPEN spID_Cursor

FETCHNEXTFROM spID_Cursor

INTO @.spID

WHILE@.@.FETCH_STATUS= 0

BEGIN

PRINT'Spid #: '+STR(@.spID)

EXEC('DBCC INPUTBUFFER ('+ @.spID +')')

FETCHNEXTFROM spID_Cursor

INTO @.spID

END

-- Close and deallocate the cursor

CLOSE spID_Cursor

DEALLOCATE spID_Cursor

|||thx more than I expected

How to call the processing of a cube ?

I need to automatically process a cube. Do I have to use an Analysis Managment Object (AMO) ?

Otherwise, I know that it is possible to create a Sql Agent Job to run periodically, but I don't want to schedule this task. In fact, I need to "call" the processing of my cube, wich will run between other jobs. The "caller" is a Visual Basic 6 program from wich I am able to launch .exe files.

thanks

Hello. I am not sure if you are taking about SSSAS2000 or SSAS2005?

Anyway, you can make a dts-package in SQL Server 2000 or a SSIS-package in BI-Dev Studio that process the dimensions and the cubes.

These packages are possible to call by a VB-application but I think you must use VB6 for dts and VB.Net 2005 for SSIS(Integration services).

HTH

Thomas Ivarsson

|||

Hi. You can also use the command prompt utility "DTEXEC" from VB6 to call a package developed with SSIS if you have to. However, Thomas' suggestion of using VB .NET 2005 is best. Here's more information on the DTEXEC utility.

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

Paul Goldy

|||

Here are a couple of articles I published with step-by-step setup for the DTS / SSIS scenarios involved:

For SSAS 2k (DTS):

http://www.databasejournal.com/features/mssql/article.php/3503201

For SSAS 2k5 (SSIS):

http://www.databasejournal.com/features/mssql/article.php/3584306

Good Luck!

Bill

William E. Pearson III
CPA, CMA, CIA, MCSE, MCDBA
Island Technologies Inc.
931 Monroe Drive
Suite 102-321
Atlanta, GA 30308

404.872.5972 Office
wep3@.islandtechnologies.com
wep3@.msas-architect.com

www.msas-architect.com
-- -- --

Publisher Sites:

http://www.databasejournal.com/article.php/1459531

http://www.sql-server-performance.com/bill_pearson.asp

http://www.informit.com/authors/bio.asp?a=862acd62-4662-49ae-879d-541c8b4d656f

http://www.2000trainers.com/section.aspx?sectionID=17

|||

Thanks ! What I need is in the first article link you posted, Bill. I will create a DTS package.

The problem is, I can't do the step #9 on page 3. I don't have the Analysis Services Processing Task ICON in the TASKS TOOLBAR ... ?

If that can helps, I work with : SQL Server Enterprise Manager version 8.0
Analysis Manager version 5
Visual Basic 6 (the program that calls the DTS package)

So, what do you think ?

PS - I found how to call the package with VB6 on this page :
http://www.sqlservercentral.com/columnists/bknight/executingpackagefromvisualbasic.asp

EDIT : I found on the Microsoft web site that the Analysis Processing task is available only when SQL Server 2000 Analysis Services is installed.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx

How to call Oracle Stored Procedure which has an output parameter from SSIS?

I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:

PROCEDURE Interface_Begin

(p_from_dttmOUT varchar2,

p_error_codeOUT number,

p_error_textOUTvarchar2,

p_proc_nameOUT varchar2);

please check this

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1711335&SiteID=1

http://microsoftdw.blogspot.com/2005/11/parameterized-queries-against-oracle.html

-Nikul

|||

Could you please be more specific? I tried the following:

1) The stored proc spec is as follows:

Procedure testing(myDate OUT varchar2);

2) Created a Data Flow Task.

3) Created 2 variables called myStoredProc & myDate at the package level.

4) In the value for myDate variable i set it to myDate.

5) In the properties for myStoredProc variable i changed the EvaluateAsExpression property to True.

6) In the expression for myStoredProc variable i have the following:

"declare myDate varchar2(50); begin sa.testing(" + @.[User::myDate] + "); end;"

7) Now inside my Data Flow Task i have a Ole Db DataSource connection set to Native OLE DB\Microsoft Ole Db

Provider for Oracle.

8) Data Access Mode set to SQL Command from Variable.

9) The value of the Variable Name is User::myStoredProc.

10) Now when i hit preview i get the following error Message.

No disconnected record set is available for the specified SQL statement.

I am not sure what's wrong here. Could someone help me?

|||Anyone?|||Try doing this from a Execute SQL task, not a Source component. Source components expect a recordset, not an output parameter.|||

Hi there,

As Jwelch said use Execute SQL Task and select Single Row in Recordset option.

I think this will iron out your issue.

Please specify if this does not work.

Thanks

|||

Thanks guys. Now i am able to execute it successfully. But i am getting some junk characters in the Output parameters. Here is the stored proc definition:

create or replace procedure testing(myDate OUT varchar2)
IS
BEGIN
myDate := 'hey';
return;
END;

This should return 'hey' but i am getting this:


)

Any ideas?

How to bypass log file ?

Rob wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any wa
y
> to do the row transfer without affecting the size of the log file ? I ha
ve
> filled up the hard drive before doing such activities.
> Thanks
You can't "bypass" the transaction log, nor would you want to. What
you can do is archive your records in batches, rather than all at once.
Doing the work in batches will keep the individual transactions small,
allowing them to commit faster, and your transaction log backups that
you run at regular intervals will then remove those committed
transactions from the transaction log.I am basically archiving some data from one db to another...
This process causes a great increase in the size of the log file. Any way
to do the row transfer without affecting the size of the log file ? I have
filled up the hard drive before doing such activities.
Thanks|||Rob wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any wa
y
> to do the row transfer without affecting the size of the log file ? I ha
ve
> filled up the hard drive before doing such activities.
> Thanks
You can't "bypass" the transaction log, nor would you want to. What
you can do is archive your records in batches, rather than all at once.
Doing the work in batches will keep the individual transactions small,
allowing them to commit faster, and your transaction log backups that
you run at regular intervals will then remove those committed
transactions from the transaction log.|||You can't turn off or bypass transaction logging. If you are moving a very
large amount of data on a regular basis such as once every month, quater, or
year, you may consider using table partitioning in SQL2005, which allows you
to slide a partition (that may correspond to a month worth of data) in and
out of a table as a SQL Server meta-data operation thus without causing your
tran log to fill up. Contrast this with having to delete and insert every
single row for the month.
Linchi
"Rob" wrote:

> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any wa
y
> to do the row transfer without affecting the size of the log file ? I ha
ve
> filled up the hard drive before doing such activities.
> Thanks
>
>|||You can't turn off or bypass transaction logging. If you are moving a very
large amount of data on a regular basis such as once every month, quater, or
year, you may consider using table partitioning in SQL2005, which allows you
to slide a partition (that may correspond to a month worth of data) in and
out of a table as a SQL Server meta-data operation thus without causing your
tran log to fill up. Contrast this with having to delete and insert every
single row for the month.
Linchi
"Rob" wrote:

> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any wa
y
> to do the row transfer without affecting the size of the log file ? I ha
ve
> filled up the hard drive before doing such activities.
> Thanks
>
>

How to bypass log file ?

I am basically archiving some data from one db to another...
This process causes a great increase in the size of the log file. Any way
to do the row transfer without affecting the size of the log file ? I have
filled up the hard drive before doing such activities.
ThanksRob wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any way
> to do the row transfer without affecting the size of the log file ? I have
> filled up the hard drive before doing such activities.
> Thanks
You can't "bypass" the transaction log, nor would you want to. What
you can do is archive your records in batches, rather than all at once.
Doing the work in batches will keep the individual transactions small,
allowing them to commit faster, and your transaction log backups that
you run at regular intervals will then remove those committed
transactions from the transaction log.|||You can't turn off or bypass transaction logging. If you are moving a very
large amount of data on a regular basis such as once every month, quater, or
year, you may consider using table partitioning in SQL2005, which allows you
to slide a partition (that may correspond to a month worth of data) in and
out of a table as a SQL Server meta-data operation thus without causing your
tran log to fill up. Contrast this with having to delete and insert every
single row for the month.
Linchi
"Rob" wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any way
> to do the row transfer without affecting the size of the log file ? I have
> filled up the hard drive before doing such activities.
> Thanks
>
>