Remove headers in SQL Query analyzer when making a query

Hi Friends...
I need to know how can i remove the colum headers when making a query with
sql query analizer, the query is:
select succeeded from sysdbmaintplan_history
i need that the field header 'succeeded' dissapear!!!
thanks alluse empty space as a column name
select succeeded as ' ' from sysdbmaintplan_history
--
Programmer
"Tinchos" wrote:

> Hi Friends...
> I need to know how can i remove the colum headers when making a query with
> sql query analizer, the query is:
> select succeeded from sysdbmaintplan_history
> i need that the field header 'succeeded' dissapear!!!
> thanks all|||Click on Tools/Options menu. Goto results tab. You will see the option for
column headers - just uncheck it.
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:0FD3406C-E8B0-4CE6-A9DE-9E7109213380@.microsoft.com...
> Hi Friends...
> I need to know how can i remove the colum headers when making a query with
> sql query analizer, the query is:
> select succeeded from sysdbmaintplan_history
> i need that the field header 'succeeded' dissapear!!!
> thanks all

Remove duplicates using SQL query

I recieve duplicate customer bill data which I want to consolidate using SQL query. How to summarize data using single query...both Qty and Price. If either one of price or qty is -ve then both should be -ve.

Here is example data for one customer.

1 – Only Qty is negative

Product Qty Price

Shirt 2 6.00

Shirt -1 6.00

-

Shirt 1 6.00 Result

2 - Only price is negative

Product Qty Price

Pant 2 6.00

Pant 1 -6.00

-

Pant 1 6.00 Result

Thanks for help!!

Pintoo

Based solely upon the limited information and sample data you provided, this solution will meet your requirements.

However, questions arise. Is the price always the same, if not, which price to return? (ABS() may be useful, so could AVG().)

Code Snippet

DECLARE @.MyTable table
( Product varchar(20),
Qty int,
Price decimal(8,2)
)

INSERT INTO @.MyTable VALUES ( 'Shirt', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 1, -6.00 )

SELECT
Product,
Qty = sum( CASE
WHEN Price < 0 THEN ( Qty * -1 )
ELSE Qty
END
),
Price = max( Price )
FROM @.MyTable
GROUP BY Product

Product Qty Price
-- -- -
Pant 1 6.00
Shirt 1 6.00

|||

Thanks Arnie.

Price is not always same. I have to calculate price by sum(price)/sum(Qty). But how can I use the quantity calculated above to calculate price.

for ex.

SELECT
Product,
Qty = sum( CASE
WHEN Price < 0 THEN ( Qty * -1 )
ELSE Qty
END
),
Price = sum(price*qty)/ sum(qty) caclulated above(how to reuse qty from above query...not sure).

FROM @.MyTable
GROUP BY Product

Thanks

Pintoo

|||

Just the way you expressed it -you were almost there.

Using the ABS() function handles the negative prices and quantities.

Code Snippet

SELECT
Product,
Qty = sum( CASE
WHEN Price < 0 THEN ( Qty * -1 )
ELSE Qty
END
),
Price = ( sum( abs( Price ) * abs( Qty )) / sum( abs( Qty )) )
FROM @.MyTable
GROUP BY Product

|||

How about this query..

DECLARE @.MyTable Table

(

Productvarchar(20),

Qtyint,

Pricedecimal(8,2)

)

INSERT INTO @.MyTable VALUES ( 'Shirt', 2, 6.00 )

INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )

INSERT INTO @.MyTable VALUES ( 'Pant', 2, 6.00 )

INSERT INTO @.MyTable VALUES ( 'Pant', 1, -6.00 )

Select

Product

, Sum(Price * Qty/abs(Price))

, Sum(Price * Qty)

From

@.MyTable

Group By

Product

|||

Mani,

With your variation, a negative Price OR Qty adversely effects the ( Price * Qty ) calculation.

Notice what happens when two additional rows are added to the dataset. (Probably not exactly what the OP had in mind...) OnHand quantities might easily be negative or zero, but the average price 'should' reflect the prices used in every transaction and therefore not be negative or zero.

Code Snippet

DECLARE @.MyTable Table
( Product varchar(20),
Qty int,
Price decimal(8,2)
)

INSERT INTO @.MyTable VALUES ( 'Shirt', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 1, -6.00 )

INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 2, -6.00 )

Select
Product
, Sum( Price * Qty / abs( Price ))
, Sum( Price * Qty )
From @.MyTable
Group By Product

Product
-- - --
Pant -1.00000000000 -6.00
Shirt .00000000000 .00

Remove Duplicate Rows

I've got the following table data:

116525.99
116520.14
129965.03
129960.12
129967.00

And I need to write a query to return only rows 2 and 4, since the
remaining rows have duplicate IDs. I've tried the Group By, but am
having no luck.

Thanks!dale...@.gmail.com wrote:
> I've got the following table data:
> 116525.99
> 116520.14
> 129965.03
> 129960.12
> 129967.00
> And I need to write a query to return only rows 2 and 4, since the
> remaining rows have duplicate IDs. I've tried the Group By, but am
> having no luck.
> Thanks!

What do you mean by "rows 2 and 4"? Those numbers refer to positions in
the list of values you posted, but SQL Server knows nothing about that
because tables in SQL have no logical order at all. In other words you
haven't given enough information to answer your question.

If these are the only two columns you have then probably the best you
can do is:

SELECT col1, MIN(col2) AS col2
FROM your_table
GROUP BY col1 ;

or:

SELECT col1, MAX(col2) AS col2
FROM your_table
GROUP BY col1 ;

--
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/...US,SQL.90).aspx
--|||Why 2 and 4? Why not 1 & 3 or 1 & 5? What are you using as your
discriminator?

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<dale.zjc@.gmail.com> wrote in message
news:1144699376.547275.246250@.t31g2000cwb.googlegr oups.com...
I've got the following table data:

11652 5.99
11652 0.14
12996 5.03
12996 0.12
12996 7.00

And I need to write a query to return only rows 2 and 4, since the
remaining rows have duplicate IDs. I've tried the Group By, but am
having no luck.

Thanks!|||Thanks for the quick response!

Here's my complete query:

SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
Job.KSName, GETDATE() AS CURDATE

FROM DataHeader INNER JOIN
Data ON DataHeader.DataID = Data.DataID INNER JOIN
Object INNER JOIN
Job ON Object.ObjID = Job.MachineObjID ON
DataHeader.JobID = Job.JobID

Group By Job.JobID

But I'm getting the following error:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'Object.Name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Data.Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Job.KSName' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

David Portas wrote:
> dale...@.gmail.com wrote:
> > I've got the following table data:
> > 116525.99
> > 116520.14
> > 129965.03
> > 129960.12
> > 129967.00
> > And I need to write a query to return only rows 2 and 4, since the
> > remaining rows have duplicate IDs. I've tried the Group By, but am
> > having no luck.
> > Thanks!
> What do you mean by "rows 2 and 4"? Those numbers refer to positions in
> the list of values you posted, but SQL Server knows nothing about that
> because tables in SQL have no logical order at all. In other words you
> haven't given enough information to answer your question.
> If these are the only two columns you have then probably the best you
> can do is:
> SELECT col1, MIN(col2) AS col2
> FROM your_table
> GROUP BY col1 ;
> or:
> SELECT col1, MAX(col2) AS col2
> FROM your_table
> GROUP BY col1 ;
> --
> 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/...US,SQL.90).aspx
> --|||dale...@.gmail.com wrote:
> Thanks for the quick response!
> Here's my complete query:
> SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
> DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
> Job.KSName, GETDATE() AS CURDATE
> FROM DataHeader INNER JOIN
> Data ON DataHeader.DataID = Data.DataID INNER JOIN
> Object INNER JOIN
> Job ON Object.ObjID = Job.MachineObjID ON
> DataHeader.JobID = Job.JobID
> Group By Job.JobID
> But I'm getting the following error:
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Object.Name' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Data.Time' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Job.KSName' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.

Any column that you don't want to group by needs to be enclosed in an
aggregate function (MIN or MAX for example). Your problem is obviously
a bit different to what you first asked for. The best way to post a
problem like this is to include enough code so that others can
reproduce it. See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
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/...US,SQL.90).aspx
--|||(dale.zjc@.gmail.com) writes:
> Thanks for the quick response!
> Here's my complete query:
> SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
> DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
> Job.KSName, GETDATE() AS CURDATE
> FROM DataHeader INNER JOIN
> Data ON DataHeader.DataID = Data.DataID INNER JOIN
> Object INNER JOIN
> Job ON Object.ObjID = Job.MachineObjID ON
> DataHeader.JobID = Job.JobID
> Group By Job.JobID

This is possible correct version of your query, but most probably not.
It's just a piece of guesswork.

SELECT o.Name, j.JobID, mx.NewValue,
DATEADD(ss, d.[Time], '1 jan 1970') AS EventDate,
j.KSName, GETDATE() AS CURDATE
FROM (SELECT j.JobID, NewValue = MAX(d.[Value])
FROM DataHeader dh
JOIN Job j ON dh.JobID = j.JobID
JOIN Data d ON dh.DataID = d.DataID) AS mx
JOIN Job j ON mx.JobID = j.JobID
JOIN DataHeader dh ON dh.JobID = j.JobID
JOIN Data d ON dh.DataID = d.DataID
JOIN Object o ON o.ObjID = j.MachineObjID
GROUP BY j.JobID

For this type of questions it helps if you include descriptions of
your tables, including keys. Preferably in form of CREATE TABLE
statements. Sample data is also a good idea, even better if as
INSERT statements, as that makes it easy to post a tested solution.

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

remove duplicate row

how do i write a query that will remove duplicate rows (rows with the same
"username" field) and keep the one with lower ID
ID USERNAME
1 john
2 john2
3 john
after executing this query this should become
ID USERNAME
1 john
2 john2
Thanks,
HowardDo:
DELETE FROM tbl
WHERE id NOT IN ( SELECT MIN( t1.id )
FROM tbl t1
WHERE t1.username = tbl.username ) ;
You can also re-write this using derived table construct or using the
EXISTS() clause.
Anith|||Hi Anith,
I'll be doing the 2005 alternatives today. ;-)
WITH Dups AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY username) AS rn
FROM dbo.T1
)
DELETE FROM Dups
WHERE rn > 1;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uAMZ9Rd3FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Do:
> DELETE FROM tbl
> WHERE id NOT IN ( SELECT MIN( t1.id )
> FROM tbl t1
> WHERE t1.username = tbl.username ) ;
> You can also re-write this using derived table construct or using the
> EXISTS() clause.
> --
> Anith
>|||I should add an ORDER BY clause...
WITH Dups AS
(
SELECT *,
ROW_NUMBER()
OVER(PARTITION BY username ORDER BY id) AS rn
FROM dbo.T1
)
DELETE FROM Dups
WHERE rn > 1;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23vqZQ%23e3FHA.1140@.tk2msftngp13.phx.gbl...
> Hi Anith,
> I'll be doing the 2005 alternatives today. ;-)
> WITH Dups AS
> (
> SELECT *,
> ROW_NUMBER() OVER(PARTITION BY username) AS rn
> FROM dbo.T1
> )
> DELETE FROM Dups
> WHERE rn > 1;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> Join us for the SQL Server 2005 launch at the SQL W in Israel!
> [url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uAMZ9Rd3FHA.3976@.TK2MSFTNGP15.phx.gbl...
>

Remove Duplicate Data

I have a query that for one reason or another produces duplicate information in the result set. I have tried using DISTINCT and GROUP BY to remove the duplicates but because of the nature of the data I cannot get this to work, here is an example fo the data I am working with

ID Name Add1 Add2
1 Matt 16 Nowhere St Glasgow
1 Matt 16 Nowhere St Glasgow, Scotland
2 Jim 23 Blue St G65 TX
3 Bill 45 Red St
3 Bill 45 red St London

The problem is that a user can have one or more addresses!! I would like to be able to remove the duplicates by keeping the first duplicate ID that appears and getting rid of the second one. Any ideas?

Cheers1. think about normalizing your data ...
2. add some primary key (identity column primaryid?)
3. then try following

select * from yourtable y
where y.primaryid = (select min(yourtable.primaryid) from yourtable y2 where y2.id = y.id)

or

select * from yourtable y1
join (select min(primaryid) as primaryid from yourtable group by id) y2 on y2.primaryid = y1.primaryid

or something like that...|||Hi Ludenka,

Thanks for that. Unfortunately Normalising the data is not an option, I just have to work with it the way it is...Managed to get it to work using a rather long winded stored procedure but I'll have a look at your suggestion and see if I can do it a better way:

DECLARE @.patno int
DECLARE @.patfname varChar(255)
DECLARE @.patsname varChar(255)
DECLARE @.DOB DATETIME
DECLARE @.patadd1 varChar(255)
DECLARE @.patadd2 varChar(255)
DECLARE @.PreviousPatientID int

CREATE TABLE #tmp_tblPrivDemo (
patno int,
patfname varChar(255),
patsname varChar(255),
DOB DATETIME,
patadd1 varChar(255),
patadd2 varChar(255)
)

--First of all load all Patient details into a cursor
DECLARE PatientIDCursor CURSOR FOR
SELECT AxTbl_Patient.Patient_ID,
AxTbl_Patient.Forename1,
AxTbl_Patient.Surname,
AxTbl_Patient.Date_Of_Birth,
AxTbl_Patient_Address.Address1,
AxTbl_Patient_Address.Address2
FROM
AxTbl_Patient
LEFT JOIN AxTbl_Patient_Address ON AxTbl_Patient_Address.Patient_ID = AxTbl_Patient.Patient_ID
LEFT JOIN AxTbl_Patient_GP ON AxTbl_Patient_GP.Patient_ID = AxTbl_Patient.Patient_ID
LEFT JOIN [SpFrm_Follow_Up_Death_Details_v1-0] ON [SpFrm_Follow_Up_Death_Details_v1-0].Patient_ID = AxTbl_Patient.Patient_ID
INNER JOIN [SpFrm_Diagnostic_Procedures_v1-1] ON [SpFrm_Diagnostic_Procedures_v1-1].Patient_ID = AxTbl_Patient.Patient_ID
WHERE
(([SpFrm_Diagnostic_Procedures_v1-1].[Date of Diagnosis] >= '01-Apr-2003' AND [SpFrm_Diagnostic_Procedures_v1-1].[Date of Diagnosis] <= '30-Sep-2003') AND (AxTbl_Patient_Address.IsCurrentAddress = 'yes' OR AxTbl_Patient_Address.IsCurrentAddress IS NULL) AND (AxTbl_Patient_GP.Date_GP_Changed IS NULL))

OPEN PatientIDCursor
FETCH NEXT FROM PatientIDCursor INTO @.patno, @.patfname, @.patsname, @.DOB, @.patadd1, @.patadd2
SET @.PreviousPatientID = 0

WHILE @.@.FETCH_STATUS = 0
BEGIN
--Then only add to temp table if not already added, so no dups.
IF @.PreviousPatientID <> @.patno
BEGIN
INSERT INTO #tmp_tblPrivDemo
SELECT @.patno, @.patfname, @.patsname, @.DOB, @.patadd1, @.patadd2
END
SET @.PreviousPatientID = @.patno
FETCH NEXT FROM PatientIDCursor INTO @.patno, @.patfname, @.patsname, @.DOB, @.patadd1, @.patadd2

END --end loop.
CLOSE PatientIDCursor
DEALLOCATE PatientIDCursor

SELECT * FROM #tmp_tblPrivDemo ORDER BY patno
DROP TABLE #tmp_tblPrivDemo|||well, normalizing is not important for the task, it was only a hint...

are you able to add the identity column?
if yes, then my solution should work
if not, you could replace all of those

y1.primaryid = y2.primaryid

with comparison off all in table included columns, but ... it's not nice at all|||I have a query that for one reason or another produces duplicate information in the result set. I have tried using DISTINCT and GROUP BY to remove the duplicates but because of the nature of the data I cannot get this to work, here is an example fo the data I am working with

ID Name Add1 Add2
1 Matt 16 Nowhere St Glasgow
1 Matt 16 Nowhere St Glasgow, Scotland
2 Jim 23 Blue St G65 TX
3 Bill 45 Red St
3 Bill 45 red St London

The problem is that a user can have one or more addresses!! I would like to be able to remove the duplicates by keeping the first duplicate ID that appears and getting rid of the second one. Any ideas?

Cheers

perhaps a quicker way of doing it than your cursor would be:

declare @.temp_name_addr TABLE (
id int not null,
name char(n) not null,
add1 char(n) null,
add2 char(n) null
)

insert @.temp_name_addr (id, name)
select distinct id, name
from permanent_table

update T
set T.add1 = P.add1,
T.add2 = P.add2
from @.temp_name_addr T,
permanent_table P
where T.id = P.id

select * from @.temp_name_addr

...of course which address you get will be arbitrary (both in your solution and mine) there's no rigid "first" without an order by clause...sql

remove double value

Hy
Never use/practice SQL a lot, (vb... more, have free msde 2000) .
2 questions
A)is it simple to write a T-SQL query for having 2) at result starting
from 1) .
B)how to test dynamically sql with parmaeter ( using vb ADO)

1) before query
columA columB
d e <-same
e d <-same
e e
e d <-same

2)after query
columA columB
d e or e d
e e> A)is it simple to write a T-SQL query for having 2) at result starting
> from 1) .

SELECT DISTINCT
COALESCE(S2.a,S1.a) AS a,
COALESCE(S2.b,S1.b) AS b
FROM Sometable AS S1
LEFT JOIN Sometable AS S2
ON S1.a = S2.b AND S1.b = S2.a AND S2.a < S2.b

> B)how to test dynamically sql with parmaeter ( using vb ADO)

This may help:
http://msdn.microsoft.com/library/e...oprg02_6df7.asp
See also:
http://www.sommarskog.se/dyn-search.html

--
David Portas
----
Please reply only to the newsgroup
--|||David Portas wrote:
>>A)is it simple to write a T-SQL query for having 2) at result starting
>>from 1) .
>
> SELECT DISTINCT
> COALESCE(S2.a,S1.a) AS a,
> COALESCE(S2.b,S1.b) AS b
> FROM Sometable AS S1
> LEFT JOIN Sometable AS S2
> ON S1.a = S2.b AND S1.b = S2.a AND S2.a < S2.b
>
>>B)how to test dynamically sql with parmaeter ( using vb ADO)
>
> This may help:
> http://msdn.microsoft.com/library/e...oprg02_6df7.asp
> See also:
> http://www.sommarskog.se/dyn-search.html

hy
thank a lot for sharing your knowledge.
perhaps it was easy for you buit very hard for me
thank's for your quicky response

Remove default parameter

I have a report with default parameters set as ' ' i.e a blank value through a query.

when i deploy this report RS2005 adds a <select a value> as the text listed in the dropdown beacuase of which it does not take my default value and no display of report.

How can I remove this text being shown.

Thanks,

Kiran.

Do you have the "allow blank value" property set? Also, is your query set up to return rows if the parameter is nothing?

Simone

|||

Yes the parameter has allow blank value set. and it returns a different table with values if this parameter is kept blank.

Hence i want users to selcet blank if they do not want to consider this parameter and not just leave the parameter value as

<select a value>

Thanks,

Help appreciated.

Kiran

|||

It seems that if you have a list of available values you must also set a default value. I have a case where I allow no value to be selected but have a list of available values when necessary. To handle this I add an "All" option to my available values list and set this as the default. If this is the chosen value, then I return all records. If another value is filtered the recordset is chosen based on the selection.

Simone

|||

Hello,

So what i should do is if I ahve a list of values and a default value set to the parameter I remove teh allow blank value and that would remove the select a value.

would taht be correct?

thanks

|||

Hello,

I have defqault value for the parameter from the list of available values.

Also i have unchecked the allow blank and allow null text boxes. Still tehre is the <select a value> in the parameter drop-down.

What next.

Thanks

|||

In your query that selects the data, do you have anything such as this:

where column = case when @.variable = '' then column else @.variable end

This tells the query to pull all data when the variable value is blank, otherwise pull only the data matching the variable value. You then need to ensure you have '' as a valid choice in the "available values" section.

Simone

|||

Thanks Simone,

I do have conditions like AND @.Country=' ' AND @.Srv_Level=' '

but there is a value in the dropdown that is ' ' which is also set as default value for some of teh parameters.

the difference is that selecting ' ' does not mean that select all values as in the dropdown there is an all functionality too. Selecting a ' ' matches a specific case and gives values returning to that condition.

I am thinking this is a RS2005 addition as in the designer view do not see the <select a value> in teh parameter dropdowns

Thanks

|||

Maybe this will help you:

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

I misunderstood your intentions with the blank value. The same should be true with having the blank value as your default. If it is a valid selection in the list and selected as your default value, so long as all other parameters also have a default value, your report should run. I am using both "ALL" and "" as defaults and do not have the <select a value> in my lists. I have either the "" or "All". Good luck.

Simone

|||I have used 'ALL' and NULL to accomplish this task on reports I have written|||Hi,

Could you elaborate how using Null u manged to remove <select a value> I need a ' ' as a possible selection....
thanks
|||

I am using a SQL statement to return my list of available values. In the statement I add a UNION to include the '' default.

ex.

Code Snippet

Select FieldA

From TableA

UNION

Select '' as FieldA

Order by 1

I then set '' as the default value.

I hope this helps.

Simone

Remove default parameter

I have a report with default parameters set as ' ' i.e a blank value through a query.

when i deploy this report RS2005 adds a <select a value> as the text listed in the dropdown beacuase of which it does not take my default value and no display of report.

How can I remove this text being shown.

Thanks,

Kiran.

Do you have the "allow blank value" property set? Also, is your query set up to return rows if the parameter is nothing?

Simone

|||

Yes the parameter has allow blank value set. and it returns a different table with values if this parameter is kept blank.

Hence i want users to selcet blank if they do not want to consider this parameter and not just leave the parameter value as

<select a value>

Thanks,

Help appreciated.

Kiran

|||

It seems that if you have a list of available values you must also set a default value. I have a case where I allow no value to be selected but have a list of available values when necessary. To handle this I add an "All" option to my available values list and set this as the default. If this is the chosen value, then I return all records. If another value is filtered the recordset is chosen based on the selection.

Simone

|||

Hello,

So what i should do is if I ahve a list of values and a default value set to the parameter I remove teh allow blank value and that would remove the select a value.

would taht be correct?

thanks

|||

Hello,

I have defqault value for the parameter from the list of available values.

Also i have unchecked the allow blank and allow null text boxes. Still tehre is the <select a value> in the parameter drop-down.

What next.

Thanks

|||

In your query that selects the data, do you have anything such as this:

where column = case when @.variable = '' then column else @.variable end

This tells the query to pull all data when the variable value is blank, otherwise pull only the data matching the variable value. You then need to ensure you have '' as a valid choice in the "available values" section.

Simone

|||

Thanks Simone,

I do have conditions like AND @.Country=' ' AND @.Srv_Level=' '

but there is a value in the dropdown that is ' ' which is also set as default value for some of teh parameters.

the difference is that selecting ' ' does not mean that select all values as in the dropdown there is an all functionality too. Selecting a ' ' matches a specific case and gives values returning to that condition.

I am thinking this is a RS2005 addition as in the designer view do not see the <select a value> in teh parameter dropdowns

Thanks

|||

Maybe this will help you:

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

I misunderstood your intentions with the blank value. The same should be true with having the blank value as your default. If it is a valid selection in the list and selected as your default value, so long as all other parameters also have a default value, your report should run. I am using both "ALL" and "" as defaults and do not have the <select a value> in my lists. I have either the "" or "All". Good luck.

Simone

|||I have used 'ALL' and NULL to accomplish this task on reports I have written|||Hi,

Could you elaborate how using Null u manged to remove <select a value> I need a ' ' as a possible selection....
thanks
|||

I am using a SQL statement to return my list of available values. In the statement I add a UNION to include the '' default.

ex.

Code Snippet

Select FieldA

From TableA

UNION

Select '' as FieldA

Order by 1

I then set '' as the default value.

I hope this helps.

Simone

sql

remove cursor

hi below is my procedure..i don't want to use cursor....than what should be my query?

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

DECLARE @.Bank_Name VARCHAR(50)
DECLARE @.MICR_CMSCode varchar(50),@.MICR_SchemeGroup varchar(50)

--BANK CURSOR
DECLARE CUR_BANK CURSOR FOR
SELECT Bank_Name FROM Tbl_BankMst where Bank_isactive=1

OPEN CUR_BANK

FETCH NEXT FROM CUR_BANK INTO @.Bank_Name
WHILE @.@.FETCH_STATUS=0
BEGIN

--print(@.Bank_Name)
--MICR CURSOR
DECLARE CUR_MICR cursor for
--Select MICR_CMSCode,MICR_SchemeGroup From Tbl_MICRMst WHERE MICR_BankName='ICICI BANK LTD'
Select MICR_CMSCode,MICR_SchemeGroup From Tbl_MICRMst WHERE MICR_AuthStatus =2 and MICR_Optype =0 and MICR_BankName=rtrim(ltrim(@.Bank_Name))

Open CUR_MICR

Fetch Next from CUR_MICR into @.MICR_CMSCode,@.MICR_SchemeGroup
while @.@.fetch_status = 0
begin
update Tbl_CMS_UploadDetails set CMS_SchemeGroup =@.MICR_SchemeGroup Where Scheme_Code=rtrim(ltrim(@.MICR_CMSCode))

--print(@.MICR_SchemeGroup)--@.MICR_CMSCode
Fetch Next from CUR_MICR into @.MICR_CMSCode,@.MICR_SchemeGroup

end
close CUR_MICR
deallocate CUR_MICR

- update compare status and maker-

declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName
--select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name='ICICI BANK LTD'

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID
-
FETCH NEXT FROM CUR_BANK INTO @.Bank_Name
END

close CUR_BANK
deallocate CUR_BANK

hi try this..

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

update Tbl_CMS_UploadDetails
set CMS_SchemeGroup = d.MICR_SchemeGroup
from Tbl_CMS_UploadDetails INNER JOIN
(
Select a.MICR_CMSCode
, a.MICR_SchemeGroup
From Tbl_MICRMst a INNER JOIN
Tbl_BankMst b ON a.MICR_BankName = rtrim(ltrim(b.Bank_Name))
WHERE a.MICR_AuthStatus = 2
and a.MICR_Optype = 0
and b.Bank_isactive=1
--and a.MICR_BankName = rtrim(ltrim(@.Bank_Name))
) d
where Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID

END
GO|||oops, i forgot to add the on clause..

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

update Tbl_CMS_UploadDetails
set CMS_SchemeGroup = d.MICR_SchemeGroup
from Tbl_CMS_UploadDetails INNER JOIN
(
Select a.MICR_CMSCode
, a.MICR_SchemeGroup
From Tbl_MICRMst a INNER JOIN
Tbl_BankMst b ON a.MICR_BankName = rtrim(ltrim(b.Bank_Name))
WHERE a.MICR_AuthStatus = 2
and a.MICR_Optype = 0
and b.Bank_isactive=1
--and a.MICR_BankName = rtrim(ltrim(@.Bank_Name))
) d ON Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
where Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID

END
GO|||thanx let me try i will be back.sql

Remove <select a value> option

I have a parameter that get his values from a query.
his defualt value is Null.
In the report page, The first value is "<select a value>".
even if I give the parameter another defualt value Then it open with
this value selected but still the first value is "<select a value>".
Can I remove this?
Thanks.On Feb 25, 12:23 am, "nicknack" <roezo...@.gmail.com> wrote:
> I have a parameter that get his values from a query.
> his defualt value is Null.
> In the report page, The first value is "<select a value>".
> even if I give the parameter another defualt value Then it open with
> this value selected but still the first value is "<select a value>".
> Can I remove this?
> Thanks.
Please refer to the response I posted on the other Reporting Services
group.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Where is the other reporting services group..I can't find it.
Cheers
D
"EMartinez" wrote:
> On Feb 25, 12:23 am, "nicknack" <roezo...@.gmail.com> wrote:
> > I have a parameter that get his values from a query.
> > his defualt value is Null.
> >
> > In the report page, The first value is "<select a value>".
> > even if I give the parameter another defualt value Then it open with
> > this value selected but still the first value is "<select a value>".
> >
> > Can I remove this?
> >
> > Thanks.
> Please refer to the response I posted on the other Reporting Services
> group.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>

Remotely connecting sql Server

Hi,
I'm Connecting to server on the internet. When I connect with the SQL Query analyzer it works fine.
but when I open the Enterprise Manager & Click on the database then It loads all the databases from the server. & it takes a lot of time. What I want that to load only specific databases or any other method by which I can improve its performance.
I hope you get that.
thanks in advance,
Das
I don't think you can change that behavior, but you can certainly increase
the timeout value for Enterprise Manager. For more informaiton, see:
http://vyaskn.tripod.com/sql_server_tools_faq.htm#q7
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Das" <anonymous@.discussions.microsoft.com> wrote in message
news:A50CF25F-A767-4BA9-BE5E-5E046346F362@.microsoft.com...
Hi,
I'm Connecting to server on the internet. When I connect with the SQL Query
analyzer it works fine.
but when I open the Enterprise Manager & Click on the database then It
loads all the databases from the server. & it takes a lot of time. What I
want that to load only specific databases or any other method by which I can
improve its performance.
I hope you get that.
thanks in advance,
Das

Remote tables

Hi everybody:

I am working on a query that referrences a table in a remote database. It seems in Management Studio 2005 anytime you open a new query window you should connect to a specific database instance that's why when I refer to the remote database table using Fully Qualified Name it tells me the database name is unknown.

Do you have any solutions for this?

Thanks a lot

Which qualified name did you use ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

[server].[database].[owner].[table]

|||Hi,

if you connect to a database / server you can reach any (linked) server and therefore remote database that are setup on the server machine. You don′t need to specify the remoteserver at connection time, that why you can specify it the four part name:

[server].[database].[owner].[table]

But the [server] has to be a linked server. If you don′t know how to setup, look in the BOl there are some good straight forward examples for it.

HTH, jens Suessmeyer.

http//www.sqlserver2005.de

|||

You can try with OPENROWSET or OPENQUERY function. To do this you need to enable this features from "SQL Server Surface Area Configuration" - Ad Hoc Remote Queries (you need to check "Enable OPENROWSET and OPENDATASOURCE). After you enable this option you can use this function to retrieve data from another server or to get data from another format (including MS Excel - for example).

SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;', 'SELECT GroupName, Name, DepartmentID FROM AdventureWorks.HumanResources.Department ORDER BY GroupName, Name') AS a;

remote sql query

we are trying to execute a remote sql query from one sql server against
another. Both servers are in the same domain. The query is a simple select
* from tablename where surname = whatever. We precede this with an
'opendatasource' statement. The query returns only a few rows. When we run
the query between two sql servers on the same lan segment the query runs in a
second or two. When we run the query between servers on two different
network segments (seperated by a firewall) but still both in the same domain
the same query takes 50+ seconds to run. Looking further, it seems that the
query is actually being executed on the local server so the entire table
(over 1 million rows) is copied locally before running the query. How can I
run the query as a remote query so that the entire query is run on the remote
sql server and only the query results are passed accross the network to the
local server? I have also tried creating a linked server and using OPENQUERY
but still takes ages to run.
Thanks
Open query should pass the query to the other server for execution. Are you
sure you are not doing a JOIN to a local table? In ay case you can create a
stored procedure on the remote server and execute that.
Andrew J. Kelly SQL MVP
"lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
> we are trying to execute a remote sql query from one sql server against
> another. Both servers are in the same domain. The query is a simple
> select
> * from tablename where surname = whatever. We precede this with an
> 'opendatasource' statement. The query returns only a few rows. When we
> run
> the query between two sql servers on the same lan segment the query runs
> in a
> second or two. When we run the query between servers on two different
> network segments (seperated by a firewall) but still both in the same
> domain
> the same query takes 50+ seconds to run. Looking further, it seems that
> the
> query is actually being executed on the local server so the entire table
> (over 1 million rows) is copied locally before running the query. How can
> I
> run the query as a remote query so that the entire query is run on the
> remote
> sql server and only the query results are passed accross the network to
> the
> local server? I have also tried creating a linked server and using
> OPENQUERY
> but still takes ages to run.
> Thanks
|||Perhaps it's the location of the WHERE clause? If you say
SELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees')
WHERE EmployeeID > 5
You are asking for all the rows and then you will apply the where clause
locally.
If you saySELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees WHERE EmployeeID > 5')
You are sending the where clause to the remote server - a good thing, and
only getting the qualified rows returned to the local server.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OB7ad5%23KGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Open query should pass the query to the other server for execution. Are
> you sure you are not doing a JOIN to a local table? In ay case you can
> create a stored procedure on the remote server and execute that.
>
> --
> Andrew J. Kelly SQL MVP
>
> "lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
> message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
>

remote sql query

we are trying to execute a remote sql query from one sql server against
another. Both servers are in the same domain. The query is a simple select
* from tablename where surname = whatever. We precede this with an
'opendatasource' statement. The query returns only a few rows. When we run
the query between two sql servers on the same lan segment the query runs in
a
second or two. When we run the query between servers on two different
network segments (seperated by a firewall) but still both in the same domain
the same query takes 50+ seconds to run. Looking further, it seems that the
query is actually being executed on the local server so the entire table
(over 1 million rows) is copied locally before running the query. How can I
run the query as a remote query so that the entire query is run on the remot
e
sql server and only the query results are passed accross the network to the
local server? I have also tried creating a linked server and using OPENQUER
Y
but still takes ages to run.
ThanksOpen query should pass the query to the other server for execution. Are you
sure you are not doing a JOIN to a local table? In ay case you can create a
stored procedure on the remote server and execute that.
Andrew J. Kelly SQL MVP
"lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
> we are trying to execute a remote sql query from one sql server against
> another. Both servers are in the same domain. The query is a simple
> select
> * from tablename where surname = whatever. We precede this with an
> 'opendatasource' statement. The query returns only a few rows. When we
> run
> the query between two sql servers on the same lan segment the query runs
> in a
> second or two. When we run the query between servers on two different
> network segments (seperated by a firewall) but still both in the same
> domain
> the same query takes 50+ seconds to run. Looking further, it seems that
> the
> query is actually being executed on the local server so the entire table
> (over 1 million rows) is copied locally before running the query. How can
> I
> run the query as a remote query so that the entire query is run on the
> remote
> sql server and only the query results are passed accross the network to
> the
> local server? I have also tried creating a linked server and using
> OPENQUERY
> but still takes ages to run.
> Thanks|||Perhaps it's the location of the WHERE clause? If you say
SELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees')
WHERE EmployeeID > 5
You are asking for all the rows and then you will apply the where clause
locally.
If you saySELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees WHERE EmployeeID > 5')
You are sending the where clause to the remote server - a good thing, and
only getting the qualified rows returned to the local server.
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OB7ad5%23KGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Open query should pass the query to the other server for execution. Are
> you sure you are not doing a JOIN to a local table? In ay case you can
> create a stored procedure on the remote server and execute that.
>
> --
> Andrew J. Kelly SQL MVP
>
> "lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
> message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
>

Remote server query

I am getting the following error:
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [DataDirect][ODBC Sybase Wire Protocol driver]Insufficient information to connect to the data source.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

Any idea?

Thanks, YBWhat code created this error? (Your connection string would be helpful).|||I am using the sp_addlinkedserver stored procedure and then trying to do an open query.
Here is the code below.

sp_addlinkedserver 'ABCDE','','MSDASQL','ABCDE',null, 'UID=username;PWD=passwd;Database=database;
Select * into Reports.dbo.Results from openquery(ABCDE, 'select * from Result');

Thanks.|||Try breaking the database off of the @.provstr parameter, and setting that in the @.catalog parameter.

Remote server Error...

Hi there. I'm getting the following error when trying to run a query on a
remote server I've set up:
Server 'SERVER_1' is not configured for DATA ACCESS.
I know this option is available for Linked Servers but I dont know why I'm
seeing this problem with a remote server. The Remote Server I've created has
RPC enabled ans is using a valid server name and login.
Any ideas?...Try executing:
exec sp_serveroption 'Server_1', 'data access', 'true'
-Sue
On Wed, 30 Mar 2005 09:49:03 -0800, len
<len@.discussions.microsoft.com> wrote:

>Hi there. I'm getting the following error when trying to run a query on a
>remote server I've set up:
>Server 'SERVER_1' is not configured for DATA ACCESS.
>I know this option is available for Linked Servers but I dont know why I'm
>seeing this problem with a remote server. The Remote Server I've created ha
s
>RPC enabled ans is using a valid server name and login.
>Any ideas?...

Remote Query results

Folks,
Can anyone help me with getting results of a query that I'm trying to
execute on a linked server into a local table? I'm trying to avoid the
DTS package as I will have to create the DTS package on every single
linked server.
I have quite a few servers that I want to execute a proc on and store
the results of all of them into one central database table.
ThanksThis is a multi-part message in MIME format.
--000502000604040203080207
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
BS wrote:
> Folks,
> Can anyone help me with getting results of a query that I'm trying to
> execute on a linked server into a local table? I'm trying to avoid the
> DTS package as I will have to create the DTS package on every single
> linked server.
> I have quite a few servers that I want to execute a proc on and store
> the results of all of them into one central database table.
> Thanks
>
Hi
There are as such no difference in inserting data from a linked server
into a local table than doing it with data from the local server. You
can use INSERT INTO or SELECT INTO. Since you don't tell anything about
the stored proc you want to run, it's difficult to to know what it
returns, but you can look up the syntax for INSERT INTO and SELECT..INTO
in Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--000502000604040203080207
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
BS wrote:
<blockquote
cite="mid1154381185.831914.162800@.m73g2000cwd.googlegroups.com"
type="cite">
<pre wrap="">Folks,
Can anyone help me with getting results of a query that I'm trying to
execute on a linked server into a local table? I'm trying to avoid the
DTS package as I will have to create the DTS package on every single
linked server.
I have quite a few servers that I want to execute a proc on and store
the results of all of them into one central database table.
Thanks
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi<br>
<br>
There are as such no difference in inserting data from a linked server
into a local table than doing it with data from the local server. You
can use INSERT INTO or SELECT INTO. Since you don't tell anything about
the stored proc you want to run, it's difficult to to know what it
returns, but you can look up the syntax for INSERT INTO and
SELECT..INTO in Books On Line.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--000502000604040203080207--

Remote Query results

Folks,
Can anyone help me with getting results of a query that I'm trying to
execute on a linked server into a local table? I'm trying to avoid the
DTS package as I will have to create the DTS package on every single
linked server.
I have quite a few servers that I want to execute a proc on and store
the results of all of them into one central database table.
ThanksBS wrote:
> Folks,
> Can anyone help me with getting results of a query that I'm trying to
> execute on a linked server into a local table? I'm trying to avoid the
> DTS package as I will have to create the DTS package on every single
> linked server.
> I have quite a few servers that I want to execute a proc on and store
> the results of all of them into one central database table.
> Thanks
>
Hi
There are as such no difference in inserting data from a linked server
into a local table than doing it with data from the local server. You
can use INSERT INTO or SELECT INTO. Since you don't tell anything about
the stored proc you want to run, it's difficult to to know what it
returns, but you can look up the syntax for INSERT INTO and SELECT..INTO
in Books On Line.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator

Remote Query Performance

I have a query that when executed against the host server, it completes in 1
1
seconds. When I execute the same query against a linked server it takes 100
seconds to run. I have checked the execution plan for the linked server
query and it shows that 100% of the cost is the Remote Query. The resultset
is only 260 rows and 5 columns of data. Both servers are SQL2000 and Window
s
2000 OS.
Since both queries ran from query analyzer on my workstation, why is there
such a drastic difference in speed? Also, how do I fix the performance issu
e
for the remote query so that it runs in 15 seconds or less?Brandon Lunt wrote:
> I have a query that when executed against the host server, it completes in
11
> seconds. When I execute the same query against a linked server it takes 1
00
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The results
et
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and Wind
ows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance is
sue
> for the remote query so that it runs in 15 seconds or less?
>
The resultset is 260 rows, but how many rows are in the base table?
What indexes are available? Such a query is going to be slower by
default simply because of the network, but often the query engine can't
determine an "optimal" execution plan for a remote query, and will end
up pulling an entire table across the network, and then filtering the
results on the local side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The base table has aprox 3 Million records. The servers are communicating o
n
a Gigabit network. I am connected to the network with a 100 Megabit
connection. Why would SQL pull the table over the network if the query plan
shows that it was executed remotely?
"Tracy McKibben" wrote:

> Brandon Lunt wrote:
> The resultset is 260 rows, but how many rows are in the base table?
> What indexes are available? Such a query is going to be slower by
> default simply because of the network, but often the query engine can't
> determine an "optimal" execution plan for a remote query, and will end
> up pulling an entire table across the network, and then filtering the
> results on the local side.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
>I have a query that when executed against the host server, it completes in
>11
> seconds. When I execute the same query against a linked server it takes
> 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The
> resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance
> issue
> for the remote query so that it runs in 15 seconds or less?
>
Can you run it through OPENQUERY? This would pass the query text to the
linked server, and just return you the results.
David|||Brandon Lunt wrote:
> The base table has aprox 3 Million records. The servers are communicating
on
> a Gigabit network. I am connected to the network with a 100 Megabit
> connection. Why would SQL pull the table over the network if the query pl
an
> shows that it was executed remotely?
>
The remote "query" is simply indicating that "something" was done on the
remote side. As David suggested, try using OPENQUERY, that will
guarantee that the query is executed on the remote side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I used the following syntax to run the openquery
select * from openquery([linkedserver], 'query string')
same results, 1:37 elapsed time.
"David Browne" wrote:

> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
> Can you run it through OPENQUERY? This would pass the query text to the
> linked server, and just return you the results.
> David
>
>

Remote Query Performance

I have a query that when executed against the host server, it completes in 11
seconds. When I execute the same query against a linked server it takes 100
seconds to run. I have checked the execution plan for the linked server
query and it shows that 100% of the cost is the Remote Query. The resultset
is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
2000 OS.
Since both queries ran from query analyzer on my workstation, why is there
such a drastic difference in speed? Also, how do I fix the performance issue
for the remote query so that it runs in 15 seconds or less?First, why are you using linked servers. In most cases this is not necessary
with RS. You can have multiple datasets against multiple data sources.
SQL 2000 can be very very bad with linked servers. SQL 2005 is much better.
I learned this while working with loading a datamart. If you must use linked
servers then you need to be using Openquery to use it. Do not use the 4 part
naming.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
>I have a query that when executed against the host server, it completes in
>11
> seconds. When I execute the same query against a linked server it takes
> 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The
> resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance
> issue
> for the remote query so that it runs in 15 seconds or less?|||The report I am producing combines data from 3 different servers. I
typically use linked servers so I don't have to put usernames and passwords
into my query strings (not all users have access to all databases so I have
to use a different account). I have usually had good results with Linked
servers (slight performance hit but never this bad). The other linked query
(to the 3rd server) runs in about 2 seconds and returns approximately the
same number or results.
My confusion I guess is why does it not run the same as a query analyzer
client connecting to the server?
"Bruce L-C [MVP]" wrote:
> First, why are you using linked servers. In most cases this is not necessary
> with RS. You can have multiple datasets against multiple data sources.
> SQL 2000 can be very very bad with linked servers. SQL 2005 is much better.
> I learned this while working with loading a datamart. If you must use linked
> servers then you need to be using Openquery to use it. Do not use the 4 part
> naming.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
> >I have a query that when executed against the host server, it completes in
> >11
> > seconds. When I execute the same query against a linked server it takes
> > 100
> > seconds to run. I have checked the execution plan for the linked server
> > query and it shows that 100% of the cost is the Remote Query. The
> > resultset
> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> > Windows
> > 2000 OS.
> >
> > Since both queries ran from query analyzer on my workstation, why is there
> > such a drastic difference in speed? Also, how do I fix the performance
> > issue
> > for the remote query so that it runs in 15 seconds or less?
>
>|||Are you doing this in a stored procedure?
If not, I have seen people post about issues where the query plan created
when a query is executed from RS is different than from query analyzer. As I
said, I haven't seen this but I have seen posts about that.
If you are using a stored procedure then that would not be an issue. If not,
try moving this to a stored procedure and see if that helps.
I have thought of one other thing. Depending on the parameterization, I have
seen in 2000 based on the parameters and the where clause where SQL Server
will decide to bring over all the data from the remote table and process it
locally on the server rather than having the query executed remotely and
bringing back the result. Based on 11 seconds when hitting the server
directly going to 100 seconds, I bet that is what is happening. If you use
openquery this will not occur. With 4 part naming you have to really be
careful.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
> The report I am producing combines data from 3 different servers. I
> typically use linked servers so I don't have to put usernames and
> passwords
> into my query strings (not all users have access to all databases so I
> have
> to use a different account). I have usually had good results with Linked
> servers (slight performance hit but never this bad). The other linked
> query
> (to the 3rd server) runs in about 2 seconds and returns approximately the
> same number or results.
> My confusion I guess is why does it not run the same as a query analyzer
> client connecting to the server?
> "Bruce L-C [MVP]" wrote:
>> First, why are you using linked servers. In most cases this is not
>> necessary
>> with RS. You can have multiple datasets against multiple data sources.
>> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
>> better.
>> I learned this while working with loading a datamart. If you must use
>> linked
>> servers then you need to be using Openquery to use it. Do not use the 4
>> part
>> naming.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
>> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
>> >I have a query that when executed against the host server, it completes
>> >in
>> >11
>> > seconds. When I execute the same query against a linked server it
>> > takes
>> > 100
>> > seconds to run. I have checked the execution plan for the linked
>> > server
>> > query and it shows that 100% of the cost is the Remote Query. The
>> > resultset
>> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
>> > Windows
>> > 2000 OS.
>> >
>> > Since both queries ran from query analyzer on my workstation, why is
>> > there
>> > such a drastic difference in speed? Also, how do I fix the performance
>> > issue
>> > for the remote query so that it runs in 15 seconds or less?
>>|||Currently this is in query form. I tried the openquery and got the same
results as the 4 part linked query. I also tried the openrowset and the time
came down to 57 seconds, but nowhere near the 10-15 seconds I would expect.
Also, the problem with openquery is that I need to pass parameters used by
the query. I didn't see a way to get those dynamically into the query.
I went ahead and created the sp and executed that against the linked server
and it ran in 50 seconds. Better, but still not what I was expecting.
"Bruce L-C [MVP]" wrote:
> Are you doing this in a stored procedure?
> If not, I have seen people post about issues where the query plan created
> when a query is executed from RS is different than from query analyzer. As I
> said, I haven't seen this but I have seen posts about that.
> If you are using a stored procedure then that would not be an issue. If not,
> try moving this to a stored procedure and see if that helps.
> I have thought of one other thing. Depending on the parameterization, I have
> seen in 2000 based on the parameters and the where clause where SQL Server
> will decide to bring over all the data from the remote table and process it
> locally on the server rather than having the query executed remotely and
> bringing back the result. Based on 11 seconds when hitting the server
> directly going to 100 seconds, I bet that is what is happening. If you use
> openquery this will not occur. With 4 part naming you have to really be
> careful.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
> > The report I am producing combines data from 3 different servers. I
> > typically use linked servers so I don't have to put usernames and
> > passwords
> > into my query strings (not all users have access to all databases so I
> > have
> > to use a different account). I have usually had good results with Linked
> > servers (slight performance hit but never this bad). The other linked
> > query
> > (to the 3rd server) runs in about 2 seconds and returns approximately the
> > same number or results.
> >
> > My confusion I guess is why does it not run the same as a query analyzer
> > client connecting to the server?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> First, why are you using linked servers. In most cases this is not
> >> necessary
> >> with RS. You can have multiple datasets against multiple data sources.
> >>
> >> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
> >> better.
> >> I learned this while working with loading a datamart. If you must use
> >> linked
> >> servers then you need to be using Openquery to use it. Do not use the 4
> >> part
> >> naming.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> >> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
> >> >I have a query that when executed against the host server, it completes
> >> >in
> >> >11
> >> > seconds. When I execute the same query against a linked server it
> >> > takes
> >> > 100
> >> > seconds to run. I have checked the execution plan for the linked
> >> > server
> >> > query and it shows that 100% of the cost is the Remote Query. The
> >> > resultset
> >> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> >> > Windows
> >> > 2000 OS.
> >> >
> >> > Since both queries ran from query analyzer on my workstation, why is
> >> > there
> >> > such a drastic difference in speed? Also, how do I fix the performance
> >> > issue
> >> > for the remote query so that it runs in 15 seconds or less?
> >>
> >>
> >>
>
>|||The only way to get a parameter to openquery (that I know of) is to
dynamically create the sql string. You end up having to do lots of messing
with single quotes.
In your stored procedure, are you able to insert into a temp table the
results from each individual query and then join the temp tables?
Also, just so you can see how it is done, here is a an example of
dynamically
select @.SQL = 'insert ' + @.TABLENAME + ' select * from
openquery(linkedservername,''' + 'SELECT * from ' + @.TABLENAME + ' where ' +
@.SYNCDATEFIELD + '> '' + convert(varchar(30),@.STARTDATE,9) + '' and '
+ @.SYNCDATEFIELD +' < '' + convert(varchar(30),@.ENDDATE,9) + '')'
execute (@.SQL)
Note all the single quotes mess. Not too friendly but it is the fastest way
to work with linked tables. In the above I am inserting into a real table
but you could easily have a temp table created that you insert into.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:EDE9A4A4-0992-4B9B-ACA3-2E6A0FB1645B@.microsoft.com...
> Currently this is in query form. I tried the openquery and got the same
> results as the 4 part linked query. I also tried the openrowset and the
> time
> came down to 57 seconds, but nowhere near the 10-15 seconds I would
> expect.
> Also, the problem with openquery is that I need to pass parameters used by
> the query. I didn't see a way to get those dynamically into the query.
> I went ahead and created the sp and executed that against the linked
> server
> and it ran in 50 seconds. Better, but still not what I was expecting.
> "Bruce L-C [MVP]" wrote:
>> Are you doing this in a stored procedure?
>> If not, I have seen people post about issues where the query plan created
>> when a query is executed from RS is different than from query analyzer.
>> As I
>> said, I haven't seen this but I have seen posts about that.
>> If you are using a stored procedure then that would not be an issue. If
>> not,
>> try moving this to a stored procedure and see if that helps.
>> I have thought of one other thing. Depending on the parameterization, I
>> have
>> seen in 2000 based on the parameters and the where clause where SQL
>> Server
>> will decide to bring over all the data from the remote table and process
>> it
>> locally on the server rather than having the query executed remotely and
>> bringing back the result. Based on 11 seconds when hitting the server
>> directly going to 100 seconds, I bet that is what is happening. If you
>> use
>> openquery this will not occur. With 4 part naming you have to really be
>> careful.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
>> news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
>> > The report I am producing combines data from 3 different servers. I
>> > typically use linked servers so I don't have to put usernames and
>> > passwords
>> > into my query strings (not all users have access to all databases so I
>> > have
>> > to use a different account). I have usually had good results with
>> > Linked
>> > servers (slight performance hit but never this bad). The other linked
>> > query
>> > (to the 3rd server) runs in about 2 seconds and returns approximately
>> > the
>> > same number or results.
>> >
>> > My confusion I guess is why does it not run the same as a query
>> > analyzer
>> > client connecting to the server?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> First, why are you using linked servers. In most cases this is not
>> >> necessary
>> >> with RS. You can have multiple datasets against multiple data sources.
>> >>
>> >> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
>> >> better.
>> >> I learned this while working with loading a datamart. If you must use
>> >> linked
>> >> servers then you need to be using Openquery to use it. Do not use the
>> >> 4
>> >> part
>> >> naming.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
>> >> >I have a query that when executed against the host server, it
>> >> >completes
>> >> >in
>> >> >11
>> >> > seconds. When I execute the same query against a linked server it
>> >> > takes
>> >> > 100
>> >> > seconds to run. I have checked the execution plan for the linked
>> >> > server
>> >> > query and it shows that 100% of the cost is the Remote Query. The
>> >> > resultset
>> >> > is only 260 rows and 5 columns of data. Both servers are SQL2000
>> >> > and
>> >> > Windows
>> >> > 2000 OS.
>> >> >
>> >> > Since both queries ran from query analyzer on my workstation, why is
>> >> > there
>> >> > such a drastic difference in speed? Also, how do I fix the
>> >> > performance
>> >> > issue
>> >> > for the remote query so that it runs in 15 seconds or less?
>> >>
>> >>
>> >>
>>