Is it possible to query a server's DBCC SHOWFILESTATS from one server to another?

I am trying to write a single query that will go out a collect all the
DBCC SHOWFILESTATS. The trick is that the query has to attach to the
remote server, then run the DBCC SHOWFILESTATS, and pull that
information back.
e.g. use servername2.master DBCC SHOWFILESTATS
This obviouly does not work even with the linked server option. Is
there another way to do this? I know you can use a select statement and
a linked server connection to query remote databases.you could create a procedure like this then call the proc on the server
create proc prShowFileStats
as
set nocount on
create table #SHOWFILESTATS (FileID int,
FileGroup int,
TotalExtents int,
UsedExtents int,
Name varchar(100),
FileName varchar(100))
insert into #SHOWFILESTATS
exec ('DBCC SHOWFILESTATS')
select * from #SHOWFILESTATS
set nocount off
http://sqlservercode.blogspot.com/

Is it possible to query a prior query?

Is there some way to save a selection set in SQL
so that another SQL query can further refine the
prior query?Peter Olcott (olcott@.worldnet.att.net) writes:
> Is there some way to save a selection set in SQL
> so that another SQL query can further refine the
> prior query?

http://www.algonet.se/~sommar/share_data.html may give you some
suggestions.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||My suggestion is to dynamicly build view.You can query view as any other
table.You can also dynamicly build SQL SELECT statement and add conditions
in WHERE clause and execute it.sql

Is it Possible to query a Cube with Query Analyzer?

i would like to query either a cube or dimension direclty using tsql and query analyzer. i thought i would be able to use openquery but cant figure out the proper syntax. any help would be appreciated.

This is an example in a previous discussion here. I have not tried this example myself.

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

HTH

Thomas Ivarsson

Is it possible to put several queries into one sp

I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.

After that query could be a good time to count total of all calculated sub sums.

Something like this.

previous query

END

go
SELECT SUM(SubTotal)
FROM dbo.t_Shoppings

I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)

Is this possible or do I have make and call another stored procedure.

I can send my sp if someone wants.

No, this is for sure possible. Try taking the "END" between your first and second query and put it after your second query. Let me know what errors you are getting when you try to execute it if that doesn't work

|||

you can just put queries on eby one withiout any separator , just start every select statement in new line. Do not use GO it unless you run your query in Management studio environment (Go is not SQL comamnd)

|||

Thank you both for your advises. I'll test the sp later today. And I have to check how to return parameters and so on first.

I'll mark your answers as answers then too.

Regards

Leif

|||

Hi

I tried today my sp and got it working. Server management studio didn't show output parameter right but the query itself seems to work. I'll test it with code later.

Thanks for help.

Here is my stored procedure now. I didn't translate it, so variables may look strange. Kokonaissumma means total value btw.

create PROCEDURE [dbo].[kori2]
(
@.Tuotekoodi varchar(20),
@.kokonaissumma money output
)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_osto WHERE Tuotekoodi=@.Tuotekoodi)
BEGIN
INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)
SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv
FROM dbo.t_Tuote
WHERE Tuotekoodi= @.Tuotekoodi
END
ELSE
BEGIN
UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
END

return (SELECT count(*) FROM dbo.t_osto)

(select @.kokonaissumma=sum(Yhteensa)FROM dbo.t_osto)

is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

Is it possible to display the selected label (not value) of a query based Parameter in rep

Hi,
If I have a query based parameter which has labels and values, for example,
parameter: Department, label: DepartmentName, Value: DepartmentID, I am
using the parameter value (e.g. DepartmentID) in the dataset filter, but I
want to display the parameter label in the report page footer. Is there an
easy way to do it? Right now I can use only Parameters!Department.Value
which displays the chosen DepartmentID.
Thanks.You can access the report parameter label by using the Label property:
=Parameters!Department.Label
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wendy H" <WendyH@.tequilasoftware.com> wrote in message
news:%23sh1GK$eEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If I have a query based parameter which has labels and values, for
example,
> parameter: Department, label: DepartmentName, Value: DepartmentID, I am
> using the parameter value (e.g. DepartmentID) in the dataset filter, but I
> want to display the parameter label in the report page footer. Is there an
> easy way to do it? Right now I can use only Parameters!Department.Value
> which displays the chosen DepartmentID.
> Thanks.
>
>

Is it possible to create the columns of a #Temp table base on a query result?

I’ve been trying without success something like this…

CREATE TABLE #table

(

(SELECT MAX(Column) FROM Table) varchar(50)

)

I'm working with SQL 2005; thank you for any help in advance.

Not quite like that.

You can SELECT ... INTO.

In the process, you need to provide a column Name for any computed or derived columns, and you can change the datatype.

Something like this:

Code Snippet

USE Northwind
GO

SELECT cast( max( EmployeeID ) AS decimal(6,2)) AS MaxEmp
INTO #MyTable
FROM Employees

|||

There are 2 options,

Create Table #Table (

ColumnValue Varchar(50)

);

Insert Into #Table

SELECT MAX(Column) FROM TableName;

--OR

SELECT Cast(MAX(Column) as Varchar(50)) ColumnValue Into #Table FROM TableName;

|||Thank you for the help provided so far. Another question on the same subject. How can the Column name be assigned dynamically after a query result? Thanks.
|||

You should use the aliase name. if you failed to give the aliase name for the expression sql server thow an error says that "No column was specified for column n on tablename".

Code Snippet

Select Max(Column) as MaxColumn Into NewTable From OldTable

Select Max(Column) MaxColumn into Newtable From OldTable

|||

As indicated above, No.

My apologies, I guess this statement was not clear enough.

In the process, you need to provide a column Name for any computed or derived columns...

sql

Is it possible to call an Oracle stored procedure and pass parameters to them from reporti

I've set up a shared datasource to oracle with the option of prompting for
credientals. The report works with just static SQL in the Query designer. I
have been trying for a number of days now to use a call to an oracle stored
procedure or function as my report data set without success.
The function called sp_ListEmp has one parameter of type number. To call the
function I have tried adding {call sp_ListEmp(?)} into the Query designer
and setting the command type to Text. I have added the parameter ? and made
that equal to a Report parameter EMPNO of Integer type that I have added to
the report. So I have in the parameter tab of the Dataset
?=Parameters!EMPNO.Value. I recieve the following error message 'An error
occurred while executing the query. ORA-01036: illegal variable
name/number.'
For the stored procedure curspkg_join.open_join_cursor1 I have not even been
able to call the procedure with recieving an error for the second parameter
which is of type refcursor. Does anyone know if it is possible to call
Oracle stored procedures and pass parameters to them from reporting
services?
I have added the code for the stored procedure and function and also the
tables for which I have been working on below and also the commands I use in
SQL Plus to prove that the actual function and stored procedure work. This
is causing me much woe.
Create DEPT table and insert some rows
CREATE TABLE DEPT
(DEPTNO NUMBER(2,0) NOT NULL,
DNAME VARCHAR2(14) NULL,
LOC VARCHAR2(13) NULL,
PRIMARY KEY (DEPTNO)
);
INSERT INTO Dept VALUES(11,'Sales','Texas');
INSERT INTO Dept VALUES(22,'Accounting','Washington');
INSERT INTO Dept VALUES(33,'Finance','Maine');
Create EMP table and insert some rows
CREATE TABLE EMP
(EMPNO NUMBER(4,0) NOT NULL,
ENAME VARCHAR2(10) NULL,
JOB VARCHAR2(9) NULL,
MGR NUMBER(4,0) NULL,
SAL NUMBER(7,2) NULL,
COMM NUMBER(7,2) NULL,
DEPTNO NUMBER(2,0) NULL,
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
PRIMARY KEY (EMPNO)
);
INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
Create package for Function example
create or replace package packperson
as
type cursorType is ref cursor;
end;
/
Create Function for Function example
create or replace function sp_ListEmp (n_EMPNO NUMBER) return
packperson.cursortype
as
l_cursor packperson.cursorType;
begin
open l_cursor for select ename as NAME, empno as NUM from emp where
empno = n_EMPNO order by ename;
return l_cursor;
end;
From SQL Plus I call this by
SQL> variable c refcursor
SQL> exec :c := sp_ListEmp(123)
SQL> print c
Create package for sp example
CREATE OR REPLACE PACKAGE curspkg_join AS
TYPE t_cursor IS REF CURSOR ;
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
t_cursor);
END curspkg_join;
/
Create package body for sp example
CREATE OR REPLACE PACKAGE BODY curspkg_join AS
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
t_cursor)
IS
v_cursor t_cursor;
BEGIN
IF n_EMPNO <> 0
THEN
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.EMPNO = n_EMPNO;
ELSE
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
END IF;
io_cursor := v_cursor;
END open_join_cursor1;
END curspkg_join;
/
From SQL Plus I call this by
SQL> variable c refcursor
SQL>exec curspkg_join.open_join_cursor1(123,:c)
SQL>print :cThe cursor in the stored procedure has to be an OUT REF cursor rather than a
IN OUT cursor.
Also make sure you use the managed Oracle provider (i.e. "Oracle" in the
data source dialog) and not OleDB.
You might also want to check this previous posting for further information
and a sample:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=049fe955-cec8-4a79-a5e5-a9c02873e19d&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe" <booksnore2@.netscape.net> wrote in message
news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> I've set up a shared datasource to oracle with the option of prompting for
> credientals. The report works with just static SQL in the Query designer.
I
> have been trying for a number of days now to use a call to an oracle
stored
> procedure or function as my report data set without success.
> The function called sp_ListEmp has one parameter of type number. To call
the
> function I have tried adding {call sp_ListEmp(?)} into the Query designer
> and setting the command type to Text. I have added the parameter ? and
made
> that equal to a Report parameter EMPNO of Integer type that I have added
to
> the report. So I have in the parameter tab of the Dataset
> ?=Parameters!EMPNO.Value. I recieve the following error message 'An error
> occurred while executing the query. ORA-01036: illegal variable
> name/number.'
> For the stored procedure curspkg_join.open_join_cursor1 I have not even
been
> able to call the procedure with recieving an error for the second
parameter
> which is of type refcursor. Does anyone know if it is possible to call
> Oracle stored procedures and pass parameters to them from reporting
> services?
> I have added the code for the stored procedure and function and also the
> tables for which I have been working on below and also the commands I use
in
> SQL Plus to prove that the actual function and stored procedure work. This
> is causing me much woe.
>
> Create DEPT table and insert some rows
> CREATE TABLE DEPT
> (DEPTNO NUMBER(2,0) NOT NULL,
> DNAME VARCHAR2(14) NULL,
> LOC VARCHAR2(13) NULL,
> PRIMARY KEY (DEPTNO)
> );
> INSERT INTO Dept VALUES(11,'Sales','Texas');
> INSERT INTO Dept VALUES(22,'Accounting','Washington');
> INSERT INTO Dept VALUES(33,'Finance','Maine');
> Create EMP table and insert some rows
> CREATE TABLE EMP
> (EMPNO NUMBER(4,0) NOT NULL,
> ENAME VARCHAR2(10) NULL,
> JOB VARCHAR2(9) NULL,
> MGR NUMBER(4,0) NULL,
> SAL NUMBER(7,2) NULL,
> COMM NUMBER(7,2) NULL,
> DEPTNO NUMBER(2,0) NULL,
> FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
> PRIMARY KEY (EMPNO)
> );
> INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
> INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
> INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
> Create package for Function example
> create or replace package packperson
> as
> type cursorType is ref cursor;
> end;
> /
> Create Function for Function example
> create or replace function sp_ListEmp (n_EMPNO NUMBER) return
> packperson.cursortype
> as
> l_cursor packperson.cursorType;
> begin
> open l_cursor for select ename as NAME, empno as NUM from emp where
> empno = n_EMPNO order by ename;
> return l_cursor;
> end;
> From SQL Plus I call this by
> SQL> variable c refcursor
> SQL> exec :c := sp_ListEmp(123)
> SQL> print c
>
> Create package for sp example
> CREATE OR REPLACE PACKAGE curspkg_join AS
> TYPE t_cursor IS REF CURSOR ;
> Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> t_cursor);
> END curspkg_join;
> /
> Create package body for sp example
> CREATE OR REPLACE PACKAGE BODY curspkg_join AS
> Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> t_cursor)
> IS
> v_cursor t_cursor;
> BEGIN
> IF n_EMPNO <> 0
> THEN
> OPEN v_cursor FOR
> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> FROM EMP, DEPT
> WHERE EMP.DEPTNO = DEPT.DEPTNO
> AND EMP.EMPNO = n_EMPNO;
> ELSE
> OPEN v_cursor FOR
> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> FROM EMP, DEPT
> WHERE EMP.DEPTNO = DEPT.DEPTNO;
> END IF;
> io_cursor := v_cursor;
> END open_join_cursor1;
> END curspkg_join;
> /
> From SQL Plus I call this by
> SQL> variable c refcursor
> SQL>exec curspkg_join.open_join_cursor1(123,:c)
> SQL>print :c
>|||Thank you for your help,
I'm still unsure of the syntax that I should use in Reporting Services when
calling the sp. Where/how do I reference the out ref cursor? Example I call
the procedure using -
{ call test_package.get_customers(?, ) }
I get the following error 'The data extension Oracle does not support
unnamed parameters. Use named parameters instead.'
So my question is how do I reference the out ref cursor in Reporting
services?
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:e1$dlvyiEHA.1656@.TK2MSFTNGP09.phx.gbl...
> The cursor in the stored procedure has to be an OUT REF cursor rather than
a
> IN OUT cursor.
> Also make sure you use the managed Oracle provider (i.e. "Oracle" in the
> data source dialog) and not OleDB.
> You might also want to check this previous posting for further information
> and a sample:
>
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=049fe955-cec8-4a79-a5e5-a9c02873e19d&sloc=en-us
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Joe" <booksnore2@.netscape.net> wrote in message
> news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > I've set up a shared datasource to oracle with the option of prompting
for
> > credientals. The report works with just static SQL in the Query
designer.
> I
> > have been trying for a number of days now to use a call to an oracle
> stored
> > procedure or function as my report data set without success.
> > The function called sp_ListEmp has one parameter of type number. To call
> the
> > function I have tried adding {call sp_ListEmp(?)} into the Query
designer
> > and setting the command type to Text. I have added the parameter ? and
> made
> > that equal to a Report parameter EMPNO of Integer type that I have added
> to
> > the report. So I have in the parameter tab of the Dataset
> > ?=Parameters!EMPNO.Value. I recieve the following error message 'An
error
> > occurred while executing the query. ORA-01036: illegal variable
> > name/number.'
> >
> > For the stored procedure curspkg_join.open_join_cursor1 I have not even
> been
> > able to call the procedure with recieving an error for the second
> parameter
> > which is of type refcursor. Does anyone know if it is possible to call
> > Oracle stored procedures and pass parameters to them from reporting
> > services?
> >
> > I have added the code for the stored procedure and function and also the
> > tables for which I have been working on below and also the commands I
use
> in
> > SQL Plus to prove that the actual function and stored procedure work.
This
> > is causing me much woe.
> >
> >
> > Create DEPT table and insert some rows
> >
> > CREATE TABLE DEPT
> > (DEPTNO NUMBER(2,0) NOT NULL,
> > DNAME VARCHAR2(14) NULL,
> > LOC VARCHAR2(13) NULL,
> > PRIMARY KEY (DEPTNO)
> > );
> >
> > INSERT INTO Dept VALUES(11,'Sales','Texas');
> > INSERT INTO Dept VALUES(22,'Accounting','Washington');
> > INSERT INTO Dept VALUES(33,'Finance','Maine');
> >
> > Create EMP table and insert some rows
> >
> > CREATE TABLE EMP
> > (EMPNO NUMBER(4,0) NOT NULL,
> > ENAME VARCHAR2(10) NULL,
> > JOB VARCHAR2(9) NULL,
> > MGR NUMBER(4,0) NULL,
> > SAL NUMBER(7,2) NULL,
> > COMM NUMBER(7,2) NULL,
> > DEPTNO NUMBER(2,0) NULL,
> > FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
> > PRIMARY KEY (EMPNO)
> > );
> >
> > INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
> > INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
> > INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
> >
> > Create package for Function example
> >
> > create or replace package packperson
> > as
> > type cursorType is ref cursor;
> > end;
> > /
> >
> > Create Function for Function example
> >
> > create or replace function sp_ListEmp (n_EMPNO NUMBER) return
> > packperson.cursortype
> > as
> > l_cursor packperson.cursorType;
> > begin
> > open l_cursor for select ename as NAME, empno as NUM from emp where
> > empno = n_EMPNO order by ename;
> >
> > return l_cursor;
> > end;
> >
> > From SQL Plus I call this by
> >
> > SQL> variable c refcursor
> > SQL> exec :c := sp_ListEmp(123)
> > SQL> print c
> >
> >
> >
> > Create package for sp example
> >
> > CREATE OR REPLACE PACKAGE curspkg_join AS
> > TYPE t_cursor IS REF CURSOR ;
> > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > t_cursor);
> > END curspkg_join;
> > /
> >
> > Create package body for sp example
> >
> > CREATE OR REPLACE PACKAGE BODY curspkg_join AS
> > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > t_cursor)
> > IS
> > v_cursor t_cursor;
> > BEGIN
> > IF n_EMPNO <> 0
> > THEN
> > OPEN v_cursor FOR
> > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > FROM EMP, DEPT
> > WHERE EMP.DEPTNO = DEPT.DEPTNO
> > AND EMP.EMPNO = n_EMPNO;
> >
> > ELSE
> > OPEN v_cursor FOR
> > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > FROM EMP, DEPT
> > WHERE EMP.DEPTNO = DEPT.DEPTNO;
> >
> > END IF;
> > io_cursor := v_cursor;
> > END open_join_cursor1;
> > END curspkg_join;
> > /
> >
> > From SQL Plus I call this by
> >
> > SQL> variable c refcursor
> > SQL>exec curspkg_join.open_join_cursor1(123,:c)
> > SQL>print :c
> >
> >
>|||Make sure you use the generic text-based query designer with 2 panes, rather
than the visual query designer with 4 panes.
Regarding the OUT cursor - don't reference it, just omit that parameter.
A better practice for stored procedures is to set the command type of the
query to StoredProcedure instead of Text. In that case, the query text is
just the name of the stored procedure "test_package.get_customers". The
parameters will automatically be determined by report designer on clicking
on the refresh fields icon.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe" <booksnore2@.netscape.net> wrote in message
news:uZsHcq%23iEHA.384@.TK2MSFTNGP10.phx.gbl...
> Thank you for your help,
> I'm still unsure of the syntax that I should use in Reporting Services
when
> calling the sp. Where/how do I reference the out ref cursor? Example I
call
> the procedure using -
> { call test_package.get_customers(?, ) }
> I get the following error 'The data extension Oracle does not support
> unnamed parameters. Use named parameters instead.'
> So my question is how do I reference the out ref cursor in Reporting
> services?
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:e1$dlvyiEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > The cursor in the stored procedure has to be an OUT REF cursor rather
than
> a
> > IN OUT cursor.
> > Also make sure you use the managed Oracle provider (i.e. "Oracle" in the
> > data source dialog) and not OleDB.
> >
> > You might also want to check this previous posting for further
information
> > and a sample:
> >
>
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=049fe955-cec8-4a79-a5e5-a9c02873e19d&sloc=en-us
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Joe" <booksnore2@.netscape.net> wrote in message
> > news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > > I've set up a shared datasource to oracle with the option of prompting
> for
> > > credientals. The report works with just static SQL in the Query
> designer.
> > I
> > > have been trying for a number of days now to use a call to an oracle
> > stored
> > > procedure or function as my report data set without success.
> > > The function called sp_ListEmp has one parameter of type number. To
call
> > the
> > > function I have tried adding {call sp_ListEmp(?)} into the Query
> designer
> > > and setting the command type to Text. I have added the parameter ? and
> > made
> > > that equal to a Report parameter EMPNO of Integer type that I have
added
> > to
> > > the report. So I have in the parameter tab of the Dataset
> > > ?=Parameters!EMPNO.Value. I recieve the following error message 'An
> error
> > > occurred while executing the query. ORA-01036: illegal variable
> > > name/number.'
> > >
> > > For the stored procedure curspkg_join.open_join_cursor1 I have not
even
> > been
> > > able to call the procedure with recieving an error for the second
> > parameter
> > > which is of type refcursor. Does anyone know if it is possible to call
> > > Oracle stored procedures and pass parameters to them from reporting
> > > services?
> > >
> > > I have added the code for the stored procedure and function and also
the
> > > tables for which I have been working on below and also the commands I
> use
> > in
> > > SQL Plus to prove that the actual function and stored procedure work.
> This
> > > is causing me much woe.
> > >
> > >
> > > Create DEPT table and insert some rows
> > >
> > > CREATE TABLE DEPT
> > > (DEPTNO NUMBER(2,0) NOT NULL,
> > > DNAME VARCHAR2(14) NULL,
> > > LOC VARCHAR2(13) NULL,
> > > PRIMARY KEY (DEPTNO)
> > > );
> > >
> > > INSERT INTO Dept VALUES(11,'Sales','Texas');
> > > INSERT INTO Dept VALUES(22,'Accounting','Washington');
> > > INSERT INTO Dept VALUES(33,'Finance','Maine');
> > >
> > > Create EMP table and insert some rows
> > >
> > > CREATE TABLE EMP
> > > (EMPNO NUMBER(4,0) NOT NULL,
> > > ENAME VARCHAR2(10) NULL,
> > > JOB VARCHAR2(9) NULL,
> > > MGR NUMBER(4,0) NULL,
> > > SAL NUMBER(7,2) NULL,
> > > COMM NUMBER(7,2) NULL,
> > > DEPTNO NUMBER(2,0) NULL,
> > > FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
> > > PRIMARY KEY (EMPNO)
> > > );
> > >
> > > INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
> > > INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
> > > INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
> > >
> > > Create package for Function example
> > >
> > > create or replace package packperson
> > > as
> > > type cursorType is ref cursor;
> > > end;
> > > /
> > >
> > > Create Function for Function example
> > >
> > > create or replace function sp_ListEmp (n_EMPNO NUMBER) return
> > > packperson.cursortype
> > > as
> > > l_cursor packperson.cursorType;
> > > begin
> > > open l_cursor for select ename as NAME, empno as NUM from emp
where
> > > empno = n_EMPNO order by ename;
> > >
> > > return l_cursor;
> > > end;
> > >
> > > From SQL Plus I call this by
> > >
> > > SQL> variable c refcursor
> > > SQL> exec :c := sp_ListEmp(123)
> > > SQL> print c
> > >
> > >
> > >
> > > Create package for sp example
> > >
> > > CREATE OR REPLACE PACKAGE curspkg_join AS
> > > TYPE t_cursor IS REF CURSOR ;
> > > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > > t_cursor);
> > > END curspkg_join;
> > > /
> > >
> > > Create package body for sp example
> > >
> > > CREATE OR REPLACE PACKAGE BODY curspkg_join AS
> > > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > > t_cursor)
> > > IS
> > > v_cursor t_cursor;
> > > BEGIN
> > > IF n_EMPNO <> 0
> > > THEN
> > > OPEN v_cursor FOR
> > > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > > FROM EMP, DEPT
> > > WHERE EMP.DEPTNO = DEPT.DEPTNO
> > > AND EMP.EMPNO = n_EMPNO;
> > >
> > > ELSE
> > > OPEN v_cursor FOR
> > > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > > FROM EMP, DEPT
> > > WHERE EMP.DEPTNO = DEPT.DEPTNO;
> > >
> > > END IF;
> > > io_cursor := v_cursor;
> > > END open_join_cursor1;
> > > END curspkg_join;
> > > /
> > >
> > > From SQL Plus I call this by
> > >
> > > SQL> variable c refcursor
> > > SQL>exec curspkg_join.open_join_cursor1(123,:c)
> > > SQL>print :c
> > >
> > >
> >
> >
>|||Thanks - got it working now, your help is much appreciated.
"Robert Bruckner [MSFT]" wrote:
> Make sure you use the generic text-based query designer with 2 panes, rather
> than the visual query designer with 4 panes.
> Regarding the OUT cursor - don't reference it, just omit that parameter.
> A better practice for stored procedures is to set the command type of the
> query to StoredProcedure instead of Text. In that case, the query text is
> just the name of the stored procedure "test_package.get_customers". The
> parameters will automatically be determined by report designer on clicking
> on the refresh fields icon.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Joe" <booksnore2@.netscape.net> wrote in message
> news:uZsHcq%23iEHA.384@.TK2MSFTNGP10.phx.gbl...
> > Thank you for your help,
> > I'm still unsure of the syntax that I should use in Reporting Services
> when
> > calling the sp. Where/how do I reference the out ref cursor? Example I
> call
> > the procedure using -
> >
> > { call test_package.get_customers(?, ) }
> >
> > I get the following error 'The data extension Oracle does not support
> > unnamed parameters. Use named parameters instead.'
> > So my question is how do I reference the out ref cursor in Reporting
> > services?
> >
> >
> >
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:e1$dlvyiEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > The cursor in the stored procedure has to be an OUT REF cursor rather
> than
> > a
> > > IN OUT cursor.
> > > Also make sure you use the managed Oracle provider (i.e. "Oracle" in the
> > > data source dialog) and not OleDB.
> > >
> > > You might also want to check this previous posting for further
> information
> > > and a sample:
> > >
> >
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=049fe955-cec8-4a79-a5e5-a9c02873e19d&sloc=en-us
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > "Joe" <booksnore2@.netscape.net> wrote in message
> > > news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > > > I've set up a shared datasource to oracle with the option of prompting
> > for
> > > > credientals. The report works with just static SQL in the Query
> > designer.
> > > I
> > > > have been trying for a number of days now to use a call to an oracle
> > > stored
> > > > procedure or function as my report data set without success.
> > > > The function called sp_ListEmp has one parameter of type number. To
> call
> > > the
> > > > function I have tried adding {call sp_ListEmp(?)} into the Query
> > designer
> > > > and setting the command type to Text. I have added the parameter ? and
> > > made
> > > > that equal to a Report parameter EMPNO of Integer type that I have
> added
> > > to
> > > > the report. So I have in the parameter tab of the Dataset
> > > > ?=Parameters!EMPNO.Value. I recieve the following error message 'An
> > error
> > > > occurred while executing the query. ORA-01036: illegal variable
> > > > name/number.'
> > > >
> > > > For the stored procedure curspkg_join.open_join_cursor1 I have not
> even
> > > been
> > > > able to call the procedure with recieving an error for the second
> > > parameter
> > > > which is of type refcursor. Does anyone know if it is possible to call
> > > > Oracle stored procedures and pass parameters to them from reporting
> > > > services?
> > > >
> > > > I have added the code for the stored procedure and function and also
> the
> > > > tables for which I have been working on below and also the commands I
> > use
> > > in
> > > > SQL Plus to prove that the actual function and stored procedure work.
> > This
> > > > is causing me much woe.
> > > >
> > > >
> > > > Create DEPT table and insert some rows
> > > >
> > > > CREATE TABLE DEPT
> > > > (DEPTNO NUMBER(2,0) NOT NULL,
> > > > DNAME VARCHAR2(14) NULL,
> > > > LOC VARCHAR2(13) NULL,
> > > > PRIMARY KEY (DEPTNO)
> > > > );
> > > >
> > > > INSERT INTO Dept VALUES(11,'Sales','Texas');
> > > > INSERT INTO Dept VALUES(22,'Accounting','Washington');
> > > > INSERT INTO Dept VALUES(33,'Finance','Maine');
> > > >
> > > > Create EMP table and insert some rows
> > > >
> > > > CREATE TABLE EMP
> > > > (EMPNO NUMBER(4,0) NOT NULL,
> > > > ENAME VARCHAR2(10) NULL,
> > > > JOB VARCHAR2(9) NULL,
> > > > MGR NUMBER(4,0) NULL,
> > > > SAL NUMBER(7,2) NULL,
> > > > COMM NUMBER(7,2) NULL,
> > > > DEPTNO NUMBER(2,0) NULL,
> > > > FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
> > > > PRIMARY KEY (EMPNO)
> > > > );
> > > >
> > > > INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
> > > > INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
> > > > INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
> > > >
> > > > Create package for Function example
> > > >
> > > > create or replace package packperson
> > > > as
> > > > type cursorType is ref cursor;
> > > > end;
> > > > /
> > > >
> > > > Create Function for Function example
> > > >
> > > > create or replace function sp_ListEmp (n_EMPNO NUMBER) return
> > > > packperson.cursortype
> > > > as
> > > > l_cursor packperson.cursorType;
> > > > begin
> > > > open l_cursor for select ename as NAME, empno as NUM from emp
> where
> > > > empno = n_EMPNO order by ename;
> > > >
> > > > return l_cursor;
> > > > end;
> > > >
> > > > From SQL Plus I call this by
> > > >
> > > > SQL> variable c refcursor
> > > > SQL> exec :c := sp_ListEmp(123)
> > > > SQL> print c
> > > >
> > > >
> > > >
> > > > Create package for sp example
> > > >
> > > > CREATE OR REPLACE PACKAGE curspkg_join AS
> > > > TYPE t_cursor IS REF CURSOR ;
> > > > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > > > t_cursor);
> > > > END curspkg_join;
> > > > /
> > > >
> > > > Create package body for sp example
> > > >
> > > > CREATE OR REPLACE PACKAGE BODY curspkg_join AS
> > > > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > > > t_cursor)
> > > > IS
> > > > v_cursor t_cursor;
> > > > BEGIN
> > > > IF n_EMPNO <> 0
> > > > THEN
> > > > OPEN v_cursor FOR
> > > > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > > > FROM EMP, DEPT
> > > > WHERE EMP.DEPTNO = DEPT.DEPTNO
> > > > AND EMP.EMPNO = n_EMPNO;
> > > >
> > > > ELSE
> > > > OPEN v_cursor FOR
> > > > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > > > FROM EMP, DEPT
> > > > WHERE EMP.DEPTNO = DEPT.DEPTNO;
> > > >
> > > > END IF;
> > > > io_cursor := v_cursor;
> > > > END open_join_cursor1;
> > > > END curspkg_join;
> > > > /
> > > >
> > > > From SQL Plus I call this by
> > > >
> > > > SQL> variable c refcursor
> > > > SQL>exec curspkg_join.open_join_cursor1(123,:c)
> > > > SQL>print :c
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Is it possible to access query designer through an interface?

Is it possible to access query designer through an interface like sqldmo?
Thanks
KurtMay be using SQL-NS (SQL Namespace API). It let you to invoke the create vie
w
wizrd used in enterprise manager.
Example:
Dim NSO As SQLNS.SQLNamespaceObject
...
NSO.ExecuteCommandByID (SQLNS_CmdID_WIZARD_VIEW)
...
http://www.microsoft.com/resources/... />
c3561.mspx
AMB
"Kurt" wrote:

> Is it possible to access query designer through an interface like sqldmo?
> Thanks
> Kurt|||No that doesn't work.
"Alejandro Mesa" wrote:
> May be using SQL-NS (SQL Namespace API). It let you to invoke the create v
iew
> wizrd used in enterprise manager.
>
> Example:
> Dim NSO As SQLNS.SQLNamespaceObject
> ...
> NSO.ExecuteCommandByID (SQLNS_CmdID_WIZARD_VIEW)
> ...
> http://www.microsoft.com/resources/...>
0/c3561.mspx
>
> AMB
> "Kurt" wrote:
>

Is it possiable conversion

Hi

i heared that Oracle can convert from number to word thru query i
remember thru (jsp format) .Is it possiable in sqlserver

ThanksHi

Although I have not done this myslf, you may want to look at either:
Creating an RTF document
Creating a txt document and a word macro to format and save it
Using a COM object (called from sp_AO* procedures)
Using XML and a word template that picks it up.
Sucking data from SQL Server into word

John

"Raghuraman" <raghuraman_ace@.rediffmail.com> wrote in message
news:66c7bef8.0311140440.4738e50d@.posting.google.c om...
> Hi
> i heared that Oracle can convert from number to word thru query i
> remember thru (jsp format) .Is it possiable in sqlserver
>
>
> Thanks

Is it posiable to close the screen

Hai

Is it possiable to close the current connection screen in the query
analyser , not to close the query analyser.

With Thanks
RaghuControl+F4 or click the window close button.

--
David Portas
SQL Server MVP
--

"Raghuraman" <raghuraman_ace@.rediffmail.com> wrote in message
news:66c7bef8.0402150118.44139866@.posting.google.c om...
> Hai
> Is it possiable to close the current connection screen in the query
> analyser , not to close the query analyser.
> With Thanks
> Raghu|||
Dear Portas

Thanks for your suggestions. Actually i meant that query thru code .

Eg

The shutdown command(sql server7.0) removes the network connection from
sqlserver.Like wise is there any command to close the current screen of
the query analyser.

With thanks
Raghu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||No. Transact SQL isn't an application development language - it doesn't
control the client GUI at all.

Use another language to execute your SQL or, if you want to run some batched
process without a GUI, run the OSQL utility from the OS command line.

--
David Portas
SQL Server MVP
--

Is it a SQL Server 2000 bug

Hi, I'm currently using SQL Server 2000 SP3.
I'm facing some problems which make me think of a SQL Server BUG.

When executing a query, it seems that SQL Server tries to convert data
even if not in the resultset -> This leads to SQL Server error.

Here is a very easy sample to reproduce it :

SET NOCOUNT ON
create table tempdb..test ( coldate varchar(30) )

insert into tempdb..test values ( '1900/01/01 00:00:00' )
insert into tempdb..test values ( '2005/01/01 00:00:00' )
insert into tempdb..test values ( 'Invalid date' )

select * from tempdb..test
where isdate( coldate ) = 1
and convert( datetime, coldate ) > GETDATE()

SQL Server output is the following :

Server: Msg 241, Level 16, State 1, Line 10
Syntax error converting datetime from character string.

SQL Server considers as an error the 'Invalid date' even if a filter
ISDATE = 1 is applied...
I guess that I am not able to determine the order in which filters are
applied as it is SQL Server optimizer job... However, this should lead
to an error only if conversion fails on a line of the resultset after
application of all other filters...

What am I doing wrong ?

Thanks
PatrickNot a bug. The execution order is determined by the query optimizer.
There is no reason to suppose that the ISDATE expression will always
execute first.

What are you doing wrong? Firstly, you are writing queries against
dates stored as strings. If at all possible you should convert the
dates to use a proper DATETIME column. If you really cannot do that
then you should be able to rewrite your query using a derived table.

The second thing wrong here is that you are relying on an implicit
conversion from a non-standard date format. Those conversions are
sensitive to local server and connection settings so avoid them. Try
the following:

SELECT coldate
FROM
(SELECT REPLACE(REPLACE(coldate,'/','-'),' ','T') AS coldate
FROM tempdb..test
WHERE ISDATE(REPLACE(REPLACE(coldate,'/','-'),' ','T'))=1) AS T
WHERE CAST(coldate AS DATETIME) > CURRENT_TIMESTAMP

--
David Portas
SQL Server MVP
--|||Thanks for the answer.
However, I thought that the optimizer was designed only to optimize the
query but resultset was independant of the order of execution of
filters.
Here, it appears that depending on the order of application of filters,
result is not the same...
I guess that this is a special case due to the abuse of conversion
use...

Thanks
Patrick

*** Sent via Developersdex http://www.developersdex.com ***|||Hi, Patrick

For details about this problem and possible solutions, see this article
by Itzik Ben-Gan, SQL Server MVP:
http://www.windowsitpro.com/Windows...148/pg/1/1.html

Razvan|||No, an SQL Statement is a specification of the result, and
theoretically, it is created "all at once". It is not good enough that
you will get correct results if the query is executed "left to right,
top to bottom", because that is not how SQL works.

A solution for your problem could be to place the two related predicates
in a case expression. A case expression always has to be evaluated from
left to right.

select * from tempdb..test
where CASE WHEN isdate( coldate ) = 0 THEN 0
WHEN convert( datetime, coldate ) > GETDATE() THEN 1
ELSE 0 END = 1

Hope this helps,
Gert-Jan

Patrick Fiche wrote:
> Thanks for the answer.
> However, I thought that the optimizer was designed only to optimize the
> query but resultset was independant of the order of execution of
> filters.
> Here, it appears that depending on the order of application of filters,
> result is not the same...
> I guess that this is a special case due to the abuse of conversion
> use...
> Thanks
> Patrick
> *** Sent via Developersdex http://www.developersdex.com ***|||Here is another way:

select * from tempdb..test where case when isdate( coldate ) = 1
then convert( datetime, coldate ) else getdate() end > getdate()

Razvan|||Thanks all for your help.

Is it a complex sql query?

Hello

I am using stored procedure with sql 2005 (with Visual studio 2005)

I have two tables .. TABLE1 And TABLE2

>From TABLE1 i need to retrive the OrderID's of the 4 most top rows. so
i did:
SELECT TOP 4 OrderID FROM TABLE1 order by OrderID desc

Now what i am trying to do is take the 4 row results (4 OrderID's) i
got from
TABLE1 and check if the 4 rows (4 OrderID's) exist in TABLE2 for a
specific
userID i get by INPUT varible (@.UserId)..

What i want to return is only which OrderID'S existed in TABLE2 for the

specific user.

If only 2 OrderID'S i retrived from TABLE1 exist in TABLE2 i will
return only 2 OrderID's (so i can do my output in visual studio 2005
using the reader())

I would appreciate this if anyone knows how to do this sql query , is
it possible to do this in 1 query? i want to put it in a stored
procedure.I tried to use this query-
SELECT TOP 4 OrderID FROM TABLE1 WHERE exists (SELECT * From TABLE2
where @.UserId=TABLE2.UserID)

But this query shows me the all 4 OrderID's if it finds the USERID in
TABLE2..

What i want to return is only which OrderID'S existes in TABLE2 for the
specific user.

if i have in TABLE1:
OrderID
1
2
3
4

TABLE2:
OrderID UserId
1 1001
2 1002

I want it to return only "2" if the INPUT Parameter of @.UserID is 1002|||Another example..

if i have in TABLE1:
OrderID
1
2
3
4

TABLE2:
OrderID UserId
1 1001
2 1002
3 1002

I want it to return only "2" and "3" if the INPUT Parameter of @.UserID
is 1002|||Hi

Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data in a usable form

You can use something like:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )

Or (better!)

SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
JOIN TABLE2 t2 ON T1.OrderId = T2.OrderId AND @.UserId=T2.UserId

Check out the topics "Using Joins" and "Join Fundamentals" in books online

John

<stockblaster@.gmail.com> wrote in message
news:1137312110.273304.240990@.g47g2000cwa.googlegr oups.com...
> Another example..
> if i have in TABLE1:
> OrderID
> 1
> 2
> 3
> 4
>
> TABLE2:
> OrderID UserId
> 1 1001
> 2 1002
> 3 1002
>
> I want it to return only "2" and "3" if the INPUT Parameter of @.UserID
> is 1002|||Excellent!

Thanks a lot John, it seems to work just fine.. i used the second
example.|||Hello again

Finally i decieded to use SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )

and modifed it to:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )

(Notice the "NOT")
Because i wanted it to return me the OrderID's (from the top 4 of
course) that does not exist in TABLE2 ..

I couldn't do it with the JOIN thingy even if i changed OrderId <>
T2.OrderId ..|||I tried to find this in the documents on the web ..I couldn't find a
way of how to perform this only for the TOP 4 of TABLE1.

Now what happenes:
stockblas...@.gmail.com
Jan 15, 10:01 am show options

Newsgroups: comp.databases.ms-sqlserver
From: stockblas...@.gmail.com - Find messages by this author
Date: 15 Jan 2006 00:01:50 -0800
Local: Sun, Jan 15 2006 10:01 am
Subject: Re: Is it a complex sql query?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

Another example..

TABLE1:
OrderID
1
2
3
4
5
6
7
8
9
10
TABLE2:
OrderID UserId
1 1001
2 1002
3 1002

It will return me: 4 5 6 7 (the top 4 of what it finds)
i use now:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )

Any ideas?|||Hi

I should have said that TOP without and ORDER BY clause is a bit
meaningless.

SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT EXISTS (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )
ORDER BY t1.OrderID

Will return you all rows OrderIds from Table1 where a row in Table2 does not
exist for that OrderId AND has a UserId of @.UserId. With the ORDER BY means
1, 4, 5 and 6 are returned.

To do this using a JOIN, an OUTER JOIN is required.

SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
LEFT JOIN TABLE2 t2 ON t1.OrderId = t2.OrderId AND @.UserId = t2.UserId
WHERE t2.OrderID IS NULL
ORDER BY t1.OrderID

John

<stockblaster@.gmail.com> wrote in message
news:1137325768.003889.45140@.g47g2000cwa.googlegro ups.com...
> Hello again
> Finally i decieded to use SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> WHERE exists (SELECT * FROM TABLE2 t2
> WHERE @.UserId=T2.UserId
> AND T1.OrderId = T2.OrderId )

> and modifed it to:
> SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> WHERE NOT exists (SELECT * FROM TABLE2 t2
> WHERE @.UserId=T2.UserId
> AND T1.OrderId = T2.OrderId )

> (Notice the "NOT")
> Because i wanted it to return me the OrderID's (from the top 4 of
> course) that does not exist in TABLE2 ..
> I couldn't do it with the JOIN thingy even if i changed OrderId <>
> T2.OrderId ..|||Hello

I am sorry, i didn't explain my self what i wanted to acchive exactly.
For this query:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
LEFT JOIN TABLE2 t2 ON t1.OrderId = t2.OrderId AND @.UserId = t2.UserId
WHERE t2.OrderID IS NULL
ORDER BY t1.OrderID DESC

There is a problem with that..
For example:
Table1:
OrderID
1
2
3
4
5
6
7
8
Table2:
OrderID UserID
6 1001
7 1001
3 1002
4 1002
the result will be:
for user 1001
8
5
4
3

I only need to get 8 and 5 which are the two orderID's the user didn't
have from the top 4 in table 1 ..

can't figure that out :(|||On 15 Jan 2006 10:09:18 -0800, stockblaster@.gmail.com wrote:

>Hello
>I am sorry, i didn't explain my self what i wanted to acchive exactly.
(snip)

Hi Stockblaster,

That's exactly the reason why John suggested you to read the information
at www.aspfaq.com/5006 in his first post to you - posting CREATE TABLE
and INSERT statements and expected output is a much better way to
explain your needs than pure narrative.

If I understand your requirements correctly, then maybe something like
this will work:

SELECT t1.OrderId
FROM (SELECT TOP 4 OrderID
FROM Table1
ORDER BY OrderID DESC) AS t1
LEFT JOIN Table2 AS t2
ON t2.OrderId = t1.OrderId
AND t2.UserId = @.UserId
WHERE t2.OrderID IS NULL

(untested - see www.aspfafq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP|||Hello Hugo..

Very nice! i believe this 1 did the work..

Thanks a lot.. this 1 was stiff.

Is there any good book you can recommend me for sql 2005 (with SQL
Server Management Studio) ... How to upload to a shared web hosting,
when to use relationships, some sql querys examples? all the basics.|||Hi

I am not sure if my interpretation is the same as Hugos!
If all 4 rows returned are below the maximum what should happen?

SELECT TOP 4 t1a.OrderID
FROM TABLE1 t1a
LEFT JOIN TABLE2 t2a ON t1.OrderId = t2a.OrderId AND @.UserId =
t2a.UserId
WHERE t2.OrderID IS NULL
AND t1a.OrderID > ( SELECT MAX(t1b.OrderID) FROM TABLE1 t1b
JOIN TABLE2 t2b ON t1b.OrderId = t2b.OrderId AND @.UserId = t2b.UserId )

ORDER BY t1a.OrderID DESC

John

stockblaster@.gmail.com wrote:
> Hello
> I am sorry, i didn't explain my self what i wanted to acchive exactly.
> For this query:
> SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> LEFT JOIN TABLE2 t2 ON t1.OrderId = t2.OrderId AND @.UserId = t2.UserId
> WHERE t2.OrderID IS NULL
> ORDER BY t1.OrderID DESC
>
> There is a problem with that..
> For example:
> Table1:
> OrderID
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> Table2:
> OrderID UserID
> 6 1001
> 7 1001
> 3 1002
> 4 1002
> the result will be:
> for user 1001
> 8
> 5
> 4
> 3
>
> I only need to get 8 and 5 which are the two orderID's the user didn't
> have from the top 4 in table 1 ..
> can't figure that out :(|||Hi

I don't think you will get a single books to cover all these topics,
and you will have to be careful of books based on the pre-release
versions. You may want to check out THe Microsoft SQL Server 2005
Administrator's Pocket Consultant ISDN 0735621071 for configuration
information, and there is always books online. Also check out SQL
Server magazine which has many articles that will be benificial
http://www.windowsitpro.com/SQLServer/

John|||On 15 Jan 2006 15:27:13 -0800, stockblaster@.gmail.com wrote:

>Hello Hugo..
>Very nice! i believe this 1 did the work..
>Thanks a lot.. this 1 was stiff.
>Is there any good book you can recommend me for sql 2005 (with SQL
>Server Management Studio) ... How to upload to a shared web hosting,
>when to use relationships, some sql querys examples? all the basics.

Hi Stockblaster,

I'm sorry, I can't help you here.

Personally, I'm going to wait for Inside SQL Server 2005, that Kalen
Delaney is (hopefully) working on right now. However, the "Inside..."
series are "how does it work" kind of books; you seem to be seeking the
"how do I operate it" kind of books.

--
Hugo Kornelis, SQL Server MVP|||Hello John.

I am not sure i understand, do you mean if table1 contains only two
records? so the top 4 will not work?|||Hi

Sorry for the delayed reply, this one slipped through the net.

My question was related to

There is a problem with that..
For example:
Table1:
OrderID
1
2
3
4
5
6
7
8
Table2:
OrderID UserID
6 1001
7 1001
3 1002
4 1002
the result will be:
for user 1001
8
5
4
3

Do you actually want 3,4,5 as this is less than the maximum for 1001 which
is already 7?

John
<stockblaster@.gmail.com> wrote in message
news:1137449155.173528.136550@.g14g2000cwa.googlegr oups.com...
> Hello John.
> I am not sure i understand, do you mean if table1 contains only two
> records? so the top 4 will not work?

Is Full Text Struggling...

H there,
We have a query that is taking too long to run, which uses Full Text
(MSSQL2000).
The query below, when using this clause takes between 11 seconds and 10
minutes! QA thnks that the full text search will cost 70% of the query cost.
CONTAINS( Article_text, '("Food" AND "Supermarkets") OR ("CITIZEN CARD") OR
("CJD") OR ("E Coli") OR ("E-Coli") OR ("Food Additives") OR ("Food Safety")
OR ("Genetically Modified Foods") OR ("Kwik Save") OR ("Proof of age card")
OR ("Somerfield") OR ("Supermarkets") OR ("Wine Reviews") AND NOT ("Ahold")
AND NOT ("Beth Israel") AND NOT ("European equity preview") AND NOT
("European stocks may decline") AND NOT ("European stocks may rise") AND NOT
("mediaplex") AND NOT ("UK Stocks Factors")'))
The query below, when using this clause, takes only between 0.02 secs and 2
secs. QA thnks that the full text search will cost 50% of the query cost.
CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
"culture") OR ("alcopops" AND "designated driver initiative") OR ("alcopops"
AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
"price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
driving")')
Why is the second query instant and the first taking ages?
Sometimes, the longer queries such as the first one here can take *much*
longer to run (like, 5 minutes). We're hoping that we can get even the
queries with more expressions to run inside a few seconds. In fact, they did
when we had < 400,000 rows.
Is it possible that our full text indexing is just set up wrong, or that our
hardware isn't sufficient?
Some additional facts that may help...
The table (and FT index) only have 800,000 rows
We run 116 of these queries in a row, directly after each other.
We've cleared out all stop words, since we want to index on anything.
The server has 1GB RAM, single P4 processor, 8GB free space across 2 raid
disks.
Whilst the queries are running, I'm not seeing massive memory use.
Any help much appreciated. Please let me know if you need more info.
Tobes
I would suspect its all the search arguments and Boolean logic you have
which is causing the problems especially the AND NOTs.
Note that this
CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
"culture") OR ("alcopops" AND "designated driver initiative") OR
("alcopops"
AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
"price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
driving")')
is equivalent to the simpler
CONTAINS( Article_text, '"alcopops" AND ("advertising" OR "culture" OR
"designated driver initiative" OR "drink driving" OR "legislation" OR
"price" OR "pricing" OR "underage driving")')
Revisting the AND NOTs, basically the way this is processed is all matches
are returned for the first part
("Food" AND "Supermarkets") OR ("CITIZEN CARD") OR ("CJD") OR ("E Coli") OR
("E-Coli") OR ("Food Additives") OR ("Food Safety") OR ("Genetically
Modified Foods") OR ("Kwik Save") OR ("Proof of age card") OR
("Somerfield") OR ("Supermarkets") OR ("Wine Reviews")
and then you trim rows which contain
AND NOT ("Ahold") AND NOT ("Beth Israel") AND NOT ("European equity
preview") AND NOT ("European stocks may decline") AND NOT ("European stocks
may rise") AND NOT
("mediaplex") AND NOT ("UK Stocks Factors")'))
This trimming is very expensive.
One thing you might do is sp_fulltext_service 'resource_usage' to 5. This
might help slightly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tobin Harris" <tobin@._do_not_spam_tobinharris.com> wrote in message
news:42af07ca$0$2588$da0feed9@.news.zen.co.uk...
> H there,
> We have a query that is taking too long to run, which uses Full Text
> (MSSQL2000).
> The query below, when using this clause takes between 11 seconds and 10
> minutes! QA thnks that the full text search will cost 70% of the query
cost.
> CONTAINS( Article_text, '("Food" AND "Supermarkets") OR ("CITIZEN CARD")
OR
> ("CJD") OR ("E Coli") OR ("E-Coli") OR ("Food Additives") OR ("Food
Safety")
> OR ("Genetically Modified Foods") OR ("Kwik Save") OR ("Proof of age
card")
> OR ("Somerfield") OR ("Supermarkets") OR ("Wine Reviews") AND NOT
("Ahold")
> AND NOT ("Beth Israel") AND NOT ("European equity preview") AND NOT
> ("European stocks may decline") AND NOT ("European stocks may rise") AND
NOT
> ("mediaplex") AND NOT ("UK Stocks Factors")'))
> The query below, when using this clause, takes only between 0.02 secs and
2
> secs. QA thnks that the full text search will cost 50% of the query cost.
> CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
> "culture") OR ("alcopops" AND "designated driver initiative") OR
("alcopops"
> AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
> "price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
> driving")')
> Why is the second query instant and the first taking ages?
> Sometimes, the longer queries such as the first one here can take *much*
> longer to run (like, 5 minutes). We're hoping that we can get even the
> queries with more expressions to run inside a few seconds. In fact, they
did
> when we had < 400,000 rows.
> Is it possible that our full text indexing is just set up wrong, or that
our
> hardware isn't sufficient?
> Some additional facts that may help...
> The table (and FT index) only have 800,000 rows
> We run 116 of these queries in a row, directly after each other.
> We've cleared out all stop words, since we want to index on anything.
> The server has 1GB RAM, single P4 processor, 8GB free space across 2 raid
> disks.
> Whilst the queries are running, I'm not seeing massive memory use.
> Any help much appreciated. Please let me know if you need more info.
> Tobes
>
>
|||Hi Hilary,
Thank you for the reply. Yes, the queries run significantly quicker without
the NOTs. I had tried the other boolen expressions in a more compact form,
but that made little difference unfortunately. I guess the full text search
engine may do it's own optimising to clean up our verbose queries!
In two weeks we'll be throwing more hardware at the program (doubling ram,
increasing disk capacity, and introducing dual Xeon processors), so
hopefully that will make the situation better.
Our main problem is that we want scalability. At the moment we have 116
"projects", each with their own queries that run one by one. These are
taking hours to run (some queries quick, some looooong!). Do you think we
may benefit from running more than one query in parrallel? For example, have
two processes executing 58 queries each?
Thanks again for your help.
Tobes
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ex7e8bQcFHA.720@.TK2MSFTNGP15.phx.gbl...
>I would suspect its all the search arguments and Boolean logic you have
> which is causing the problems especially the AND NOTs.
> Note that this
> CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
> "culture") OR ("alcopops" AND "designated driver initiative") OR
> ("alcopops"
> AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
> "price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
> driving")')
> is equivalent to the simpler
> CONTAINS( Article_text, '"alcopops" AND ("advertising" OR "culture" OR
> "designated driver initiative" OR "drink driving" OR "legislation" OR
> "price" OR "pricing" OR "underage driving")')
> Revisting the AND NOTs, basically the way this is processed is all matches
> are returned for the first part
> ("Food" AND "Supermarkets") OR ("CITIZEN CARD") OR ("CJD") OR ("E Coli")
> OR
> ("E-Coli") OR ("Food Additives") OR ("Food Safety") OR ("Genetically
> Modified Foods") OR ("Kwik Save") OR ("Proof of age card") OR
> ("Somerfield") OR ("Supermarkets") OR ("Wine Reviews")
> and then you trim rows which contain
> AND NOT ("Ahold") AND NOT ("Beth Israel") AND NOT ("European equity
> preview") AND NOT ("European stocks may decline") AND NOT ("European
> stocks
> may rise") AND NOT
> ("mediaplex") AND NOT ("UK Stocks Factors")'))
> This trimming is very expensive.
> One thing you might do is sp_fulltext_service 'resource_usage' to 5. This
> might help slightly.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Tobin Harris" <tobin@._do_not_spam_tobinharris.com> wrote in message
> news:42af07ca$0$2588$da0feed9@.news.zen.co.uk...
> cost.
> OR
> Safety")
> card")
> ("Ahold")
> NOT
> 2
> ("alcopops"
> did
> our
>

Is editing possible in QA 2000 debugger?

Hi,

Just started using the debugger in Query Analyser in SQL Server 2000.

Is there a way I can edit the code? It's nice to be able to step through the code,
but I don't seem to be able to edit it.

Cheers!

Eric.

use the alter statement

you can use it with Sps, table, view, udf etc

here some example

http://doc.ddart.net/mssql/sql70/aa-az_5.htm

you can also right click on the object in the object browser

and click on script object to new window as and then

clcik on alter

|||You can't edit in debug mode, as Joey says you need to issue an Alter proc command from the editor