Query across servers

I'm new to SQL and I'm trying to write a stored procdeure in my current database that queries a different database on a different server. What is the best way to do this?
Thanks and sorry if it's too remedial a question.you will need to set up a linked server. there is a great product documentation tool called Books Online under Start\Programs\Micorsoft SQL Server. Use the search feature for "linked server". You can then reference your the linked server using openquery or you can create a view that created with openquery. again see books online about openquery and views.|||You can use OpenDataSource. Check that from BOL.

SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories

Query Access & MS SQL

Are syntax of queries same in MS Access and MS SQL 2005?
I am asking because I have lot of queries in Access database. Do I have to
change every query when I move to MS SQL 2005?
Thanks!!!Hi
No. Most will work the same, but certain Access specific implementations of
queries will not.
If a query does not work with SQL Server 2000, it won't in SQL Server 2005.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <john713@.hotmail.com> wrote in message
news:ddkllt$f2g$1@.ss405.t-com.hr...
> Are syntax of queries same in MS Access and MS SQL 2005?
> I am asking because I have lot of queries in Access database. Do I have to
> change every query when I move to MS SQL 2005?
> Thanks!!!
>|||Some basic queries might be portable between MS Access and SQL Server but if
you've used stuff like most of the VBA functions or some of the non-standard
SQL elements of Access then they'll need some work. If you are porting an
application to SQL Server then to get the most out of the platform you
should aim to convert your queries and other data-access code into SQL
stored procedures. That'll almost certainly involve a significant re-write
of your app. How far you need to go will depend partly on what you want to
gain from switching platforms.
Also, if your data model is of more than trivial complexity you should
certainly review it when upsizing. Many people use data models in Access
that are poorly normalized. These are OK in Access because Access often lets
you do non-relational stuff in the database to cope with problems like
missing keys or redundant data. In SQL Server you are more likely to have
serious problems with a weak data model. Of course, you may already be
totally confident that your data is strictly Third Normal Form, which will
give you a head start.
Hope this helps.
David Portas
SQL Server MVP
--|||Thanks David,
there are no VBA elements, because I am using VB 6.0 and Access as database,
but some queries are not in VB code, but in Access database, like cross tab
queries.|||John:
Cross-tab queries in Access have no equivalent in SQL Server. Also, as was
mentioned before by others in this thread, VB functions in Access won't work
in SQL.
You may want to check out the following: "Microsoft Access Developer's Guide
to SQL Server" by Mary Chipman and Andy Baron (ISBN 0-672-31944-6), availabl
e
at Amazon.com for under $10 used.
Going through an upgrade from MDB to ADP/SQL I learned to watch for the
"Now()" function in many Access queries, which were replaced with
"GETDATE()". You will need to see what functions are in use.
Good luck.
Toddsql

Query

Greetings,

I have three queries that I have to put together and they are based on the SELECT and GROUP BY.

1st Query requirement:
group the Employees by Job Code. Use a SELECT query.and modify the SQL

My answer is (and I'm not able to see the actual names and job codes):
SELECT 'EmployeeID' AS EmployeeID, 'FirstName' AS FirstName, 'LastName' AS LastName, 'JobTitleCode' AS JobTitleCode
FROM Employees_Table
GROUP BY EmployeeID, FirstName, LastName, JobTitleCode
ORDER BY JobTitleCode;

2nd Query requirement:
group the Employees by Salary. Use a SELECT Query and modify the SQL

My answer is (and very similar to the above, I'm not seeing the information):
SELECT 'Employee ID' AS EmployeeID, 'FirstName' AS FirstName, 'LastName' AS LastName, 'Salary' AS Salary
FROM Employees_Table
GROUP BY EmployeeID, FirstName, LastName, [Salary]
ORDER BY [Salary];

Last Query requirement:
group the Employees by Salary within their Job Code. Use a SELECT Query and modify the SQL

My rough draft answer (not really sure at this point :-( is:
SELECT 'First Name' AS FirstName, 'Last Name' AS LastName, 'Salary' AS Salary
FROM Employees_Table
GROUP BY 'Salary', 'Job Title Code';

Any words of wisdom would be greatly appreciated.

Thanks for your time!Looks like a homework...Are you sure the fieldnames are spelled correctly? I don't think your queries will run...

1st - ...will give an error on invalid field name
2nd - ...will do the same
3rd - ...same difference

But conceptually ... I don't think you are answering the questions. You need to loose GROUP BY and reverse the order of the fields in ORDER BY clause.|||Yes, the field names in my database are spelled the same exact way as I've listed them in the SQL statements?

Any other words of advice?

Thanks for your time.|||Yes, the field names in my database are spelled the same exact way as I've listed them in the SQL statements?

Any other words of advice?

Thanks for your time.
So, you're saying that 'EmployeeID' and 'Employee ID' are the same field or 2 different fields?|||You are correct and I've modified to the new statements below:

Query 1:
SELECT EmployeeID, FirstName, JobTitleCode
FROM Employees_Table
GROUP BY EmployeeID, FirstName, JobTitleCode
ORDER BY JobTitleCode;

Query 2:
SELECT EmployeeID, FirstName, [Salary]
FROM Employees_Table
WHERE Salary>45000
GROUP BY EmployeeID, FirstName, [Salary]
ORDER BY [Salary];

I hope I'm going in the correct direction with my answers?

Thanks for your responses!|||See my first post (loose GROUP BY, because you're not doing any aggregation)sql

Query

Guys,
New to SQL programming and would like to know can we use the result of
2 queries in one?
Eg,
Parts as a query
Spares as a query
Can I use the results of these 2 queries
SELECT parts.*, spares.*
FROM Parts, Spares
can you pls help?
Thanks in advance
rgds
tdYour question is rather vague so only a general answer is possible. I think
you will want to use either a UNION or a JOIN. The example query you posted
is actually a CROSS JOIN, which probably isn't what you intended but that
depends what you mean by "use the result of 2 queries".
If you need more help then please read the following article, which explains
how you can best describe your problem for the group:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

Quering Fact table using MDX Queries

Is there any way that I can query the Fact table using the MDX queries. What
I want to do is to find whether a record exists in the fact table at a
particular level.
Thanks
Akber.
That would be the Drillthrough command (Docd in books on line)...Below is
the example from BOL
DRILLTHROUGH
SELECT [Warehouse].[All Warehouses].[Canada].[BC] ON ROWS,
[Time].[1998].[Q1] ON COLUMNS,
[Product].[All Products].[Drink] ON PAGES,
[Measures].[Units Shipped] ON SECTIONS
FROM [My Cube]
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Akber" <Akber@.discussions.microsoft.com> wrote in message
news:B1B53F76-5677-4176-A03D-FF8B19F65CF1@.microsoft.com...
> Is there any way that I can query the Fact table using the MDX queries.
What
> I want to do is to find whether a record exists in the fact table at a
> particular level.
> --
> Thanks
> Akber.

Queries??

I'm confused.

I am creating a interaction application for alton towers available through a website (using asp.net). This website is connected to a database(sql server 2000) containing infomation on different shops, rides, restaurants and facilities at the park.

I have inputted data to my database. The user should be able to do searches such as:
rides (and shops or all) available in a certain area.
rides in a certain area above a certain height rescrictions.
etc

I'm confused. I have used SQL statements before and I know it is possible to retrieve this information from the database, but how should I go about this and how should I be storing this information so that I am able to call it from the front teir.

I have read up on views, stored procedures and triggers and I'm a bit lost. Should I be creating all the possible queries and then store them as a view (or stored procedure) I thought I would just have to write a sql statement but it seems a lot more confusing...

and what about triggers and user defined statements??

PLEASE help, I have a deadline in a week : (

THANK YOUOriginally posted by asbirpam
I have a deadline in a week : (

Not much time for QA, huh...

Use stored procedures

CREATE PROC mySproc99
@.key int
AS
SELECT Col_list* FROM myTable99 WHERE Key = myKey99**
GO

EXEC mySproc99 value
GO

* Supply the columns you need from the table

** Supply the name of the key colmn and the value you need to select...

Good luck...

Come back with more specific info if you need help

Queries with different results...

Hi,
I am getting a difference of '1' when I am running the queries which I am listing below...
1)
select count(distinct(I.order_ID)) as completedTotal
from T_INVOICES I, T_ORDERS O,T_STATUS S
where
O.order_Id = I.order_ID

and I.feeDate >= {d '2002-08-01'} and I.feeDate < {d '2002-09-01'}
AND client_ID NOT IN(14,693) AND NOT EXISTS(SELECT Order_ID FROM T_STATUS WHERE
order_id = O.order_ID AND StatusType_ID = 7)
__________________________________________________ __________
2)
select count(distinct(I.order_ID)) as completedTotal
from T_INVOICES I
LEFT OUTER JOIN T_ORDERS AS O ON (O.order_Id = I.order_ID)
LEFT OUTER JOIN T_STATUS AS S ON (s.Order_ID = O.order_id)

WHERE I.feeDate >= {d '2002-08-01'} and I.feeDate < {d '2002-09-01'}
AND client_ID NOT IN(14,693) AND NOT EXISTS(SELECT Order_ID FROM T_STATUS WHERE order_id = O.order_ID AND StatusType_ID = 7)

can anyone help inresolving this.

thanksIn your first query, table S is not joined to table O, resulting in a cross-join.

blindman|||could you say how to optimize the second query?|||Well, I would script it like this for clarity, but this is just my style (it looks better once you paste it without the line-wrapping):

select count(distinct(I.order_ID)) as completedTotal
from T_INVOICES
LEFT OUTER JOIN T_ORDERS on T_ORDERS.order_Id = T_INVOICES.order_ID
LEFT OUTER JOIN T_STATUS on T_STATUS.Order_ID = T_ORDERS.order_id
WHERE T_INVOICES.feeDate '2002-08-01' and T_INVOICES.feeDate < '2002-09-01'
AND client_ID NOT IN(14,693)
AND NOT EXISTS (SELECT Order_ID FROM T_STATUS WHERE order_id = T_ORDERS.order_ID AND StatusType_ID = 7)

I've never liked using short aliases for tables because I've never thought the time saved in typing was worth the cost in readability.

You can optimize your query by indexing the join columns and criteria columns: order_id, feeDate, client_ID, and StatusType, but it may not be necessary to index all of them to get good performance.

Make sure you understand how your NOT EXISTS clause is going to affect your output. It will exclude all invoice and order records where there is a status recrod = 7, even if there are additional associated status records that do not = 7. If you just want to exclude status records = 7 from your output, put your criteria in the join:

select count(distinct(I.order_ID)) as completedTotal
from T_INVOICES
LEFT OUTER JOIN T_ORDERS on T_ORDERS.order_Id = T_INVOICES.order_ID
LEFT OUTER JOIN T_STATUS on T_STATUS.Order_ID = T_ORDERS.order_id T_STATUS.StatusType_ID <> 7
WHERE T_INVOICES.feeDate '2002-08-01' and T_INVOICES.feeDate < '2002-09-01'
AND client_ID NOT IN(14,693)

Also, I don't know how you have your relationships set up, but by the way you have three tables joined on order_id I suspect you are violating database normalization guidelines. (Unless the orders table has a one-to-many relationship with both Invoices and Status tables.)

blindman|||Hi,
I am using the query,

select count(distinct(T_INVOICES.order_ID)) as completedTotal
from T_INVOICES
LEFT OUTER JOIN T_ORDERS on T_ORDERS.order_Id = T_INVOICES.order_ID
LEFT OUTER JOIN T_STATUS on T_STATUS.Order_ID = T_ORDERS.order_id and T_STATUS.StatusType_ID <> 7
where
T_INVOICES.feeDate >= {d '2002-08-01'} and T_INVOICES.feeDate < {d '2002-09-01'}
AND client_ID NOT IN(14,693)
__________________________________________________ ___

but i am getting the number so high of the actual. Actually I have to get '29' instead I am getting '46'. Can you explain it.

thanks,
siva|||Instead of:
select count(distinct(T_INVOICES.order_ID)) as completedTotal

try this:
select distinct T_INVOICES.order_ID

...in order to see what data is actually being counted. I'm not sure your count(distinct( syntax is going to work the way you are expecting it to.

Also, try this:
select count(distinct T_INVOICES.order_ID) as completedTotal

The extra parentheses you have aren't necessary and may be affected your results.

blindman|||still iam getting the same result...|||So how many rows were returned by "select distinct T_INVOICES.order_ID"? Were any of them duplicates? Were any there that weren't supposed to be there?

Your problem my be due to, or compounded by, the relationships established between your tables. What is the architecture of these three tables:

Invoices -> Orders -> Status?

or

Invoices <- Orders -> Status?

You may need to try building your query from scratch again, starting with a simple select from Invoices and then adding joins and criteria as you verify that you are getting the data you expect. Your query design has some subtle joins and criteria, but I can't tell if these are required to get the results you want, or if they are just coding issues to be cleaned up.

blindmansql

Queries with Dates

New to SQL.
I'm trying to include 2 dates in a query. They query works, but not
logically. I'm sure I'm missing something small.
When the query runs, it doesn't include dates that have times later than
12:00:00AM. How do I get rid of that?
SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.OrderDate,
dbo.Products.ProductName
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
Details].OrderID INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID =
dbo.Products.ProductID
WHERE (dbo.Orders.OrderDate >= @.ordDate) AND (dbo.Orders.OrderDate <=
@.ordDate2)This example should demonstrate.
DECLARE @.ordDate datetime
DECLARE @.ordDate2 datetime
SET @.ordDate '20060531' -- As no time is specified this will be interpreted
as '20060531 00:00'
SET @.ordDate2 '20060531 23:59:59'
To capture all data on the 31-May-2006 your WHERE clause would be :-
WHERE dbo.Orders.OrderDate BETWEEN @.ordDate AND @.ordDate2
HTH. Ryan
"Tony K" <king-tony2@.comcast.nospam.net> wrote in message
news:O%23IUoRFhGHA.4404@.TK2MSFTNGP05.phx.gbl...
> New to SQL.
> I'm trying to include 2 dates in a query. They query works, but not
> logically. I'm sure I'm missing something small.
> When the query runs, it doesn't include dates that have times later than
> 12:00:00AM. How do I get rid of that?
> SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID,
> dbo.Orders.OrderDate, dbo.Products.ProductName
> FROM dbo.Orders INNER JOIN
> dbo.[Order Details] ON dbo.Orders.OrderID =
> dbo.[Order Details].OrderID INNER JOIN
> dbo.Products ON dbo.[Order Details].ProductID =
> dbo.Products.ProductID
> WHERE (dbo.Orders.OrderDate >= @.ordDate) AND (dbo.Orders.OrderDate <=
> @.ordDate2)
>|||>SET @.ordDate2 '20060531 23:59:59'
To be strict about it there is a very small chance this will not work
because it ignores the milliseconds. But you can not simply add .999
to the end. Consider this query and results:
select 990, convert(datetime,'20060531 23:59:59.990') UNION ALL
select 991, convert(datetime,'20060531 23:59:59.991') UNION ALL
select 992, convert(datetime,'20060531 23:59:59.992') UNION ALL
select 993, convert(datetime,'20060531 23:59:59.993') UNION ALL
select 994, convert(datetime,'20060531 23:59:59.994') UNION ALL
select 995, convert(datetime,'20060531 23:59:59.995') UNION ALL
select 996, convert(datetime,'20060531 23:59:59.996') UNION ALL
select 997, convert(datetime,'20060531 23:59:59.997') UNION ALL
select 998, convert(datetime,'20060531 23:59:59.998') UNION ALL
select 999, convert(datetime,'20060531 23:59:59.999')
order by 1
-- --
990 2006-05-31 23:59:59.990
991 2006-05-31 23:59:59.990
992 2006-05-31 23:59:59.993
993 2006-05-31 23:59:59.993
994 2006-05-31 23:59:59.993
995 2006-05-31 23:59:59.997
996 2006-05-31 23:59:59.997
997 2006-05-31 23:59:59.997
998 2006-05-31 23:59:59.997
999 2006-06-01 00:00:00.000
As you can see, SQL Server doesn't quite work in milliseconds, and
23:59:59.999 resolves to the NEXT DAY. However, you have four choices
for the largest value for the same day, .995 through .998.
Roy Harvey
Beacon Falls, CT
On Wed, 31 May 2006 09:44:24 +0100, "Ryan"
<Ryan_Waight@.nospam.hotmail.com> wrote:

>This example should demonstrate.
>DECLARE @.ordDate datetime
>DECLARE @.ordDate2 datetime
>SET @.ordDate '20060531' -- As no time is specified this will be interpreted
>as '20060531 00:00'
>SET @.ordDate2 '20060531 23:59:59'
>To capture all data on the 31-May-2006 your WHERE clause would be :-
>WHERE dbo.Orders.OrderDate BETWEEN @.ordDate AND @.ordDate2|||Valid point, well made.
HTH. Ryan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:v7vq72haq4c3a3npo7krbl1qceb4emk7mc@.
4ax.com...
> To be strict about it there is a very small chance this will not work
> because it ignores the milliseconds. But you can not simply add .999
> to the end. Consider this query and results:
> select 990, convert(datetime,'20060531 23:59:59.990') UNION ALL
> select 991, convert(datetime,'20060531 23:59:59.991') UNION ALL
> select 992, convert(datetime,'20060531 23:59:59.992') UNION ALL
> select 993, convert(datetime,'20060531 23:59:59.993') UNION ALL
> select 994, convert(datetime,'20060531 23:59:59.994') UNION ALL
> select 995, convert(datetime,'20060531 23:59:59.995') UNION ALL
> select 996, convert(datetime,'20060531 23:59:59.996') UNION ALL
> select 997, convert(datetime,'20060531 23:59:59.997') UNION ALL
> select 998, convert(datetime,'20060531 23:59:59.998') UNION ALL
> select 999, convert(datetime,'20060531 23:59:59.999')
> order by 1
> -- --
> 990 2006-05-31 23:59:59.990
> 991 2006-05-31 23:59:59.990
> 992 2006-05-31 23:59:59.993
> 993 2006-05-31 23:59:59.993
> 994 2006-05-31 23:59:59.993
> 995 2006-05-31 23:59:59.997
> 996 2006-05-31 23:59:59.997
> 997 2006-05-31 23:59:59.997
> 998 2006-05-31 23:59:59.997
> 999 2006-06-01 00:00:00.000
> As you can see, SQL Server doesn't quite work in milliseconds, and
> 23:59:59.999 resolves to the NEXT DAY. However, you have four choices
> for the largest value for the same day, .995 through .998.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 31 May 2006 09:44:24 +0100, "Ryan"
> <Ryan_Waight@.nospam.hotmail.com> wrote:
>|||All these things is the reason I wrote http://www.karaszi.com/SQLServer/in...
ime.asp
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eiAcoeKhGHA.896@.TK2MSFTNGP02.phx.gbl...
> Valid point, well made.
> --
> HTH. Ryan
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:v7vq72haq4c3a3npo7krbl1qceb4emk7mc@.
4ax.com...
>|||On Wed, 31 May 2006 13:54:22 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>All these things is the reason I wrote http://www.karaszi.com/SQLServer/in...br />
time.asp
>
>:-)
Very nice. I've got to catch up on all the articles you guys wrote
since I was last around!
Roy|||Thank you everybody. I'll look at the website and write back if I have
other issues.
Thank you everybody!
Tony
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6t2s729hu1bdn521lbur5qea0ia4ct55a8@.
4ax.com...
> On Wed, 31 May 2006 13:54:22 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Very nice. I've got to catch up on all the articles you guys wrote
> since I was last around!
> Roy

Queries with "like" and full text indexes

Hi
I use ASPNET application using Sql Ser 2000 database. There are a lot of
queries using "like" statement.
I suffer bad performance of that application and I noticed in SQL Profiler
that those "like" queries takes a lot of time.
I heard about full text queries and I wonder if it could boost the queries
performance.
Please advise me if I'm right.
Best Regards
Darek T.Dariusz
Yes , if you use LIKE '%test%' so SQL Server probably will choose
TABLE/INDEX SCAN to perfom the query , however using LIKE 'test'% will
INDXEX/CLUSTERED INDEX SEEK. Well , obviously it depends on many things and
we don't know how do you run the queries?
I can't say that you are going to gain some benefits (in terms of
perfomance) of using FTI due to not using this feature for long time , so
maybe someone else can provide more info.
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:%23uECaDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Hi
> I use ASPNET application using Sql Ser 2000 database. There are a lot of
> queries using "like" statement.
> I suffer bad performance of that application and I noticed in SQL Profiler
> that those "like" queries takes a lot of time.
> I heard about full text queries and I wonder if it could boost the queries
> performance.
> Please advise me if I'm right.
>
> Best Regards
> Darek T.
>|||In most cases it will. If you have a restriction the restriction will be
applied after the results set comes back from the query of the full text
catalog. If you are returning a large number of rows this will be
expensive.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:%23uECaDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Hi
> I use ASPNET application using Sql Ser 2000 database. There are a lot of
> queries using "like" statement.
> I suffer bad performance of that application and I noticed in SQL Profiler
> that those "like" queries takes a lot of time.
> I heard about full text queries and I wonder if it could boost the queries
> performance.
> Please advise me if I'm right.
>
> Best Regards
> Darek T.
>

Queries with "like" and full text indexes

Hi
I use ASPNET application using Sql Ser 2000 database. There are a lot of
queries using "like" statement.
I suffer bad performance of that application and I noticed in SQL Profiler
that those "like" queries takes a lot of time.
I heard about full text queries and I wonder if it could boost the queries
performance.
Please advise me if I'm right.
Best Regards
Darek T.Please don't multipost , I have just answered the question in .programming
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:uEudZDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Hi
> I use ASPNET application using Sql Ser 2000 database. There are a lot of
> queries using "like" statement.
> I suffer bad performance of that application and I noticed in SQL Profiler
> that those "like" queries takes a lot of time.
> I heard about full text queries and I wonder if it could boost the queries
> performance.
> Please advise me if I'm right.
>
> Best Regards
> Darek T.
>|||ok, sorry
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:euvWZNZfGHA.1856@.TK2MSFTNGP03.phx.gbl...
> Please don't multipost , I have just answered the question in .programming
> "Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
> news:uEudZDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>

Queries with "like" and full text indexes

Hi
I use ASPNET application using Sql Ser 2000 database. There are a lot of
queries using "like" statement.
I suffer bad performance of that application and I noticed in SQL Profiler
that those "like" queries takes a lot of time.
I heard about full text queries and I wonder if it could boost the queries
performance.
Please advise me if I'm right.
Best Regards
Darek T.Please don't multipost , I have just answered the question in .programming
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:uEudZDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Hi
> I use ASPNET application using Sql Ser 2000 database. There are a lot of
> queries using "like" statement.
> I suffer bad performance of that application and I noticed in SQL Profiler
> that those "like" queries takes a lot of time.
> I heard about full text queries and I wonder if it could boost the queries
> performance.
> Please advise me if I'm right.
>
> Best Regards
> Darek T.
>|||ok, sorry
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:euvWZNZfGHA.1856@.TK2MSFTNGP03.phx.gbl...
> Please don't multipost , I have just answered the question in .programming
> "Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
> news:uEudZDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Hi
>> I use ASPNET application using Sql Ser 2000 database. There are a lot of
>> queries using "like" statement.
>> I suffer bad performance of that application and I noticed in SQL
>> Profiler that those "like" queries takes a lot of time.
>> I heard about full text queries and I wonder if it could boost the
>> queries performance.
>> Please advise me if I'm right.
>>
>> Best Regards
>> Darek T.
>

Queries will be the death of me

Hello! My query skills are in need of some help. I've simplified my tables
in order to explain what it is that I'm trying to do. I need this to create
dynamic checkboxes on an asp.net page and to have the checkbox values set as
the tables are drawn dynamically. Anyway, here goes:
Table A
ItemID <bigint>
Title <string>
Table B
PurchaseID <bitint>
ItemID <smallint>
I have two tables (A and B). The two tables are linked on field ItemID. I
need to make a query that will return ALL of the values from table A along
with a boolean value stating whether or not table B contains an item - but
only for a specific purchaseID.
For example:
Table A (ItemID, Title)
1 VCR
2 DVD
3 Television
4 Phone
Table B (PurchaseID, ItemID)
1 1 (VCR)
1 2 (DVD)
1 3 (Tel)
2 2 (DVD)
2 1 (...)
2 4
3 4
3 1
For the above data I need a list of the Items from tableA and a boolean
value showing whether the item exists in Table B for a specific PurchaseID,
in this case I used PurchaseID = 2.
Result Table (ItemID, Title, Boolean)
1 VCR 1
2 DVD 1
3 Television 0
4 Phone 1
The result shows that there exists a VCR, DVD and Phone for purchaseID, but
no Television.
Grateful for any help...
Hello ChrisN,
I think this is what you're looking for (with tables and data included):
-- DROP TABLE A - Items
CREATE TABLE A (
ItemID int NOT NULL, -- PK
Title varchar (50) NOT NULL)
go
ALTER TABLE A WITH NOCHECK ADD PRIMARY KEY CLUSTERED (ItemID)
go
-- DROP TABLE B - Purchases
CREATE TABLE B (
PurchaseID int NOT NULL, -- PK
ItemID int NOT NULL) -- PK
go
ALTER TABLE B WITH NOCHECK ADD PRIMARY KEY CLUSTERED (PurchaseID, ItemID)
go
-- Insert test sample data into table A:
insert into A values (1, 'VCR')
insert into A values (2, 'DVD')
insert into A values (3, 'Television')
insert into A values (4, 'Phone')
go
-- Insert test sample data into table B:
insert into B values (1, 1) -- VCR
insert into B values (1, 2) -- DVD
insert into B values (1, 3) -- Television
insert into B values (2, 2) -- DVD
insert into B values (2, 1) -- VCR
insert into B values (2, 4) -- Phone
insert into B values (3, 4) -- Phone
insert into B values (3, 1) -- VCR
go
SELECT TableA.ItemID, TableA.Title
FROM A TableA
INNER JOIN B TableB ON (TableA.ItemID = TableB.ItemID)
WHERE PurchaseID = 2
go
However, are you also interested in a FTS query based upon the above INNER
JOIN between tables A & B since you posted this question in the fulltext
newsgroup? If so, could you post an example of what you're looking for?
Thanks,
John
"ChrisN" <cnewald@.hotmail.com> wrote in message
news:uJV$f$b1EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello! My query skills are in need of some help. I've simplified my
tables
> in order to explain what it is that I'm trying to do. I need this to
create
> dynamic checkboxes on an asp.net page and to have the checkbox values set
as
> the tables are drawn dynamically. Anyway, here goes:
> Table A
> ItemID <bigint>
> Title <string>
> Table B
> PurchaseID <bitint>
> ItemID <smallint>
> I have two tables (A and B). The two tables are linked on field ItemID.
I
> need to make a query that will return ALL of the values from table A along
> with a boolean value stating whether or not table B contains an item - but
> only for a specific purchaseID.
> For example:
> Table A (ItemID, Title)
> 1 VCR
> 2 DVD
> 3 Television
> 4 Phone
> Table B (PurchaseID, ItemID)
> 1 1 (VCR)
> 1 2 (DVD)
> 1 3 (Tel)
> 2 2 (DVD)
> 2 1 (...)
> 2 4
> 3 4
> 3 1
> For the above data I need a list of the Items from tableA and a boolean
> value showing whether the item exists in Table B for a specific
PurchaseID,
> in this case I used PurchaseID = 2.
> Result Table (ItemID, Title, Boolean)
> 1 VCR 1
> 2 DVD 1
> 3 Television 0
> 4 Phone 1
> The result shows that there exists a VCR, DVD and Phone for purchaseID,
but
> no Television.
> Grateful for any help...
>
|||Thank you John for your response. Being a dough head I did post to the
wrong newsgroup and realized after it sent. I'll give your solution a try
in a little while.
Thanks again,
Chris.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OlzoyOf1EHA.1300@.TK2MSFTNGP14.phx.gbl...
> Hello ChrisN,
> I think this is what you're looking for (with tables and data included):
> -- DROP TABLE A - Items
> CREATE TABLE A (
> ItemID int NOT NULL, -- PK
> Title varchar (50) NOT NULL)
> go
> ALTER TABLE A WITH NOCHECK ADD PRIMARY KEY CLUSTERED (ItemID)
> go
> -- DROP TABLE B - Purchases
> CREATE TABLE B (
> PurchaseID int NOT NULL, -- PK
> ItemID int NOT NULL) -- PK
> go
> ALTER TABLE B WITH NOCHECK ADD PRIMARY KEY CLUSTERED (PurchaseID, ItemID)
> go
> -- Insert test sample data into table A:
> insert into A values (1, 'VCR')
> insert into A values (2, 'DVD')
> insert into A values (3, 'Television')
> insert into A values (4, 'Phone')
> go
> -- Insert test sample data into table B:
> insert into B values (1, 1) -- VCR
> insert into B values (1, 2) -- DVD
> insert into B values (1, 3) -- Television
> insert into B values (2, 2) -- DVD
> insert into B values (2, 1) -- VCR
> insert into B values (2, 4) -- Phone
> insert into B values (3, 4) -- Phone
> insert into B values (3, 1) -- VCR
> go
> SELECT TableA.ItemID, TableA.Title
> FROM A TableA
> INNER JOIN B TableB ON (TableA.ItemID = TableB.ItemID)
> WHERE PurchaseID = 2
> go
> However, are you also interested in a FTS query based upon the above INNER
> JOIN between tables A & B since you posted this question in the fulltext
> newsgroup? If so, could you post an example of what you're looking for?
> Thanks,
> John
>
>
> "ChrisN" <cnewald@.hotmail.com> wrote in message
> news:uJV$f$b1EHA.3416@.TK2MSFTNGP09.phx.gbl...
> tables
> create
> as
> I
> PurchaseID,
> but
>
sql

QUERIES VS STOREDPROCEDURES

I use visual studio 2005 standard edition and sql express 2005.I would like to know if there is any difference using queries(designed in dataset xsd) or stored procedures.

Also I am having the following problem :Whenever I make a change on a table in my database (add columns etc) and use the wizard to retrieve the changes i loose all the queries for that table (I have to write them again).Am i doing something wrong?

Thanks in advance!!!

There is quite a bit of difference between VS queries and SQL Server Stored Procedures.

First, Stored Procedures are actually stored in SQL Server. They don't just 'vanish' -as you are discovering happens to your VS queries. (If you make changes to the tables that cause the stored procedures to fail, they will still be there -just error when executed (until repaired.)

Stored procedures, when called, are compiled and an execution plan is prepared on SQL Server. That execution plan can be reused the next time the stored procedure is called -saving time.

Security can be applied to stored procedures, allowing or denying the 'right' to use them.

Developers don't have to know where the stored procedures are getting their data, and the application doesn't have to be changed when the database changes -just change the stored procedures.

And the list goes on...

|||

Stored procedures are easier to maintain than in-line queries. They are more secure than in-line queries, since they are less vulnerable to SQL Injection attacks. They can also reduce network traffic (incoming to the DB server).

As long as you don't change the interface of a stored procedure (name, parameters, and return values). This will insulate you from the problems you are seeing now when you make table changes.

|||

Hi,

If you seperate the database queries from your application codes by using stored procedures, it will be easier to modify the sql queries for purposes of improving performance, updating queries, etc. Otherwise you should make those changes in your application.

Eralper

http://www.kodyaz.com

|||

Thank you!

I can see now the benefits of stored procedures.

QUERIES VS STOREDPROCEDURES

I use visual studio 2005 standard edition and sql express 2005.I would like to know if there is any difference using queries(designed in dataset xsd) or stored procedures.

Also I am having the following problem :Whenever I make a change on a table in my database (add columns etc) and use the wizard to retrieve the changes i loose all the queries for that table (I have to write them again).Am i doing something wrong?

Thanks in advance!!!

There is quite a bit of difference between VS queries and SQL Server Stored Procedures.

First, Stored Procedures are actually stored in SQL Server. They don't just 'vanish' -as you are discovering happens to your VS queries. (If you make changes to the tables that cause the stored procedures to fail, they will still be there -just error when executed (until repaired.)

Stored procedures, when called, are compiled and an execution plan is prepared on SQL Server. That execution plan can be reused the next time the stored procedure is called -saving time.

Security can be applied to stored procedures, allowing or denying the 'right' to use them.

Developers don't have to know where the stored procedures are getting their data, and the application doesn't have to be changed when the database changes -just change the stored procedures.

And the list goes on...

|||

Stored procedures are easier to maintain than in-line queries. They are more secure than in-line queries, since they are less vulnerable to SQL Injection attacks. They can also reduce network traffic (incoming to the DB server).

As long as you don't change the interface of a stored procedure (name, parameters, and return values). This will insulate you from the problems you are seeing now when you make table changes.

|||

Hi,

If you seperate the database queries from your application codes by using stored procedures, it will be easier to modify the sql queries for purposes of improving performance, updating queries, etc. Otherwise you should make those changes in your application.

Eralper

http://www.kodyaz.com

|||

Thank you!

I can see now the benefits of stored procedures.

Queries using tables from diffrent Databases or SQL instances

Hello,

I am new in SSIS.

I am using an OLEDB source and setted as SQL Command.

The Query is a JOIN between different databases.

How can I make the QUERY with different source (different databases or SQL Servers)?

I mean, any solution is OK, the important is to make queries against different databases with SSIS.

Thank

You can always use two or more OLE DB sources and then use a union all or Merge Join transformations.|||

As Phil wrote in the previous post, you can add several OLEDB sources or others sources and join the data adding a UNION ALL or MERGE JOIN.

In merge Join the input data must be sorted and could ne joined by LEFT or RIGHT.

Regards!

|||

Thank|||

If you have to use an Execute SQL Task, you can do this by creating Linked servers, it can be SQL server or not.

Then use fully qualified names.

I use this to compare tables content side by side accross similar servers.

i.e.

Select s1.ColumnA as Server1_Status, s2.ColumnA as Server2_Status

From Server1 s1

Join Server2 s2 on s1.Pkey = s2.Pkey

Where Server2 is an entry in Server Objects, Linked Servers

For everything else, I use the other options like multiple pumps and Union All.

It performs better, especially when pulling from non-SQL databases.

Regards,

Philippe

Queries using bitwise?

Hi Everyone,
I've using the query below to extract some information. ONe of the items in
the where clause is a check for an integer column for 0x1.
Question: What is the correct syntax for checking this column? As below, I
have pm.ProductStatus = 0x1. This is returning rows. Am I correct in this?
Thanks in advance
Larry
SELECT am.AccountID, cpc.ProductID, cpc.ProductName, cpc.ManufacturerName,
cpc.ManufacturerPartNumber, wtl.Description, cpws.Notes
FROM
Account_Master am
INNER JOIN ClientProductCatalog cpc
ON am.AccountID = cpc.AccountID
INNER JOIN ProductMaster pm
ON cpc.ProductID = pm.ProductID
INNER JOIN ClientProductWorkflowStatus cpws
ON cpc.ClientProductCatalogID = cpws.clientProductCatalogID
INNER JOIN WorkflowStatusTypeLkp wfst
ON cpws.WorkflowStatusTypeLkpID = wfst.WorkFlowStatusTypeLkpID
INNER JOIN WorkflowTypeLkp wtl
ON cpws.WorkflowTypeLkpID = wtl.WorkflowTypeLkpID
WHERE wfst.WorkflowStatusTypeLkpID = 3 -- Needs more information from
customer?
AND cpc.Deactivated = 0 -- Product still active?
AND pm.ProductStatus = 0x1 -- Awaiting status?> Question: What is the correct syntax for checking this column? As below,
I
> have pm.ProductStatus = 0x1. This is returning rows. Am I correct in
this?
The equal operator is NOT a bitwise operator. Therefore, the answer is "NO"
if you are attempting to limit the resultset to rows where ProductStatus has
the lowest bit set. If this is actually what you are after, then this
highlights a potential flaw in your design. Product status appears to
contain multiple pieces of information in a single column, a violation of
first normal form. On the flip side, perhaps you want status values that are
odd?
Assuming you want to continue down this path, then have a look in BOL for
the bitwise operators. As a hint, you will need to use the bitwise AND
operator, using an expression in the form of "(mycolumn AND y) = z" (note -
pseudocode).|||Larry,
How can we know if this is the correct syntax to check if you don't
say what you are trying to check? The query you posted will select
rows where pm.productStatus equals 1. If that's what you want, then
that's what you'll get. If that's not what you want, you need to tell us
what it is that you want. You say the query is returning rows. Have
you tried to run this on some test data to see if they are the rows you
want?
It's not clear why you are expressing the number 1 as 0x1, by the way.
The query you are posting here would be easier to read if you wrote
pm.productStatus = 1.
Please provide more information.
Larry wrote:

>Hi Everyone,
>I've using the query below to extract some information. ONe of the items i
n
>the where clause is a check for an integer column for 0x1.
>Question: What is the correct syntax for checking this column? As below,
I
>have pm.ProductStatus = 0x1. This is returning rows. Am I correct in this
?
>Thanks in advance
>Larry
>
>SELECT am.AccountID, cpc.ProductID, cpc.ProductName, cpc.ManufacturerName,
>cpc.ManufacturerPartNumber, wtl.Description, cpws.Notes
>FROM
>Account_Master am
>INNER JOIN ClientProductCatalog cpc
>ON am.AccountID = cpc.AccountID
>INNER JOIN ProductMaster pm
>ON cpc.ProductID = pm.ProductID
>INNER JOIN ClientProductWorkflowStatus cpws
>ON cpc.ClientProductCatalogID = cpws.clientProductCatalogID
>INNER JOIN WorkflowStatusTypeLkp wfst
>ON cpws.WorkflowStatusTypeLkpID = wfst.WorkFlowStatusTypeLkpID
>INNER JOIN WorkflowTypeLkp wtl
>ON cpws.WorkflowTypeLkpID = wtl.WorkflowTypeLkpID
>WHERE wfst.WorkflowStatusTypeLkpID = 3 -- Needs more information from
>customer?
>AND cpc.Deactivated = 0 -- Product still active?
>AND pm.ProductStatus = 0x1 -- Awaiting status?
>|||Larry - The bitwise operators (from BOL) are
bitwise logical And ... &
bitwise logical or ... |
bitwise NOT ............ ~
bitwise exclusive or ... ^
So to test if an integral value has a particular bit SET
(Defined by a bitmask with all zeros but one)
Bitmask must be decimal 1,2,4,8,16, etc, or hex 0x1, 0x2, 0x4, 0x8, 0x10,
0x20, etc
use logical And ....... Where (Value & @.BitMask) <> 0
- If the result is non-zero, the Bit was set
To test if a particular bit is NOT SET
flip the bits of the value with NOT (~) and test that with the bitmask using
And (&)
Where (~Value & @.BitMask) <> 0
"Larry" wrote:

> Hi Everyone,
> I've using the query below to extract some information. ONe of the items
in
> the where clause is a check for an integer column for 0x1.
> Question: What is the correct syntax for checking this column? As below,
I
> have pm.ProductStatus = 0x1. This is returning rows. Am I correct in thi
s?
> Thanks in advance
> Larry
>
> SELECT am.AccountID, cpc.ProductID, cpc.ProductName, cpc.ManufacturerName,
> cpc.ManufacturerPartNumber, wtl.Description, cpws.Notes
> FROM
> Account_Master am
> INNER JOIN ClientProductCatalog cpc
> ON am.AccountID = cpc.AccountID
> INNER JOIN ProductMaster pm
> ON cpc.ProductID = pm.ProductID
> INNER JOIN ClientProductWorkflowStatus cpws
> ON cpc.ClientProductCatalogID = cpws.clientProductCatalogID
> INNER JOIN WorkflowStatusTypeLkp wfst
> ON cpws.WorkflowStatusTypeLkpID = wfst.WorkFlowStatusTypeLkpID
> INNER JOIN WorkflowTypeLkp wtl
> ON cpws.WorkflowTypeLkpID = wtl.WorkflowTypeLkpID
> WHERE wfst.WorkflowStatusTypeLkpID = 3 -- Needs more information from
> customer?
> AND cpc.Deactivated = 0 -- Product still active?
> AND pm.ProductStatus = 0x1 -- Awaiting status?|||You can use a simple integer one constant for this. Why do you want to
make the code proprietary, unreadable and unmaintainable?
There are many things we do not do in SQL and one of them is low-level,
bit and byte operations. That is what assembly languages and things
like C are for. The next error is writing a lot of flags into the
schema, to mimic an old punch card file system. For example,
"deactivated" looks like a flag which should be a more general status
code or an even date.
We also do not construct data element names like "WorkflowTypeLkpID",
since a thing cannot be both a type code and an identfier. Where is
the "ClientProducts" or perhaps "ClientProductWorkflow" table which
would have "ClientProductWorkflowStatus" in a column, since a status
code is not an entity?
You confuse data and metadata concepts. I would bet you are used to an
OO language with lots of low-level constructs, without even a year of
data modeling.|||Is it not the case that bit wise operators are part of the SQL 99
specification?
Thomas
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:0F8877FE-B2B3-416C-9803-C2501F1C60A5@.microsoft.com...
> Hi Everyone,
> I've using the query below to extract some information. ONe of the items
> in
> the where clause is a check for an integer column for 0x1.
> Question: What is the correct syntax for checking this column? As below,
> I
> have pm.ProductStatus = 0x1. This is returning rows. Am I correct in
> this?
> Thanks in advance
> Larry
>
> SELECT am.AccountID, cpc.ProductID, cpc.ProductName, cpc.ManufacturerName,
> cpc.ManufacturerPartNumber, wtl.Description, cpws.Notes
> FROM
> Account_Master am
> INNER JOIN ClientProductCatalog cpc
> ON am.AccountID = cpc.AccountID
> INNER JOIN ProductMaster pm
> ON cpc.ProductID = pm.ProductID
> INNER JOIN ClientProductWorkflowStatus cpws
> ON cpc.ClientProductCatalogID = cpws.clientProductCatalogID
> INNER JOIN WorkflowStatusTypeLkp wfst
> ON cpws.WorkflowStatusTypeLkpID = wfst.WorkFlowStatusTypeLkpID
> INNER JOIN WorkflowTypeLkp wtl
> ON cpws.WorkflowTypeLkpID = wtl.WorkflowTypeLkpID
> WHERE wfst.WorkflowStatusTypeLkpID = 3 -- Needs more information from
> customer?
> AND cpc.Deactivated = 0 -- Product still active?
> AND pm.ProductStatus = 0x1 -- Awaiting status?|||Politics. There were products that still had a low-level
implementation based on particular hardware. Did you look at the
SQL:2003 that deprecared bit data types? Did you look at the problems
in implementations in actual products?|||Only AND, OR, and NOT, and only for data of type BOOLEAN, are
in the SQL-99 standard as far as I know. I wouldn't expect the standard
to specify bitwise operations for numerical data, or any other operators
that behaved as though the data were represented in a particular way.
Steve Kass
Drew University
Thomas C wrote:

>Is it not the case that bit wise operators are part of the SQL 99
>specification?
>
>Thomas
>"Larry" <Larry@.discussions.microsoft.com> wrote in message
>news:0F8877FE-B2B3-416C-9803-C2501F1C60A5@.microsoft.com...
>
>
>

Queries used in V?deo for Beginners

Hi,

I just installed reporting services and now starting with the video tutorial for reporting services(http://msdn2.microsoft.com/en-us/express/aa718391.aspx)

In Video 10 and 11 some queries are used to demonstrate building reports. I would like to repeat the steps in the video but I need the SQL Queries used in those videos.

Does anyone have any idea where I can download those SQL queries in order to programm the same reports as shown in the videos?

Many thanks for your replies and best regards from Switzerland

Chris

The samples have moved to Codeplex download and install the samples and databases, attach the database and go to the SQL Server folder to subfolder 90 and you will see the samples open the Reporting samples AdventureWorks Samples double click the solution file and if you have SSRS installed VS2005 will open the Reports solution you can look at the code. Hope this helps.

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

sql

queries take a long time after sp2 installation

Has anyone had thsi issue, if so what was the fix?

We installed service pack2, the day after 2 of our production jobs started taking a long time to complete and causing a ton of blocking.

it went from running in 2 minutes to now taking 3 hours and 29 minutes to run. Can someone help?

Is it only certain queries running slower or is it all queries running slower? What high-level symptoms are you seeing? Does it look like CPU pressure, memory pressure, I/O pressure, blocking, etc.?

What kind of results do you get when you run these DMV queries?

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)

,'%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)

,'%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

FROM sys.dm_os_wait_stats

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

-- Isolate top waits

WITH Waits AS

(

SELECT

wait_type,

wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

FROM sys.dm_os_wait_stats

WHERE wait_type NOT LIKE '%SLEEP%'

-- filter out additional irrelevant waits

)

SELECT

W1.wait_type,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.rn <= W1.rn

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold

ORDER BY W1.rn;

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROM sys.dm_exec_requests AS blocked_query

INNER JOIN sys.dm_exec_requests AS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNER JOIN sys.dm_os_waiting_tasks AS waits

ON waits.session_id = blocking_query.session_id

|||After the SP2 Upgrade did you update the stats and index rebuilds on the tables?. You might want to perform some optimization and see if the problem still persists.|||

If you have not done so already, try running sp_UpdateStats on your database(s). This will run UPDATE STATISTICS with the default 10% sample rate on every table in the database (which may take a while, depending on your hardware and db size).

You should always do that after upgrading to SQL Server 2005

Rebuilding (as opposed to reorganizing) indexes will also automatically update statistics.

|||Hi,

We have experienced many slowness and trouble since the SP2 was installed.
Have a look at theses 2 KB:
http://support.microsoft.com/default.aspx/kb/937745
And
http://support.microsoft.com/default.aspx/kb/933564/en-us

Last point, run the following query when your server is slow:

select type, sum(single_pages_kb+multi_pages_kb) 'total memory' from sys.dm_os_memory_clerks ORDER by 2 DESC

This will help you to know what cache type is getting most of your SQL Ram.
If it's USERSTORE_TOKENPERM, consider using Trace flag 4618. It has had good impact on our system, but still not correct the case. I have conf call with MS Support on Monday about that point and will answer in this post if I have anymore informations.

Regards,
Jeremy
|||

Jeremy - Just wondering if you had any updates. We are currently hitting this issue. The clearing of ('TokenAndPermUserStore') does not work 100% of the time. We've experienced times when the system is so busy that we can not clear it fast enough.

We are considering using the trace flag.

Thanks -

Sam.

queries take a long time after sp2 installation

Has anyone had thsi issue, if so what was the fix?

We installed service pack2, the day after 2 of our production jobs started taking a long time to complete and causing a ton of blocking.

it went from running in 2 minutes to now taking 3 hours and 29 minutes to run. Can someone help?

Is it only certain queries running slower or is it all queries running slower? What high-level symptoms are you seeing? Does it look like CPU pressure, memory pressure, I/O pressure, blocking, etc.?

What kind of results do you get when you run these DMV queries?

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)

,'%signal (cpu) waits'=CAST(100.0 *SUM(signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)

,'%resource waits'=CAST(100.0 *SUM(wait_time_ms - signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

FROMsys.dm_os_wait_stats

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROMsys.dm_os_schedulers

WHERE scheduler_id < 255

-- Isolate top waits

WITH Waits AS

(

SELECT

wait_type,

wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms /SUM(wait_time_ms)OVER()AS pct,

ROW_NUMBER()OVER(ORDERBY wait_time_ms DESC)AS rn

FROMsys.dm_os_wait_stats

WHERE wait_type NOTLIKE'%SLEEP%'

-- filter out additional irrelevant waits

)

SELECT

W1.wait_type,

CAST(W1.wait_time_s ASDECIMAL(12, 2))AS wait_time_s,

CAST(W1.pct ASDECIMAL(12, 2))AS pct,

CAST(SUM(W2.pct)ASDECIMAL(12, 2))AS running_pct

FROM Waits AS W1

INNERJOIN Waits AS W2

ON W2.rn <= W1.rn

GROUPBY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVINGSUM(W2.pct)- W1.pct < 90 -- percentage threshold

ORDERBY W1.rn;

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROMsys.dm_exec_requestsAS blocked_query

INNERJOINsys.dm_exec_requestsAS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSSAPPLY

(SELECT*

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSSAPPLY

(SELECT*

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNERJOINsys.dm_os_waiting_tasksAS waits

ON waits.session_id = blocking_query.session_id

|||After the SP2 Upgrade did you update the stats and index rebuilds on the tables?. You might want to perform some optimization and see if the problem still persists.|||

If you have not done so already, try running sp_UpdateStats on your database(s). This will run UPDATE STATISTICS with the default 10% sample rate on every table in the database (which may take a while, depending on your hardware and db size).

You should always do that after upgrading to SQL Server 2005

Rebuilding (as opposed to reorganizing) indexes will also automatically update statistics.

|||Hi,

We have experienced many slowness and trouble since the SP2 was installed.
Have a look at theses 2 KB:
http://support.microsoft.com/default.aspx/kb/937745
And
http://support.microsoft.com/default.aspx/kb/933564/en-us

Last point, run the following query when your server is slow:

select type, sum(single_pages_kb+multi_pages_kb) 'total memory' from sys.dm_os_memory_clerks ORDER by 2 DESC

This will help you to know what cache type is getting most of your SQL Ram.
If it's USERSTORE_TOKENPERM, consider using Trace flag 4618. It has had good impact on our system, but still not correct the case. I have conf call with MS Support on Monday about that point and will answer in this post if I have anymore informations.

Regards,
Jeremy
|||

Jeremy - Just wondering if you had any updates. We are currently hitting this issue. The clearing of ('TokenAndPermUserStore') does not work 100% of the time. We've experienced times when the system is so busy that we can not clear it fast enough.

We are considering using the trace flag.

Thanks -

Sam.

queries take a long time after sp2 installation

Has anyone had thsi issue, if so what was the fix?

We installed service pack2, the day after 2 of our production jobs started taking a long time to complete and causing a ton of blocking.

it went from running in 2 minutes to now taking 3 hours and 29 minutes to run. Can someone help?

Is it only certain queries running slower or is it all queries running slower? What high-level symptoms are you seeing? Does it look like CPU pressure, memory pressure, I/O pressure, blocking, etc.?

What kind of results do you get when you run these DMV queries?

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)

,'%signal (cpu) waits'=CAST(100.0 *SUM(signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)

,'%resource waits'=CAST(100.0 *SUM(wait_time_ms - signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

FROMsys.dm_os_wait_stats

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROMsys.dm_os_schedulers

WHERE scheduler_id < 255

-- Isolate top waits

WITH Waits AS

(

SELECT

wait_type,

wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms /SUM(wait_time_ms)OVER()AS pct,

ROW_NUMBER()OVER(ORDERBY wait_time_ms DESC)AS rn

FROMsys.dm_os_wait_stats

WHERE wait_type NOTLIKE'%SLEEP%'

-- filter out additional irrelevant waits

)

SELECT

W1.wait_type,

CAST(W1.wait_time_s ASDECIMAL(12, 2))AS wait_time_s,

CAST(W1.pct ASDECIMAL(12, 2))AS pct,

CAST(SUM(W2.pct)ASDECIMAL(12, 2))AS running_pct

FROM Waits AS W1

INNERJOIN Waits AS W2

ON W2.rn <= W1.rn

GROUPBY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVINGSUM(W2.pct)- W1.pct < 90 -- percentage threshold

ORDERBY W1.rn;

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROMsys.dm_exec_requestsAS blocked_query

INNERJOINsys.dm_exec_requestsAS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSSAPPLY

(SELECT*

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSSAPPLY

(SELECT*

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNERJOINsys.dm_os_waiting_tasksAS waits

ON waits.session_id = blocking_query.session_id

|||After the SP2 Upgrade did you update the stats and index rebuilds on the tables?. You might want to perform some optimization and see if the problem still persists.|||

If you have not done so already, try running sp_UpdateStats on your database(s). This will run UPDATE STATISTICS with the default 10% sample rate on every table in the database (which may take a while, depending on your hardware and db size).

You should always do that after upgrading to SQL Server 2005

Rebuilding (as opposed to reorganizing) indexes will also automatically update statistics.

|||Hi,

We have experienced many slowness and trouble since the SP2 was installed.
Have a look at theses 2 KB:
http://support.microsoft.com/default.aspx/kb/937745
And
http://support.microsoft.com/default.aspx/kb/933564/en-us

Last point, run the following query when your server is slow:

select type, sum(single_pages_kb+multi_pages_kb) 'total memory' from sys.dm_os_memory_clerks ORDER by 2 DESC

This will help you to know what cache type is getting most of your SQL Ram.
If it's USERSTORE_TOKENPERM, consider using Trace flag 4618. It has had good impact on our system, but still not correct the case. I have conf call with MS Support on Monday about that point and will answer in this post if I have anymore informations.

Regards,
Jeremy
|||

Jeremy - Just wondering if you had any updates. We are currently hitting this issue. The clearing of ('TokenAndPermUserStore') does not work 100% of the time. We've experienced times when the system is so busy that we can not clear it fast enough.

We are considering using the trace flag.

Thanks -

Sam.