Link Server from SQL2005 to SQL2000

I have the follwoing scenario, where the query returns an error, when we tried to upgrade oru production SQL2000 enviroment to SQL2005.

SQL2000 Env:

Microsoft SQL Server Developer Edition

Microsoft Windows NT 5.2 (3790)

8.00.2175 SP4

SQL2005 Env:

Microsoft SQL Server Developer Edition

Microsoft Windows NT 5.2 (3790)

9.00.3054.00

Notice the removal of the two RTRIMs.

Any help welcome

Gertus

Current Connection was on the SQL2005 machine:

Remote Link Server SQL2000 SP4

Working:

SELECT * FROM Entity -- on SQL2005

WHERE

(EntityRef) + Source NOT IN

(SELECT (ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY) --this is on sql2000

AND Source = 'EU'

Not-working:

SELECT * FROM Entity-- on SQL2005

WHERE

RTRIM(EntityRef) + Source NOT IN

(SELECT RTRIM(ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY)

AND Source = 'EU'

Error :: >>

OLE DB provider "SQLNCLI" for linked server "MRIEU" returned message "Cannot create new connection because in manual or distributed transaction mode.".

Msg 7320, Level 16, State 2, Line 1

Cannot execute the query "SELECT TOP 1 1 FROM "MRIOB"."dbo"."ENTITY" "Tbl1005"" against OLE DB provider "SQLNCLI" for linked server "MRIEU".

Did you try searching the MS knowledge base?

http://support.microsoft.com/kb/272358/en-us

|||

Sorry that is not the problem, I am runnign this query in sql storedproc, it this is an error on the link server code internal of Microsoft.

Can any Microsoft person help here please?

Regards

Gert

Link Server doesn't work except through query analyzer: MSSQL freezing / timing out

Environment:
Server1 (Local)
OS Windows 2000 Server
SQL Server 2000
Server2 (Remote)
OS Windows 2003 Server
SQL Server 2000
(Both with most recent service packs)
Using Enterprise Manager, we have set up the Link Server (LINK_A) in
the Local Server 1 to connect to Server 2.
The SQL we need to run is the following:
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx;
When we run this from the Query Analyzer, it completes with no problems
in a few seconds.
Our problem:
When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"
Dim Sql, obj_Conn
Set obj_Conn = CreateObject("ADODB.Connection")
obj_Conn.Open XXXX
obj_Conn.BeginTrans
str_Sql = "INSERT INTO table1("
str_Sql = str_Sql & "column1"
str_Sql = str_Sql & ", column2"
str_Sql = str_Sql & ")"
str_Sql = str_Sql & " SELECT Ａ.column1"
str_Sql = str_Sql & ", A.column2"
str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
str_Sql = str_Sql & " WHERE A.column1 > 0"
str_Sql = str_Sql & ";"
obj_Conn.Execute str_Sql
When we make a Stored Procedure and run the following SQL, it freezes.
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
We've also tried the following with the same results;
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
WHERE A.column1 > xxxx
The same thing happens when we try to run the "SELECT" by itself.
SELECT TOP 1 @.test=A.column1
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
ORDER BY A.column1
What is going wrong here, and how do we need to change this so that it
runs without timing out or freezing?
Hi
Try running SQL Server Profiler to see what is going on when you run it from
QA and by using DTS? Do you have trigger/s defined on the table?
How big is your load? Does the database set up iwth FULL recovery mode?
"SQL Server Questions" <farkerku@.gmail.com> wrote in message
news:1168149322.774824.251680@.51g2000cwl.googlegro ups.com...
> Environment:
> Server1 (Local)
> OS Windows 2000 Server
> SQL Server 2000
> Server2 (Remote)
> OS Windows 2003 Server
> SQL Server 2000
> (Both with most recent service packs)
> Using Enterprise Manager, we have set up the Link Server (LINK_A) in
> the Local Server 1 to connect to Server 2.
> The SQL we need to run is the following:
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx;
> When we run this from the Query Analyzer, it completes with no problems
> in a few seconds.
> Our problem:
> When we add the DTS Package as the ActiveX Script (VB Script) to the
> Local Package, it times out at "obj_Conn.Execute str_Sql"
> Dim Sql, obj_Conn
> Set obj_Conn = CreateObject("ADODB.Connection")
> obj_Conn.Open XXXX
> obj_Conn.BeginTrans
> str_Sql = "INSERT INTO table1("
> str_Sql = str_Sql & "column1"
> str_Sql = str_Sql & ", column2"
> str_Sql = str_Sql & ")"
> str_Sql = str_Sql & " SELECT Ａ.column1"
> str_Sql = str_Sql & ", A.column2"
> str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
> str_Sql = str_Sql & " WHERE A.column1 > 0"
> str_Sql = str_Sql & ";"
> obj_Conn.Execute str_Sql
> ----
> When we make a Stored Procedure and run the following SQL, it freezes.
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> We've also tried the following with the same results;
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
> WHERE A.column1 > xxxx
>
> The same thing happens when we try to run the "SELECT" by itself.
> SELECT TOP 1 @.test=A.column1
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> ORDER BY A.column1
> What is going wrong here, and how do we need to change this so that it
> runs without timing out or freezing?
>

Link Server doesn't work except through query analyzer: MSSQL freezing / timing out

Environment:
Server1 (Local)
OS Windows 2000 Server
SQL Server 2000
Server2 (Remote)
OS Windows 2003 Server
SQL Server 2000
(Both with most recent service packs)
Using Enterprise Manager, we have set up the Link Server (LINK_A) in
the Local Server 1 to connect to Server 2.
The SQL we need to run is the following:
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx;
When we run this from the Query Analyzer, it completes with no problems
in a few seconds.
Our problem:
When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"
Dim Sql, obj_Conn
Set obj_Conn = CreateObject("ADODB.Connection")
obj_Conn.Open XXXX
obj_Conn.BeginTrans
str_Sql = "INSERT INTO table1("
str_Sql = str_Sql & "column1"
str_Sql = str_Sql & ", column2"
str_Sql = str_Sql & ")"
str_Sql = str_Sql & " SELECT Ａ.column1"
str_Sql = str_Sql & ", A.column2"
str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
str_Sql = str_Sql & " WHERE A.column1 > 0"
str_Sql = str_Sql & ";"
obj_Conn.Execute str_Sql
----
When we make a Stored Procedure and run the following SQL, it freezes.
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
We've also tried the following with the same results;
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
WHERE A.column1 > xxxx
The same thing happens when we try to run the "SELECT" by itself.
SELECT TOP 1 @.test=A.column1
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
ORDER BY A.column1
What is going wrong here, and how do we need to change this so that it
runs without timing out or freezing?Hi
Try running SQL Server Profiler to see what is going on when you run it from
QA and by using DTS? Do you have trigger/s defined on the table?
How big is your load? Does the database set up iwth FULL recovery mode?
"SQL Server Questions" <farkerku@.gmail.com> wrote in message
news:1168149322.774824.251680@.51g2000cwl.googlegroups.com...
> Environment:
> Server1 (Local)
> OS Windows 2000 Server
> SQL Server 2000
> Server2 (Remote)
> OS Windows 2003 Server
> SQL Server 2000
> (Both with most recent service packs)
> Using Enterprise Manager, we have set up the Link Server (LINK_A) in
> the Local Server 1 to connect to Server 2.
> The SQL we need to run is the following:
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx;
> When we run this from the Query Analyzer, it completes with no problems
> in a few seconds.
> Our problem:
> When we add the DTS Package as the ActiveX Script (VB Script) to the
> Local Package, it times out at "obj_Conn.Execute str_Sql"
> Dim Sql, obj_Conn
> Set obj_Conn = CreateObject("ADODB.Connection")
> obj_Conn.Open XXXX
> obj_Conn.BeginTrans
> str_Sql = "INSERT INTO table1("
> str_Sql = str_Sql & "column1"
> str_Sql = str_Sql & ", column2"
> str_Sql = str_Sql & ")"
> str_Sql = str_Sql & " SELECT Ａ.column1"
> str_Sql = str_Sql & ", A.column2"
> str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
> str_Sql = str_Sql & " WHERE A.column1 > 0"
> str_Sql = str_Sql & ";"
> obj_Conn.Execute str_Sql
> ----
> When we make a Stored Procedure and run the following SQL, it freezes.
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> We've also tried the following with the same results;
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
> WHERE A.column1 > xxxx
>
> The same thing happens when we try to run the "SELECT" by itself.
> SELECT TOP 1 @.test=A.column1
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> ORDER BY A.column1
> What is going wrong here, and how do we need to change this so that it
> runs without timing out or freezing?
>

Link Server doesn't work except through query analyzer: MSSQL freezing / timing out

Environment:
Server1 (Local)
OS Windows 2000 Server
SQL Server 2000

Server2 (Remote)
OS Windows 2003 Server
SQL Server 2000
(Both with most recent service packs)

Using Enterprise Manager, we have set up the Link Server (LINK_A) in
the Local Server 1 to connect to Server 2.

The SQL we need to run is the following:

INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 xxxx;

When we run this from the Query Analyzer, it completes with no problems
in a few seconds.

Our problem:

When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"

Dim Sql, obj_Conn
Set obj_Conn = CreateObject("ADODB.Connection")
obj_Conn.Open XXXX
obj_Conn.BeginTrans

str_Sql = "INSERT INTO table1("
str_Sql = str_Sql & "column1"
str_Sql = str_Sql & ", column2"
str_Sql = str_Sql & ")"
str_Sql = str_Sql & " SELECT Ａ.column1"
str_Sql = str_Sql & ", A.column2"
str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
str_Sql = str_Sql & " WHERE A.column1 0"
str_Sql = str_Sql & ";"
obj_Conn.Execute str_Sql
------------------
When we make a Stored Procedure and run the following SQL, it freezes.

INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 xxxx

We've also tried the following with the same results;

INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
WHERE A.column1 xxxx

The same thing happens when we try to run the "SELECT" by itself.

SELECT TOP 1 @.test=A.column1
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 xxxx
ORDER BY A.column1

What is going wrong here, and how do we need to change this so that it
runs without timing out or freezing?SQL Server Questions (farkerku@.gmail.com) writes:

Quote:

Originally Posted by

INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 xxxx;
>
When we run this from the Query Analyzer, it completes with no problems
in a few seconds.

And if you slap BEGIN/COMMIT TRANSACTION around it?

Quote:

Originally Posted by

When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"

I believe this is the problem is the increased security with DTC in Windows
2003. We had a problem with this a while back. This was what the support
professional from MS told us:

Let starts with a few troubleshooting steps, first take security MSDTC
configurations at the minimum level. Component Services -My Computer
-Right click proprieties ->MSDTC -Select use local coordinator ->
Security Configuration -Enable Network DTC access, Enable remote
administration, Enable Allow Remote clients, Enable allow inbound and
outbound, Select no authentication required, Enable XA transactions and
verify that NT AUTHORITY\NetworkService is set in DTC Logon account.

Restart DTC service and SQL Server and test if issue persists.

If problem persist please follow this KB article:
http://support.microsoft.com/kb/306843 - How to troubleshoot
MS DTC firewall issues.

I think the step to restart SQL Server is unnecessary.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks!

We've got to this point and are getting a "New transaction cannot
enlist in specified transaction coordinator" error.

Erland Sommarskog wrote:

Quote:

Originally Posted by

SQL Server Questions (farkerku@.gmail.com) writes:

Quote:

Originally Posted by

INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 xxxx;

When we run this from the Query Analyzer, it completes with no problems
in a few seconds.

>
And if you slap BEGIN/COMMIT TRANSACTION around it?
>

Quote:

Originally Posted by

When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"

>
I believe this is the problem is the increased security with DTC in Windows
2003. We had a problem with this a while back. This was what the support
professional from MS told us:
>
Let starts with a few troubleshooting steps, first take security MSDTC
configurations at the minimum level. Component Services -My Computer
-Right click proprieties ->MSDTC -Select use local coordinator ->
Security Configuration -Enable Network DTC access, Enable remote
administration, Enable Allow Remote clients, Enable allow inbound and
outbound, Select no authentication required, Enable XA transactions and
verify that NT AUTHORITY\NetworkService is set in DTC Logon account.
>
Restart DTC service and SQL Server and test if issue persists.
>
If problem persist please follow this KB article:
http://support.microsoft.com/kb/306843 - How to troubleshoot
MS DTC firewall issues.
>
I think the step to restart SQL Server is unnecessary.
>
>
>
>
--
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

Link Server doesn't work except through query analyzer: MSSQL freezing / timing out

Environment:
Server1 (Local)
OS Windows 2000 Server
SQL Server 2000
Server2 (Remote)
OS Windows 2003 Server
SQL Server 2000
(Both with most recent service packs)
Using Enterprise Manager, we have set up the Link Server (LINK_A) in
the Local Server 1 to connect to Server 2.
The SQL we need to run is the following:
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx;
When we run this from the Query Analyzer, it completes with no problems
in a few seconds.
Our problem:
When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"
Dim Sql, obj_Conn
Set obj_Conn = CreateObject("ADODB.Connection")
obj_Conn.Open XXXX
obj_Conn.BeginTrans
str_Sql = "INSERT INTO table1("
str_Sql = str_Sql & "column1"
str_Sql = str_Sql & ", column2"
str_Sql = str_Sql & ")"
str_Sql = str_Sql & " SELECT Ａ.column1"
str_Sql = str_Sql & ", A.column2"
str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
str_Sql = str_Sql & " WHERE A.column1 > 0"
str_Sql = str_Sql & ";"
obj_Conn.Execute str_Sql
----
When we make a Stored Procedure and run the following SQL, it freezes.
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
We've also tried the following with the same results;
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
WHERE A.column1 > xxxx
The same thing happens when we try to run the "SELECT" by itself.
SELECT TOP 1 @.test=A.column1
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
ORDER BY A.column1
What is going wrong here, and how do we need to change this so that it
runs without timing out or freezing?Hi
Try running SQL Server Profiler to see what is going on when you run it from
QA and by using DTS? Do you have trigger/s defined on the table?
How big is your load? Does the database set up iwth FULL recovery mode?
"SQL Server Questions" <farkerku@.gmail.com> wrote in message
news:1168149322.774824.251680@.51g2000cwl.googlegroups.com...
> Environment:
> Server1 (Local)
> OS Windows 2000 Server
> SQL Server 2000
> Server2 (Remote)
> OS Windows 2003 Server
> SQL Server 2000
> (Both with most recent service packs)
> Using Enterprise Manager, we have set up the Link Server (LINK_A) in
> the Local Server 1 to connect to Server 2.
> The SQL we need to run is the following:
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx;
> When we run this from the Query Analyzer, it completes with no problems
> in a few seconds.
> Our problem:
> When we add the DTS Package as the ActiveX Script (VB Script) to the
> Local Package, it times out at "obj_Conn.Execute str_Sql"
> Dim Sql, obj_Conn
> Set obj_Conn = CreateObject("ADODB.Connection")
> obj_Conn.Open XXXX
> obj_Conn.BeginTrans
> str_Sql = "INSERT INTO table1("
> str_Sql = str_Sql & "column1"
> str_Sql = str_Sql & ", column2"
> str_Sql = str_Sql & ")"
> str_Sql = str_Sql & " SELECT Ａ.column1"
> str_Sql = str_Sql & ", A.column2"
> str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
> str_Sql = str_Sql & " WHERE A.column1 > 0"
> str_Sql = str_Sql & ";"
> obj_Conn.Execute str_Sql
> ----
> When we make a Stored Procedure and run the following SQL, it freezes.
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> We've also tried the following with the same results;
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
> WHERE A.column1 > xxxx
>
> The same thing happens when we try to run the "SELECT" by itself.
> SELECT TOP 1 @.test=A.column1
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> ORDER BY A.column1
> What is going wrong here, and how do we need to change this so that it
> runs without timing out or freezing?
>sql

Link Server

Can u have a link server from SQL 7 to SQL 2000. I keep getting login error but I can connect successfully using query analyzer
thx
In the security tab in the linked server select the option: Be made using
this security context and put in the sa account and password. If that works
you can configure a standard SQL login to use.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||that is how I have it but I get 'login failed for sa' and I know the passwd is right

Link Server

Can u have a link server from SQL 7 to SQL 2000. I keep getting login error
but I can connect successfully using query analyzer
thxIn the security tab in the linked server select the option: Be made using
this security context and put in the sa account and password. If that works
you can configure a standard SQL login to use.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||that is how I have it but I get 'login failed for sa' and I know the passwd
is rightsql

Link

Hello. Have a cross-server query that we use a NOLOCK so
nobody complains about locks on their server. ANyway,
this works ok on SQL 7, but running the exact same code on
SQL 2000 I get the error below as though you can't do a
NOLOCK now when using a Linked Server under SQL 2000'
Any ideas on that or a better way to enure that we do as
few locks as possible on that other server we're pulling
data from? Thanks, Bruce
select col1, col2 FROM SERVER.DB.dbo.TABLE WITH (NOLOCK)
Server: Msg 7377, Level 16, State 1, Procedure PROC_NAME
Cannot specify an index or locking hint for a remote data
source.HI,
Create stored procedure on Linked server:
CREATE PROCEDURE Test
AS
select col1, col2 FROM SERVER.DB.dbo.TABLE WITH (NOLOCK)
----
-
Change the select statement to following:
EXECUTE [Linked Server].[Database].[Owner].[Test]
JBandi|||Bruce,
If you create the sp on the foreign system, you would NOT have to use the
4part name in the sp.. Only use the 2-part name in the sp, and use the
4-part name to call it from your primary server.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:102a501c414e2$f34ab360$a001280a@.phx
.gbl...
> Hello. Have a cross-server query that we use a NOLOCK so
> nobody complains about locks on their server. ANyway,
> this works ok on SQL 7, but running the exact same code on
> SQL 2000 I get the error below as though you can't do a
> NOLOCK now when using a Linked Server under SQL 2000'
> Any ideas on that or a better way to enure that we do as
> few locks as possible on that other server we're pulling
> data from? Thanks, Bruce
> select col1, col2 FROM SERVER.DB.dbo.TABLE WITH (NOLOCK)
> Server: Msg 7377, Level 16, State 1, Procedure PROC_NAME
> Cannot specify an index or locking hint for a remote data
> source.|||Thanks. I was actually looking for a way to NOT put any
SP objects in the database we're accessing (political
thing!)... Also, the code wa write with direct join in
these cross-server links. THe NOLOCK option works in SQL
7, but it looks like with SQL 2000 you cannot say NOLOCK
on a remote linked server query?
Is there any other way to directly access data in the
other database without SP's ON that other database, so you
cause as few locks as possible on that database?
Thanks, Bruce

>--Original Message--
>Bruce,
>If you create the sp on the foreign system, you would NOT
have to use the
>4part name in the sp.. Only use the 2-part name in the
sp, and use the
>4-part name to call it from your primary server.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> news:102a501c414e2$f34ab360$a001280a@.phx
.gbl...
so
on
data
>
>.
>|||Thanks. Yes, we could do that but there are issue with
anyone putting any objects in "their" database (yea, even
though we can ACCESS their database!?!)... Looking for a
NOLOCK type of situation where we can retrieve from their
database... THanks, Bruce

>--Original Message--
>HI,
>Create stored procedure on Linked server:
>CREATE PROCEDURE Test
>AS
>select col1, col2 FROM SERVER.DB.dbo.TABLE WITH (NOLOCK)
>----
--
>
>Change the select statement to following:
>EXECUTE [Linked Server].[Database].[Owner].[Test]
>JBandi
>.
>|||You can try a pass through query, maybe that would do it..
ie
select * from openquery(linkedservername, select col1, col2 FROM
DB.dbo.TABLE WITH (NOLOCK))
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:14f7401c4158b$d6ebc6b0$a501280a@.phx
.gbl...
> Thanks. I was actually looking for a way to NOT put any
> SP objects in the database we're accessing (political
> thing!)... Also, the code wa write with direct join in
> these cross-server links. THe NOLOCK option works in SQL
> 7, but it looks like with SQL 2000 you cannot say NOLOCK
> on a remote linked server query?
> Is there any other way to directly access data in the
> other database without SP's ON that other database, so you
> cause as few locks as possible on that database?
> Thanks, Bruce
>
> have to use the
> sp, and use the
> Charlotte, NC
> (PASS) and it's
> so
> on
> data

linefeeds in text fields extracted with substring

Im running a query like
select * from MyTable where SUBSTRING(MyColumn,1,300)='searchtext'
MyColumn is th the text type.
The text in MyColumn contains a lot of tabulator characters (TAB),
carrige return (CR) and linefeed (LF) characters.
Sometimes (but not often) when I run the query, even in the Query
Analyser, I get the wrong result. I get too few characters, even though
I know they are in the database. (If I read the field with out the
substring, I can see everything).
The error is consistent - either a record works as it should, or it
fails to.
All answers are very welcome.
Thomas K
select @.@.version
----
----
----
--
-
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
(1 row(s) affected)I think you did not handle the Tab, CR and Linefeed. The records that
dont have these will work otherwise fail
Madhivanan|||Check your 'maximum characters per column' setting in Query Analyzer under
Tools --> Options --> Results. This may be set too small and truncate the
value.
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas Holme" <seiofecco@.hotmail.com> wrote in message
news:ONdDTThJFHA.1096@.tk2msftngp13.phx.gbl...
> Im running a query like
> select * from MyTable where SUBSTRING(MyColumn,1,300)='searchtext'
> MyColumn is th the text type.
> The text in MyColumn contains a lot of tabulator characters (TAB), carrige
> return (CR) and linefeed (LF) characters.
> Sometimes (but not often) when I run the query, even in the Query
> Analyser, I get the wrong result. I get too few characters, even though I
> know they are in the database. (If I read the field with out the
> substring, I can see everything).
> The error is consistent - either a record works as it should, or it fails
> to.
>
> All answers are very welcome.
> Thomas K
>
> select @.@.version
> ----
----
----
--
--
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> (1 row(s) affected)sql

line numbering in result set

Does anybody know how I can get a row count in the result of my query.
So first column should be 1, 2, 3 ...
Thanks,
BoontjeSince you appear to be using Microsoft SQL Server, I'd take the suggestion from Kaiowas in your posting (http://www.dbforums.com/t1095025.html) in that forum. You may get a lot of confusing answers here from people that don't know which database engine you are using.

-PatP|||FYI duplicate posts in multiple forums will be deleted

:)

Line item query

I have a query that I am trying to write that will get demographic
information from one table and then get journal entries from another
table. However, I want all the journal entries to be concatenated
together so in the end I only have one record for each person.
Any ideas?
Thanks!!use coalesce|||I tried the COALESCE function. It still returns mutiple rows for each
record.|||This is what I am dealing with:
Demographics Table
Person (PK)
Name
Address
City
State
Journal Table
Person (FK)
Journal
Each person can have only one record in Demographics. But there may be
multiple records for each person in Journal. I need to take all the
records from Journal, match them up to the records in Demographics and
then output it all as one record per person.
Thanks for the help.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
multiple records[sic] for each person in Journal.<<
What you attempted to post makes no sense. Rows are not anything like
records, But ignoring that, if this "person_<something>" is a key,
then BY DEFINITION there can be multiple rows per person in the Journal
table.
If I understand what you mean, that is called violating First Normal
Form and we do not do this in an RDBMS. You want to use a 1950's COBOL
file system with variant records and an OCCURS clause.|||What CELKO means to say is you can do this in the application rather than
the database. Applications work well for these, not so much with database
queries.
If you are on SQL 2005 you can use the XML functionality to concatenate the
values together. Do a search in this newsgroup for XML and concatenation,
and you'll find a neat trick that makes this very simple.
If you are not on 2005 yet, I think you are stuck with a cursor, which can
be written simpler and more efficiently in most programming languages.
"CJEN" <christopher.t.davis@.gmail.com> wrote in message
news:1146667896.994197.34100@.u72g2000cwu.googlegroups.com...
> This is what I am dealing with:
> Demographics Table
> Person (PK)
> Name
> Address
> City
> State
> Journal Table
> Person (FK)
> Journal
> Each person can have only one record in Demographics. But there may be
> multiple records for each person in Journal. I need to take all the
> records from Journal, match them up to the records in Demographics and
> then output it all as one record per person.
> Thanks for the help.
>|||I might have misunderstood what you're trying to do, but what about
something like this:
CREATE TABLE People(personid int, name varchar(50))
CREATE TABLE Journals(entrydate datetime, PersonID int, entry
varchar(200))
INSERT INTO People
SELECT 1, 'Rod'
UNION ALL
SELECT 2, 'Jane'
UNION ALL
SELECT 3, 'Freddy'
INSERT INTO Journals
SELECT '2006-04-02', 1, 'Up above the streets and houses'
UNION ALL
SELECT '2006-04-01', 1, 'etc...'
UNION ALL
SELECT '2006-04-06', 2, 'I think Rod and Freddy are conspiring against
me'
UNION ALL
SELECT '2006-04-07', 2, 'I must strike first...'
GO
CREATE FUNCTION GetJournal(@.PersonID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.TotalJournal varchar(8000)
SET @.TotalJournal = ''
SELECT @.TotalJournal = @.TotalJournal + ', ' + Entry FROM Journals
WHERE PersonID = @.PersonID
RETURN @.TotalJournal
END
GO
SELECT PersonID, Name, dbo.GetJournal(PersonID)
FROM People
DROP FUNCTION GetJournal
DROP TABLE Journals
DROP TABLE People
GO

Line 1: Incorrect syntax near 're'.

Hi Guys,
This is my Sql Query to insert values. All the fields are nvarchar
except the postcode which is Int. I have a condition if the postcode
field is numeric then insert values otherwise error message. Only one
of the customer gets the message on his browser
"Line 1: Incorrect syntax near 're'.
/westernsydney/register.asp, line 198 "
But other registrations are still going through with this code. Can
someone tell me why this is happening
strSql5 = "INSERT INTO tblattendeem
(attendee_firstname,attendee_surname,att
endee_location_street1,attendee_loca
tion_suburb,attendee_location_postcode,a
ttendee_company_name,attendee_phone,
attendee_email,event_id)"_
& "VALUES
('"&Request.QueryString("firstname")&"','"&Request.QueryString("surname")&"'
,"_
&"
'"&Request.QueryString("address")&"','"&Request.QueryString("suburb")&"',"_
&" '"&Request.QueryString("postcode")&"',
'"&Request.QueryString("company")&"',"_
&" '"&Request.QueryString("phone_number")&"',
'"&Request.QueryString("email")&"',"_
&" '"&Request.Cookies("event_id")&"' )"
Many ThanksHey Nachi, without looking at your sample data, it's difficult to tell
what's going on. My guess is that you have a customer with either a
quote (") or an apostrophe (') in the column.
There are other issues that you be aware of, however; building a string
like you are doing increases the chances that someone will be able to
inject another SQL statement into your code, thus doing damange to your
application. See http://aspalliance.com/385 for an example.
Also, do any of your postal codes start with a 0? If so, you may want
reconsider using an integer to store the data.
HTH,
Stu|||Hi Stu,
Thank you so much for your time & Quick reply. This Groups is really
useful. Im waiting on the Data from this specific customer. thank you
for your suggestion as well on Sql Injection. I will impl this in
future.
K Regards,
Nachi
Stu wrote:
> Hey Nachi, without looking at your sample data, it's difficult to tell
> what's going on. My guess is that you have a customer with either a
> quote (") or an apostrophe (') in the column.
> There are other issues that you be aware of, however; building a string
> like you are doing increases the chances that someone will be able to
> inject another SQL statement into your code, thus doing damange to your
> application. See http://aspalliance.com/385 for an example.
> Also, do any of your postal codes start with a 0? If so, you may want
> reconsider using an integer to store the data.
> HTH,
> Stu|||To emphasize Stu's point, there are hackers who do nothing all day but
search for web sites to exploit SQL injection vulnerabilities. If your site
is exposed to the public internet, be sure to heed Stu's advice use only
parameterized SQL queries. I hope that 'in the future' means now.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nachi" <Rengenath@.gmail.com> wrote in message
news:1148612435.121757.148630@.j33g2000cwa.googlegroups.com...
> Hi Stu,
> Thank you so much for your time & Quick reply. This Groups is really
> useful. Im waiting on the Data from this specific customer. thank you
> for your suggestion as well on Sql Injection. I will impl this in
> future.
> K Regards,
> Nachi
> Stu wrote:
>

Limits on parameters and query complexity?

I have a huge SQL statement with subqueries in the From and Where clauses.
When I run it in SQL Query Analyzer, it runs fine, but if I put it into the
Data tab of a report, Visual Studio just hangs.
I've been able to create the parameter list manually (adding them to the
Report Parameters dialog), and then when I paste the query in without the
Where clause, it works ok. I can then start adding lines to the Where
clause, but as soon as I get enough lines for about 10 parameters or so, it
hangs up again.
I know nothing's wrong with the SQL itself since it runs in Query Analyzer
without a hitch; am I looking at a bug in RS?Sounds like a bug. If you have a good repro, please send it to me at
bradsy@.microsoft.com
--
| From: "DJM" <msnews@.puddlestheshark.com>
| Subject: Limits on parameters and query complexity?
| Date: Mon, 14 Feb 2005 16:53:09 -0700
| Lines: 14
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <uG8e6AvEFHA.548@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 64.140.73.34
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:35923
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a huge SQL statement with subqueries in the From and Where
clauses.
| When I run it in SQL Query Analyzer, it runs fine, but if I put it into
the
| Data tab of a report, Visual Studio just hangs.
|
| I've been able to create the parameter list manually (adding them to the
| Report Parameters dialog), and then when I paste the query in without the
| Where clause, it works ok. I can then start adding lines to the Where
| clause, but as soon as I get enough lines for about 10 parameters or so,
it
| hangs up again.
|
| I know nothing's wrong with the SQL itself since it runs in Query
Analyzer
| without a hitch; am I looking at a bug in RS?
|
|
|

Limits in SQL 2000

I have a database with 10,000 records and I want to just see record
1000 - 1025 is there an easy way to do this with a query. I have found
info for my SQL using limits but it does not work with SQL Server 2000.
Please help.

Thanks in advance.Robert P (rphilipson@.legaleze.com) writes:

Quote:

Originally Posted by

I have a database with 10,000 records and I want to just see record
1000 - 1025 is there an easy way to do this with a query. I have found
info for my SQL using limits but it does not work with SQL Server 2000.

Assuming that you want to do paging for display on a web page,
see SQL Server MVP's Aaron Bertrand article on the topic on
http://www.aspfaq.com/show.asp?id=2120.

--
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

limiting the resources

Hi
I have a problem on microsoft sql server 2000.
I need to limit the amount of resources(eg cpu, mem..) a query uses
Is there a way to do this in SQL server 2000
Thks in advance
:eek:for the server u can limit the max mem usage. u can also limit the max query time for the server as a whole or for a connection. check
SET QUERY_GOVERNOR_COST_LIMIT at BOL.

Limiting SQL results

I have a query that simply needs to determine if data exists in a table for
a given key.
Initially I used COUNT:
Select COUNT(*) from TABLEWITHTONSODATA Where Key = 'X'
The table has about 500,000 of rows so this takes too long.
Then I used TOP to speed things up. But TOP causes a timeout if key X isn't
in the table.
Does anyone have any suggestions on how to simply determine if key X has
records without counting every record?
Does anyone have a clue why TOP goes into never-never land if the table has
a lot of rows?
ThanksTry
if exists (Select 1 from TABLEWITHTONSODATA Where Key = 'X')
begin
...
end
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:eF6nK$sqDHA.2592@.TK2MSFTNGP10.phx.gbl...
> I have a query that simply needs to determine if data exists in a table
for
> a given key.
> Initially I used COUNT:
> Select COUNT(*) from TABLEWITHTONSODATA Where Key = 'X'
> The table has about 500,000 of rows so this takes too long.
> Then I used TOP to speed things up. But TOP causes a timeout if key X
isn't
> in the table.
> Does anyone have any suggestions on how to simply determine if key X has
> records without counting every record?
> Does anyone have a clue why TOP goes into never-never land if the table
has
> a lot of rows?
> Thanks
>|||Use EXISTS:
...
EXISTS
(SELECT *
FROM tablewithtonsodata
WHERE key = 'X')
If it's still too slow then consider indexing the column "key".
--
David Portas
--
Please reply only to the newsgroup
--|||You could do...
IF EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
SELECT 1
Or I believe this would perform equally well:
SELECT 1 WHERE EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
COUNT(*) has to actually go and count all of the rows. TOP has to return
based on ordering of some sort. EXISTS just needs to find a single row that
matches.
I assume that Key is indexed?
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:eF6nK$sqDHA.2592@.TK2MSFTNGP10.phx.gbl...
> I have a query that simply needs to determine if data exists in a table
for
> a given key.
> Initially I used COUNT:
> Select COUNT(*) from TABLEWITHTONSODATA Where Key = 'X'
> The table has about 500,000 of rows so this takes too long.
> Then I used TOP to speed things up. But TOP causes a timeout if key X
isn't
> in the table.
> Does anyone have any suggestions on how to simply determine if key X has
> records without counting every record?
> Does anyone have a clue why TOP goes into never-never land if the table
has
> a lot of rows?
> Thanks
>|||All great suggestions...will try. I am still wondering if anyone else has
had problems with TOP in large tables.
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:eF6nK$sqDHA.2592@.TK2MSFTNGP10.phx.gbl...
> I have a query that simply needs to determine if data exists in a table
for
> a given key.
> Initially I used COUNT:
> Select COUNT(*) from TABLEWITHTONSODATA Where Key = 'X'
> The table has about 500,000 of rows so this takes too long.
> Then I used TOP to speed things up. But TOP causes a timeout if key X
isn't
> in the table.
> Does anyone have any suggestions on how to simply determine if key X has
> records without counting every record?
> Does anyone have a clue why TOP goes into never-never land if the table
has
> a lot of rows?
> Thanks
>|||In article <ueHi#GtqDHA.1632@.TK2MSFTNGP10.phx.gbl>, amachanic@.air-
worldwide.nospamallowed.com said...
> You could do...
> IF EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
> SELECT 1
> Or I believe this would perform equally well:
> SELECT 1 WHERE EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
> COUNT(*) has to actually go and count all of the rows. TOP has to return
> based on ordering of some sort. EXISTS just needs to find a single row that
> matches.
Top doesn't have to have an order. I would think you would want to go
with:
IF EXISTS (SELECT TOP 1 'X' FROM TableWithTonsOfData With (NoLock) Where
Key = 'X')
-- Do your thing
The NoLock should help depending on usage.|||"Brad" <me@.privacy.net> wrote in message
news:MPG.1a1f0ba92c81d36f98b995@.news...
> Top doesn't have to have an order. I would think you would want to go
> with:
An unordered TOP is meaningless. TOP 1 what?|||In article <OuROemvqDHA.3732@.tk2msftngp13.phx.gbl>, amachanic@.air-
worldwide.nospamallowed.com said...
> "Brad" <me@.privacy.net> wrote in message
> news:MPG.1a1f0ba92c81d36f98b995@.news...
> > Top doesn't have to have an order. I would think you would want to go
> > with:
> An unordered TOP is meaningless. TOP 1 what?
It doesn't matter in his case because he is just checking existence. Top
1 is perfect for that because it tells the server to find one record in
whatever order is easiest and then stop.
I use top 1 * or top 10 * all of the time to just get a sample record(s).|||Brad wrote:
> In article <ueHi#GtqDHA.1632@.TK2MSFTNGP10.phx.gbl>, amachanic@.air-
> worldwide.nospamallowed.com said...
> > You could do...
> >
> > IF EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
> > SELECT 1
> >
> > Or I believe this would perform equally well:
> >
> > SELECT 1 WHERE EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
> >
> > COUNT(*) has to actually go and count all of the rows. TOP has to return
> > based on ordering of some sort. EXISTS just needs to find a single row that
> > matches.
> Top doesn't have to have an order. I would think you would want to go
> with:
> IF EXISTS (SELECT TOP 1 'X' FROM TableWithTonsOfData With (NoLock) Where
> Key = 'X')
> -- Do your thing
> The NoLock should help depending on usage.
Using EXISTS(SELECT TOP 1) will not be faster than EXIST(SELECT *), but
equally fast. This is because both can be satisfied with exactly the
same algorithm.
For that reason I would suggest you use the ANSI-SQL standard, and avoid
the SQL-Server proprietary TOP keyword.
Gert-Jan

Limiting Results from MDX query

Is there any way to specify that I only
want the last 16 rows of an MDX query?

Thanks.

Depends on the query, and depends what exactly you want to limit. In the simplest case when there are no NON EMPTY clauses, you can modify the select statement from

SELECT set0 on COLUMNS, set1 on ROWS FROM cube

to

SELECT set0 on COLUMNS, Head(set1, 16) on ROWS FROM cube

|||

What I actually need is to have my results
only return values that fall within a certain
date range.

It's kinda complicated.

First I need a cumulative running total of
hours charged, then after that I need only
the last 16 weeks of data. I was thinking
of something like this, but it's not working.

WITH MEMBER [Measures].[CumulativeTimeCharged] AS

'SUM({NULL:[Date].[Week].CurrentMember},[Time Charged])'

SET [SixteenWeeks] AS

'Filter( [Date].[Day].Members,

[Date].[Day] >= STRTOMEMBER(@.graphStartDate, CONSTRAINED) AND

[Date].[Day] <= STRTOMEMBER(@.ToDate, CONSTRAINED))'

SELECT NON EMPTY

{

[Measures].[CumulativeTimeCharged]

} ON COLUMNS,

NON EMPTY

{

(

[Project].[Project Number].[Project Number].ALLMEMBERS *

[SixteenWeeks]

)

} ON ROWS

FROM

(

SELECT

(

Null:STRTOMEMBER(@.ToDate, CONSTRAINED)

) ON COLUMNS

FROM

(

SELECT

(

STRTOSET(@.Project, CONSTRAINED)

) ON COLUMNS

FROM [Timesheet_Cube]

)

)

Any suggestions?|||

Let me explain a little more.
Using the following query:

WITH MEMBER [Measures].[CumulativeTimeCharged] AS 
'SUM({NULL:[Header].[Week Ending].CurrentMember},[Time Charged])'
SELECT NON EMPTY 
 { 
 [Measures].[CumulativeTimeCharged] 
 } ON COLUMNS,
NON EMPTY 
{ 
 (
 [Project].[Project Number].[Project Number].ALLMEMBERS * 
 [Header].[Week Ending].[Week Ending].ALLMEMBERS
 ) 
} ON ROWS 
FROM
(
 SELECT 
 ( 
 STRTOSET(@.Project, CONSTRAINED) 
 ) ON COLUMNS 
 FROM [Timesheet_Cube]
)

I get the results on the screenshot at the following link:

http://picasaweb.google.com/fbalas/ScreenShots/photo#5000287345760862226

As you can see from the above results, I am getting
results from weeks before my desired range and results
after my desired range (which also are pretty useless
because there was no additional time after the cutoff so
the cumulative number is always the same).

Is my problem understandable the way I've presented it?

|||Sorry - I have hard time understanding your requirements. Do you want the running sum to run only from 16 weeks back, or do you want it to run from the beginning, but restrict to the subset of Weeks Ending in the report ?|||

Re Mosha:

Thanks for the reply.
What I'm looking for is more like using Tail() than head.
But if you look in the rest of the thread, my problem now
also includes filtering out records at the end as well.

|||

The second understanding is correct...

I want the cumulative sum to run from the beginning of
time, but I only want the last 16 weeks from the cutoff
time (which will be specified by the user using SSRS).

I will be graphing this data, that's why the user wants
to restrict it to only 16 weeks, so that they can see
the dates on the X-axis. If I were to include all the data,
the dates on the X-axis begin to get to small to read.

|||

I should also mention that I am a relative beginner
with this MDX stuff so if what I am asking is so blatantly
easy that you can't fathom why I didn't try something
completely different... please forgive me ;-)

|||

OK - if you need to get 16 weeks from the current week - you can use

StrToMember(@.CurrentWeek, CONSTRAINED) : StrToMember(@.CurrentWeek, CONSTRAINED).Lead(16)

If you need 16 weeks before the current week - you can use

StrToMember(@.CurrentWeek, CONSTRAINED).Lag(16) : StrToMember(@.CurrentWeek, CONSTRAINED)

HTH

Mosha

|||

Thank you so much Mosha! You just saved me
hours of frustration. I have 3 books now that I will
be going over to hopefully edumacate me on this topic ;-)

For those interested my final solution looked like:

WITH MEMBER [Measures].[CumulativeTimeCharged] AS

'SUM({NULL:[Header].[Week Ending].CurrentMember},[Time Charged])'

SELECT NON EMPTY

{

[Measures].[CumulativeTimeCharged]

} ON COLUMNS,

NON EMPTY

{

(

[Project].[Project Number].[Project Number].ALLMEMBERS *

{

StrToMember(@.ToDate, CONSTRAINED).Lag(15) : StrToMember(@.ToDate, CONSTRAINED)

}

)

} ON ROWS

FROM

(

SELECT

(

STRTOSET(@.Project, CONSTRAINED)

) ON COLUMNS

FROM [Timesheet_Cube]

)

|||

I have 3 books now

Is one of those books mine ? (Fast Track to MDX)

|||Actually yes, one of them is ;-)

Limiting Results from MDX query

Is there any way to specify that I only
want the last 16 rows of an MDX query?

Thanks.

Depends on the query, and depends what exactly you want to limit. In the simplest case when there are no NON EMPTY clauses, you can modify the select statement from

SELECT set0 on COLUMNS, set1 on ROWS FROM cube

to

SELECT set0 on COLUMNS, Head(set1, 16) on ROWS FROM cube

|||

What I actually need is to have my results
only return values that fall within a certain
date range.

It's kinda complicated.

First I need a cumulative running total of
hours charged, then after that I need only
the last 16 weeks of data. I was thinking
of something like this, but it's not working.

WITH MEMBER [Measures].[CumulativeTimeCharged] AS

'SUM({NULL:[Date].[Week].CurrentMember},[Time Charged])'

SET [SixteenWeeks] AS

'Filter( [Date].[Day].Members,

[Date].[Day] >= STRTOMEMBER(@.graphStartDate, CONSTRAINED) AND

[Date].[Day] <= STRTOMEMBER(@.ToDate, CONSTRAINED))'

SELECT NON EMPTY

{

[Measures].[CumulativeTimeCharged]

} ON COLUMNS,

NON EMPTY

{

(

[Project].[Project Number].[Project Number].ALLMEMBERS *

[SixteenWeeks]

)

} ON ROWS

FROM

(

SELECT

(

Null:STRTOMEMBER(@.ToDate, CONSTRAINED)

) ON COLUMNS

FROM

(

SELECT

(

STRTOSET(@.Project, CONSTRAINED)

) ON COLUMNS

FROM [Timesheet_Cube]

)

)

Any suggestions?|||

Let me explain a little more.
Using the following query:

WITH MEMBER [Measures].[CumulativeTimeCharged] AS 
'SUM({NULL:[Header].[Week Ending].CurrentMember},[Time Charged])'
SELECT NON EMPTY 
 { 
 [Measures].[CumulativeTimeCharged] 
 } ON COLUMNS,
NON EMPTY 
{ 
 (
 [Project].[Project Number].[Project Number].ALLMEMBERS * 
 [Header].[Week Ending].[Week Ending].ALLMEMBERS
 ) 
} ON ROWS 
FROM
(
 SELECT 
 ( 
 STRTOSET(@.Project, CONSTRAINED) 
 ) ON COLUMNS 
 FROM [Timesheet_Cube]
)

I get the results on the screenshot at the following link:

http://picasaweb.google.com/fbalas/ScreenShots/photo#5000287345760862226

As you can see from the above results, I am getting
results from weeks before my desired range and results
after my desired range (which also are pretty useless
because there was no additional time after the cutoff so
the cumulative number is always the same).

Is my problem understandable the way I've presented it?

|||Sorry - I have hard time understanding your requirements. Do you want the running sum to run only from 16 weeks back, or do you want it to run from the beginning, but restrict to the subset of Weeks Ending in the report ?|||

Re Mosha:

Thanks for the reply.
What I'm looking for is more like using Tail() than head.
But if you look in the rest of the thread, my problem now
also includes filtering out records at the end as well.

|||

The second understanding is correct...

I want the cumulative sum to run from the beginning of
time, but I only want the last 16 weeks from the cutoff
time (which will be specified by the user using SSRS).

I will be graphing this data, that's why the user wants
to restrict it to only 16 weeks, so that they can see
the dates on the X-axis. If I were to include all the data,
the dates on the X-axis begin to get to small to read.

|||

I should also mention that I am a relative beginner
with this MDX stuff so if what I am asking is so blatantly
easy that you can't fathom why I didn't try something
completely different... please forgive me ;-)

|||

OK - if you need to get 16 weeks from the current week - you can use

StrToMember(@.CurrentWeek, CONSTRAINED) : StrToMember(@.CurrentWeek, CONSTRAINED).Lead(16)

If you need 16 weeks before the current week - you can use

StrToMember(@.CurrentWeek, CONSTRAINED).Lag(16) : StrToMember(@.CurrentWeek, CONSTRAINED)

HTH

Mosha

|||

Thank you so much Mosha! You just saved me
hours of frustration. I have 3 books now that I will
be going over to hopefully edumacate me on this topic ;-)

For those interested my final solution looked like:

WITH MEMBER [Measures].[CumulativeTimeCharged] AS

'SUM({NULL:[Header].[Week Ending].CurrentMember},[Time Charged])'

SELECT NON EMPTY

{

[Measures].[CumulativeTimeCharged]

} ON COLUMNS,

NON EMPTY

{

(

[Project].[Project Number].[Project Number].ALLMEMBERS *

{

StrToMember(@.ToDate, CONSTRAINED).Lag(15) : StrToMember(@.ToDate, CONSTRAINED)

}

)

} ON ROWS

FROM

(

SELECT

(

STRTOSET(@.Project, CONSTRAINED)

) ON COLUMNS

FROM [Timesheet_Cube]

)

|||

I have 3 books now

Is one of those books mine ? (Fast Track to MDX)

|||Actually yes, one of them is ;-)

limiting result set from MS SQL Server Query

Hello,

I am running SQL Server 2000. I would like to know whether
Microsoft Transact-SQL has a method for limiting the result
set from a query in a way analogous to MySQL's LIMIT keyword,
so that, for instance, if the result set contains 10,000 rows,
then only the first 10 rows from the record set are output.

Thank you,

Best Regards,

Neilnzanella@.cs.mun.ca wrote:
> Hello,
> I am running SQL Server 2000. I would like to know whether
> Microsoft Transact-SQL has a method for limiting the result
> set from a query in a way analogous to MySQL's LIMIT keyword,
> so that, for instance, if the result set contains 10,000 rows,
> then only the first 10 rows from the record set are output.

Yes. You need to use the TOP clause.

In your example:

SELECT TOP 10 col1, col2, col3 FROM TABLE|||(nzanella@.cs.mun.ca) writes:
> I am running SQL Server 2000. I would like to know whether
> Microsoft Transact-SQL has a method for limiting the result
> set from a query in a way analogous to MySQL's LIMIT keyword,
> so that, for instance, if the result set contains 10,000 rows,
> then only the first 10 rows from the record set are output.

As Andrew said you can use TOP. For it to be meaningful, you need in
most cases also use an ORDER BY clause.

If you want to implement paging, there are a couple of options. One is
to use a @.last_key variable, and to:

SELECT TOP 10 ... FROM tbl WHERE keycol = @.last_key ORDER BY keycol

To implement arbitrary jumps, you can do something like:

CREATE TABLE #temp (ident int IDENTITY,
...
)

and then

INSERT #temp (...)
SELECT ...

And to get rows 41 to 60

SELECT ... FROM #temp WHERE ident BETWEEN 41 AND 60

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

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

limiting query result based on AS clause

can some point me to a sql statement that would perfrom the equivalent of th
e
following statement?
select Desc, (select count (*) from CART_Items where
fkItemCategoryID=CART_ItemCategories.pkItemCategoryID ) itemcount
from CART_ItemCategories
where itemcount >0
typical ouput
Desc itemcount
Cat 1 0
Cat 2 2
Cat 3 1
Cat 4 0
i would simply like to limit my results to itemcount values greater than zer
o.SELECT
ic.Desc,
COUNT(ci.fkItemCategoryID)
FROM
Cart_Items ci
INNER JOIN
Cart_ItemCategories ic
ON
ci.fkItemCategoryID = ic.pkItemCategoryID
GROUP BY
ic.Desc
HAVING
COUNT(ci.fkItemCategoryID) > 0;
"Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
news:FFF7450A-A17A-479B-B169-8DE92212E3CD@.microsoft.com...
> can some point me to a sql statement that would perfrom the equivalent of
> the
> following statement?
> select Desc, (select count (*) from CART_Items where
> fkItemCategoryID=CART_ItemCategories.pkItemCategoryID ) itemcount
> from CART_ItemCategories
> where itemcount >0
>
> typical ouput
> Desc itemcount
> Cat 1 0
> Cat 2 2
> Cat 3 1
> Cat 4 0
> i would simply like to limit my results to itemcount values greater than
> zero.|||thanks a million
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT
> ic.Desc,
> COUNT(ci.fkItemCategoryID)
> FROM
> Cart_Items ci
> INNER JOIN
> Cart_ItemCategories ic
> ON
> ci.fkItemCategoryID = ic.pkItemCategoryID
> GROUP BY
> ic.Desc
> HAVING
> COUNT(ci.fkItemCategoryID) > 0;
>
>
> "Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
> news:FFF7450A-A17A-479B-B169-8DE92212E3CD@.microsoft.com...
>
>