How to make this simple SELECT query perform faster

Hi,

I have the following simple SELECT query:

SELECT * FROM TRX_LAPD

But the problem that table TRX_LAPD is very big. Althoug, I am using this query in a Network envirenment.

If I implement this query inside Microsoft SQL Server, the first time is somehow slow (40 sec), but becasue of the caching cabability the next time is very fast.

The thing that I am using this query in Visual Web Developer, so to be accessed by other users in the local network, but it seems that there is no caching features; each time I execute the query the implementation remains slow (40 sec) even from the from the server PC which have the SQL Server running.

I used also a simple Stored Procedure, but nothing changed

Below my code in Visual Web Developer with VB script:

Sub getmytable()
Dim sql As String
sql = "SELECT * FROM TRX_LAPD"
'or sql = "EXEC getTRX_LAPD" if I will use the Stored Procedure

MySqlDataSource.SelectCommand = sql
'where MySQLDataSource is an SqlDataSource control
End Sub

and the Stored Procedure that I tried also:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[getTRX_LAPD]
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM TRX_LAPD
END

The SqlDatasource is bounded to a GridView control
I really appreciate any help.
Thanks

There are two basic rules of querying a database table:

1. Query only the rows that you need.
2. Query only the columns that you need.

By doing "SELECT * FROM TRX_LAPD", you are retreiving all the rows and all the columns all the time. Is that really a necessity? If you are doing this on a "very big" table (as you as say) it is bound to take time. I suggest you should revisit the design of querying the entire table every time.

how to make sure a upgrade from sql 2005 standard to enterprise edition?

Randy,

I did run upgrade advisior to check the existed sql 2005 standard edition to upgrade to enterprise editon. I got the following error message:

SQL Server version: 09.00.1399 is not supported by this release of Upgrade Advisor

Is it means the upgrade advisor can only work on from 7.0, 2000 to 2005? If I need check from standard to enterjprise in 2005, what kind of tool I can use?

One of the SQL Server forums would be a better place to ask this question:

http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1

You'll have better luck finding an answer there.

-Tom

How to make footers page number appear when i print the report

I have the footer with the following in it,

**********************************

= " Page " & Globals!PageNumber & " of " & Globals!TotalPages & " " & String.Format(Globals!ExecutionTime, "dd-MM-yyy uu:mm")

********************************

When i view the report it shows the page number and time, but when i print it does'nt appear at all on the print paper( page number and time)

is there a trick to make it appear on the print.

Thank you very much.

Reddy,

I'm going to assume you have the above code inside a textbox expression with in your footer. There is no smoke and mirror involved in displaying page number in the footer. I would suggest checking the textbox to make sure visible is true, check footer for printonfirstpage is true (in case there's one page), also go to the print view of the report to see if it shows up there.

I hope this helps.

Ham

|||You might have it too far down. Sometimes it cuts it off if you place the textbox too far down.

How to loop through a table

Hi,
I have the following table X
create table x
(
a int
)
;
insert x
values(1);
insert x
values(2);
insert x
values(3);
Is there a way to extract each a in table X to get the following print
result?
x is 1
x is 2
x is 3
Thanks a lot!!
Michael
"Michael" <michaelnx@.gmail.com> wrote in message
news:1176324884.807557.48050@.n76g2000hsh.googlegro ups.com...
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
There is, but why would you want to loop?
Simply do a select.
select 'x is ' + cast(a as char(2)) from x;

> x is 1
> x is 2
> x is 3
>
> Thanks a lot!!
> Michael
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
> x is 1
> x is 2
> x is 3
> Thanks a lot!!
> Michael
select 'x is ' + convert(varchar(20),a)
from x
order by a
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||On Apr 11, 7:57 pm, "SQL Menace" <denis.g...@.gmail.com> wrote:
> On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
>
>
>
>
>
>
> select 'x is ' + convert(varchar(20),a)
> from x
> order by a
> Denis the SQL Menacehttp://sqlservercode.blogspot.com/- Hide quoted text -
> - Show quoted text -
Thanks a lot!! The reason why I want to loop through the table is that
I simplified the problem. I need to loop through the table to get
exactly what I want. Anyone could show me that?
Thanks!!
Michael
|||On 12 Apr, 14:06, "Michael" <michae...@.gmail.com> wrote:
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?
> Thanks!!
> Michael
>
That's an unwise assumption. In my experience it's untrue at least
99.99% of the time so do not assume that you will need to loop through
the table row by row unless you have had an expert opinion to that
effect. Maybe you consider you are an expert but the fact that you are
asking this question at all suggests that you should take a much more
cautious approach - for example you could post an accurate description
of the problem to see if anyone can suggest alternative solutions.
What you are asking for is called a cursor. Look up DECLARE CURSOR in
Books Online. Cursors can be very bad news in the wrong hands and the
stock advice is that you should avoid cursors and write set-based code
instead wherever possible.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1176383189.058521.159980@.e65g2000hsc.googlegr oups.com...
> On Apr 11, 7:57 pm, "SQL Menace" <denis.g...@.gmail.com> wrote:
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?
Perhaps you should post exactly what you want then. We can only answer the
questions posted.
GENERALLY, looping through a table should be and can be avoided at all
costs.
But, take a look at CURSORS in the Books Online in SQL Server.

> Thanks!!
> Michael
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

How to loop through a table

Hi,
I have the following table X
create table x
(
a int
)
;
insert x
values(1);
insert x
values(2);
insert x
values(3);
Is there a way to extract each a in table X to get the following print
result?
x is 1
x is 2
x is 3
Thanks a lot!!
Michael"Michael" <michaelnx@.gmail.com> wrote in message
news:1176324884.807557.48050@.n76g2000hsh.googlegroups.com...
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
> x is 1
> x is 2
> x is 3
>
> Thanks a lot!!
> Michael
>
Replied in .programming
Please do not multi-post.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1176324884.807557.48050@.n76g2000hsh.googlegroups.com...
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
There is, but why would you want to loop?
Simply do a select.
select 'x is ' + cast(a as char(2)) from x;

> x is 1
> x is 2
> x is 3
>
> Thanks a lot!!
> Michael
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
> x is 1
> x is 2
> x is 3
> Thanks a lot!!
> Michael
select 'x is ' + convert(varchar(20),a)
from x
order by a
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 11, 7:57 pm, "SQL Menace" <denis.g...@.gmail.com> wrote:
> On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> select 'x is ' + convert(varchar(20),a)
> from x
> order by a
> Denis the SQL Menacehttp://sqlservercode.blogspot.com/- Hide quoted text -
> - Show quoted text -
Thanks a lot!! The reason why I want to loop through the table is that
I simplified the problem. I need to loop through the table to get
exactly what I want. Anyone could show me that?
Thanks!!
Michael|||On 12 Apr, 14:06, "Michael" <michae...@.gmail.com> wrote:
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?
> Thanks!!
> Michael
>
That's an unwise assumption. In my experience it's untrue at least
99.99% of the time so do not assume that you will need to loop through
the table row by row unless you have had an expert opinion to that
effect. Maybe you consider you are an expert but the fact that you are
asking this question at all suggests that you should take a much more
cautious approach - for example you could post an accurate description
of the problem to see if anyone can suggest alternative solutions.
What you are asking for is called a cursor. Look up DECLARE CURSOR in
Books Online. Cursors can be very bad news in the wrong hands and the
stock advice is that you should avoid cursors and write set-based code
instead wherever possible.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1176383189.058521.159980@.e65g2000hsc.googlegroups.com...
> On Apr 11, 7:57 pm, "SQL Menace" <denis.g...@.gmail.com> wrote:
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?
Perhaps you should post exactly what you want then. We can only answer the
questions posted.
GENERALLY, looping through a table should be and can be avoided at all
costs.
But, take a look at CURSORS in the Books Online in SQL Server.

> Thanks!!
> Michael
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

How to loop through a table

Hi,
I have the following table X
create table x
(
a int
)
;
insert x
values(1);
insert x
values(2);
insert x
values(3);
Is there a way to extract each a in table X to get the following print
result?
x is 1
x is 2
x is 3
Thanks a lot!!
Michael"Michael" <michaelnx@.gmail.com> wrote in message
news:1176324884.807557.48050@.n76g2000hsh.googlegroups.com...
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
> x is 1
> x is 2
> x is 3
>
> Thanks a lot!!
> Michael
>
Replied in .programming
Please do not multi-post.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1176324884.807557.48050@.n76g2000hsh.googlegroups.com...
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
There is, but why would you want to loop?
Simply do a select.
select 'x is ' + cast(a as char(2)) from x;
> x is 1
> x is 2
> x is 3
>
> Thanks a lot!!
> Michael
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
> x is 1
> x is 2
> x is 3
> Thanks a lot!!
> Michael
select 'x is ' + convert(varchar(20),a)
from x
order by a
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 11, 7:57 pm, "SQL Menace" <denis.g...@.gmail.com> wrote:
> On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
>
>
> > Hi,
> > I have the following table X
> > create table x
> > (
> > a int
> > )
> > ;
> > insert x
> > values(1);
> > insert x
> > values(2);
> > insert x
> > values(3);
> > Is there a way to extract each a in table X to get the following print
> > result?
> > x is 1
> > x is 2
> > x is 3
> > Thanks a lot!!
> > Michael
> select 'x is ' + convert(varchar(20),a)
> from x
> order by a
> Denis the SQL Menacehttp://sqlservercode.blogspot.com/- Hide quoted text -
> - Show quoted text -
Thanks a lot!! The reason why I want to loop through the table is that
I simplified the problem. I need to loop through the table to get
exactly what I want. Anyone could show me that?
Thanks!!
Michael|||On 12 Apr, 14:06, "Michael" <michae...@.gmail.com> wrote:
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?
> Thanks!!
> Michael
>
That's an unwise assumption. In my experience it's untrue at least
99.99% of the time so do not assume that you will need to loop through
the table row by row unless you have had an expert opinion to that
effect. Maybe you consider you are an expert but the fact that you are
asking this question at all suggests that you should take a much more
cautious approach - for example you could post an accurate description
of the problem to see if anyone can suggest alternative solutions.
What you are asking for is called a cursor. Look up DECLARE CURSOR in
Books Online. Cursors can be very bad news in the wrong hands and the
stock advice is that you should avoid cursors and write set-based code
instead wherever possible.
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1176383189.058521.159980@.e65g2000hsc.googlegroups.com...
> On Apr 11, 7:57 pm, "SQL Menace" <denis.g...@.gmail.com> wrote:
>> On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
>>
>>
>> > Hi,
>> > I have the following table X
>> > create table x
>> > (
>> > a int
>> > )
>> > ;
>> > insert x
>> > values(1);
>> > insert x
>> > values(2);
>> > insert x
>> > values(3);
>> > Is there a way to extract each a in table X to get the following print
>> > result?
>> > x is 1
>> > x is 2
>> > x is 3
>> > Thanks a lot!!
>> > Michael
>> select 'x is ' + convert(varchar(20),a)
>> from x
>> order by a
>> Denis the SQL Menacehttp://sqlservercode.blogspot.com/- Hide quoted
>> text -
>> - Show quoted text -
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?
Perhaps you should post exactly what you want then. We can only answer the
questions posted.
GENERALLY, looping through a table should be and can be avoided at all
costs.
But, take a look at CURSORS in the Books Online in SQL Server.
> Thanks!!
> Michael
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

how to lock the store procedure and allow one process to acces it at a time

Hello:

I run one process that calls the following the store procedure and
works fine.

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

SELECT int_parameter_dbf + 1
FROM system_parameter_dbt
WHERE parameter_name_dbf = 'seqNum'

UPDATE system_parameter_dbt
SET int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

END
GO

If I run two processes that call the above store procedure, I might
occasionally get the dirty data of int_parameter_dbt. I guess that is
caused by two processes accessing to the same resource simultaneously.
Is there any way to lock the store procedure call from MSSQL Server
and allow only one process to access it at a time?

Thanks for help.

Best Jin"Jin" <texlqj@.hotmail.com> wrote in message
news:82b49cd5.0401131104.7c12efc5@.posting.google.c om...
> Hello:
> I run one process that calls the following the store procedure and
> works fine.
> create PROCEDURE sp_GetHostSequenceNum
> AS
> BEGIN
> SELECT int_parameter_dbf + 1
> FROM system_parameter_dbt
> WHERE parameter_name_dbf = 'seqNum'
> UPDATE system_parameter_dbt
> SET int_parameter_dbf = int_parameter_dbf + 1
> WHERE parameter_name_dbf = 'seqNum'
> END
> GO
>
> If I run two processes that call the above store procedure, I might
> occasionally get the dirty data of int_parameter_dbt. I guess that is
> caused by two processes accessing to the same resource simultaneously.
> Is there any way to lock the store procedure call from MSSQL Server
> and allow only one process to access it at a time?
> Thanks for help.
> Best Jin

Here is one possible approach, using an UPDATE syntax specific to MSSQL:

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

declare @.val int

UPDATE system_parameter_dbt
SET @.val = int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

select @.val

END
GO

Alternatively, you can use a locking hint:

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

begin tran

SELECT int_parameter_dbf + 1
FROM system_parameter_dbt with (UPDLOCK)
WHERE parameter_name_dbf = 'seqNum'

UPDATE system_parameter_dbt
SET int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

commit

END
GO

Simon|||Sure. You could do it transactionally at serializable isolation level...
Joe

Jin wrote:

> Hello:
> I run one process that calls the following the store procedure and
> works fine.
> create PROCEDURE sp_GetHostSequenceNum
> AS
> BEGIN
> SELECT int_parameter_dbf + 1
> FROM system_parameter_dbt
> WHERE parameter_name_dbf = 'seqNum'
> UPDATE system_parameter_dbt
> SET int_parameter_dbf = int_parameter_dbf + 1
> WHERE parameter_name_dbf = 'seqNum'
> END
> GO
>
> If I run two processes that call the above store procedure, I might
> occasionally get the dirty data of int_parameter_dbt. I guess that is
> caused by two processes accessing to the same resource simultaneously.
> Is there any way to lock the store procedure call from MSSQL Server
> and allow only one process to access it at a time?
> Thanks for help.
> Best Jin

how to locate a table that contains a specific trigger?

Hello,
the following tsql
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) = 'tbl_CompanyChangeHistory'
displays a list of UDFs and a trigger that tbl_CompanyChangeHistory is
dependent upon. I need to locate the table that contains this specific
trigger.
I tried
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) like 't_ChgHistory_Write%'
but this did not yield anything (empty).
How can I locate the table which contains this trigger?
Thanks,
RichReverse id and depid:
select DISTINCT OBJECT_NAME([depid]) FROM sysdepends
WHERE OBJECT_NAME([id]) like 't_ChgHistory_Write%' --trigger name?
"Rich" wrote:

> Hello,
> the following tsql
> select DISTINCT OBJECT_NAME([id]) FROM sysdepends
> WHERE OBJECT_NAME([depid]) = 'tbl_CompanyChangeHistory'
> displays a list of UDFs and a trigger that tbl_CompanyChangeHistory is
> dependent upon. I need to locate the table that contains this specific
> trigger.
> I tried
> select DISTINCT OBJECT_NAME([id]) FROM sysdepends
> WHERE OBJECT_NAME([depid]) like 't_ChgHistory_Write%'
> but this did not yield anything (empty).
> How can I locate the table which contains this trigger?
> Thanks,
> Rich
>|||Thanks for your reply. I had the [id] and [depid] columns switched around
which was incorrect. But your solution retrieved the dependent table and a
dependent UDF (still good stuff).
Here is something else I tried that actually retrieve the table I was
looking for
select OBJECT_NAME([parent_obj]) FROM sysobjects
WHERE [id] = 1467920351
The [id] here is the [id] of the trigger which I retrieved like this:
select * from sysobjects where xtype ='tr'
and name = 't_CompaniesWrite'
"Mark Williams" wrote:
> Reverse id and depid:
> select DISTINCT OBJECT_NAME([depid]) FROM sysdepends
> WHERE OBJECT_NAME([id]) like 't_ChgHistory_Write%' --trigger name?
>
> --
>
> "Rich" wrote:
>|||Rich
I'd not rely on sysdepends table ,instead take a look at Vyas's examle
CREATE PROCEDURE sp_FindObject
@.SearchString varchar (255)
AS
SET nocount ON
DECLARE @.Name varchar(255)
DECLARE @.Text nvarchar(4000)
CREATE TABLE #Objs
( ObjName varchar (255))
DECLARE Obj CURSOR
FOR
SELECT [NAME]
FROM
(
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='P' )
AND so.id = sc.id
UNION ALL
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='V' )
AND so.id = sc.id
UNION ALL
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='TR' )
AND so.id = sc.id
) AS Der WHERE [TEXT] LIKE @.SearchString
OPEN Obj
FETCH Next FROM Obj INTO @.Name
WHILE @.@.FETCH_STATUS=0
BEGIN
INSERT INTO #Objs VALUES (@.Name)
FETCH Next FROM Obj INTO @.Name
END
CLOSE Obj
DEALLOCATE Obj
SELECT objname FROM #Objs GROUP BY objname
DROP TABLE #Objs
GO
EXEC sp_FindObject '%HOST_ID()%'
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:BF61EDA5-4130-40E8-8FA5-2DF3938B0E9C@.microsoft.com...
> Thanks for your reply. I had the [id] and [depid] columns switched around
> which was incorrect. But your solution retrieved the dependent table and
> a
> dependent UDF (still good stuff).
> Here is something else I tried that actually retrieve the table I was
> looking for
> select OBJECT_NAME([parent_obj]) FROM sysobjects
> WHERE [id] = 1467920351
> The [id] here is the [id] of the trigger which I retrieved like this:
> select * from sysobjects where xtype ='tr'
> and name = 't_CompaniesWrite'
> "Mark Williams" wrote:
>

How to load a Legacy DTS Package from .NET?

If I have an object of type "Microsoft.SqlServer.DTS.Runtime.Application,"

can I use one of the following functions
- ExistsOnDTSServer
- ExistsOnSQLServer
- LoadFromDTSServer
- LoadFromSQLServer
- LoadFromSQLServer2

to interact/load with a Legacy (SQL 2000) DTS package, stored on a SQL 2005 machine?

If so, can someone post an example first-argument (the Package Path) ?
Thanks.

I suspect that this is not possible.

Why would you not use the DTS object model to load and run a DTS package? Although it's a COM library, you can certainly use it from .NET...

-Doug|||You're probably right. It was worth someone verifying before assuming correctly/incorrectly.

The "Microsoft DTSPackage Object" COM object is an adequate alternative.
But this would require SQL 2000 Client Tools to be installed on the machine, in addition to SQL 2005 Client Tools, to gain use of both objects.

How to limit the MDX dataset for the last six months

I have the following MDX query and I'd like to put in another condition that the "[Account Period].[True Prescription Date].[True Prescription Date].ALLMEMBERS" has to be GREATER than OR EQUAL TO last six months. Eg. using today date as 21-Nov-2006 and the dataset should only include the records from 21-May-2006 onward.

SELECT NON EMPTY { [Measures].[Pharmacy DW Count] } ON COLUMNS, NON EMPTY { ([AgencyID].[Agency Id].[Agency Id].ALLMEMBERS * [Account Period].[True Prescription Date].[True Prescription Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.DrugProtocolCode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DrugDrugName, CONSTRAINED) ) ON COLUMNS FROM [Patient Hospital and Drug])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Thanks

It depends how you build your True Prescription Date attribute. If we will assume, that it is rebiult daily, and the last attribute member is today's date (i.e. no dates go to the future), then it would be something like

Lag([Account Period].[True Prescription Date].[All True Prescription Dates].LastChild, 182):[Account Period].[True Prescription Date].[All True Prescription Dates].LastChild)

If the rules are more complex, then it is probably best to build named set inside MDX Script which will resolve to the last 6 months worth of dates, and use it instead in the queries.

How to LIMIT results in an html php/sql query?

Hi,

I need to limit results in the following query type:

http://www.somewhere.com/php/sql-a...ql_order=&pos=1

I found a reference that says I should be able to use LIMIT x[,y], but
I don't know where/exactly how to add that to the string. Once I know
what it's supposed to look like, and can write something to generate
it.

If someone could post an example using the above and limiting the
output to 100 records starting at position 1, that would be great.LIMIT isn't a standard SQL feature. It is supported by MySQL but not SQL
Server.

Take a look at the following alternative solutions:
http://www.aspfaq.com/show.asp?id=2120

--
David Portas
SQL Server MVP
--

how to let others connect to my sql server 2005?

Hi,

My collegue is trying to connect to my sql server 2005 using login 'sa' but he is not able to. It gives the following error :-

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 233)

Please suggest

Ashu sharma

You have to enable remote connections:

http://www.developer.com/db/article.php/3496511
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=204199&SiteID=1

HTH, Jens Suessmeyer.

|||

Still not working, all the protocols are enable and i have also allowed remote connection.

Any Idea?

Ashu

|||Did you fire up SQL Browser? Do you have a firewall running? Checkout this blog: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx|||

Check also this here:

http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

HTH, Jens Suessmeyer.

How to let a SP be shorter?

Hi,
The following is one of our stored procedures,
is it possible to make it shorter?
Thanks for help.
Jason
ALTER PROCEDURE UpdateCustomerBtoRoot
AS
SET nocount on
UPDATE Customer SET
CustCName = (SELECT CustCName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
CustEName =(SELECT CustEName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
AbbrName=(SELECT AbbrName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
ZipCode =(SELECT ZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Addr = (SELECT Addr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
EZipCode = (SELECT EZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillZipCode = (SELECT BillZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillAddr = (SELECT BillAddr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Phone = (SELECT Phone FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Fax = (SELECT Fax FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Owner = (SELECT Owner FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
OwnerTitle = (SELECT OwnerTitle FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
HomePage = (SELECT HomePage FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
FirstDate = (SELECT FirstDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
DateUpDate = (SELECT DateUpDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Area = (SELECT Area FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
WeightMeasure =(SELECT WeightMeasure FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo)
WHERE EXISTS (SELECT * FROM
CustomerB WHERE CustomerB.CustNo=Customer.CustNo)
GO
Hi, Jason
Use something like this:
UPDATE Customer
SET CustCName=B.CustCName,
CustEName=B.CustEName,
AbbrName=B.AbbrName,
[...]
FROM Customer as C INNER JOIN CustomerB as B
ON C.CustNo=B.CustNo
In order to get the expected results, make sure that the CustNo is a
unique key or a primary key in both tables. Although easier to read,
this syntax is less portable than the original statement (the original
syntax was ANSI standard, but this is proprietary to Microsoft SQL
Server).
Razvan

How to let a SP be shorter?

Hi,
The following is one of our stored procedures,
is it possible to make it shorter?
Thanks for help.
Jason
ALTER PROCEDURE UpdateCustomerBtoRoot
AS
SET nocount on
UPDATE Customer SET
CustCName = (SELECT CustCName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
CustEName =(SELECT CustEName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
AbbrName=(SELECT AbbrName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
ZipCode =(SELECT ZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Addr = (SELECT Addr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
EZipCode = (SELECT EZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillZipCode = (SELECT BillZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillAddr = (SELECT BillAddr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Phone = (SELECT Phone FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Fax = (SELECT Fax FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Owner = (SELECT Owner FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
OwnerTitle = (SELECT OwnerTitle FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
HomePage = (SELECT HomePage FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
FirstDate = (SELECT FirstDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
DateUpDate = (SELECT DateUpDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Area = (SELECT Area FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
WeightMeasure =(SELECT WeightMeasure FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo)
WHERE EXISTS (SELECT * FROM
CustomerB WHERE CustomerB.CustNo=Customer.CustNo)
GOHi, Jason
Use something like this:
UPDATE Customer
SET CustCName=B.CustCName,
CustEName=B.CustEName,
AbbrName=B.AbbrName,
[...]
FROM Customer as C INNER JOIN CustomerB as B
ON C.CustNo=B.CustNo
In order to get the expected results, make sure that the CustNo is a
unique key or a primary key in both tables. Although easier to read,
this syntax is less portable than the original statement (the original
syntax was ANSI standard, but this is proprietary to Microsoft SQL
Server).
Razvan

How to let a SP be shorter?

Hi,
The following is one of our stored procedures,
is it possible to make it shorter?
Thanks for help.
Jason
ALTER PROCEDURE UpdateCustomerBtoRoot
AS
SET nocount on
UPDATE Customer SET
CustCName = (SELECT CustCName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
CustEName =(SELECT CustEName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
AbbrName=(SELECT AbbrName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
ZipCode =(SELECT ZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Addr = (SELECT Addr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
EZipCode = (SELECT EZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillZipCode = (SELECT BillZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillAddr = (SELECT BillAddr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Phone = (SELECT Phone FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Fax = (SELECT Fax FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Owner = (SELECT Owner FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
OwnerTitle = (SELECT OwnerTitle FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
HomePage = (SELECT HomePage FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
FirstDate = (SELECT FirstDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
DateUpDate = (SELECT DateUpDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Area = (SELECT Area FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
WeightMeasure =(SELECT WeightMeasure FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo)
WHERE EXISTS (SELECT * FROM
CustomerB WHERE CustomerB.CustNo=Customer.CustNo)
GOHi, Jason
Use something like this:
UPDATE Customer
SET CustCName=B.CustCName,
CustEName=B.CustEName,
AbbrName=B.AbbrName,
[...]
FROM Customer as C INNER JOIN CustomerB as B
ON C.CustNo=B.CustNo
In order to get the expected results, make sure that the CustNo is a
unique key or a primary key in both tables. Although easier to read,
this syntax is less portable than the original statement (the original
syntax was ANSI standard, but this is proprietary to Microsoft SQL
Server).
Razvan