Best Reporting Services Book

Any suggestions on the best reference book to help with building
simple-moderately difficult reports for a beginner? This looks pretty good
but wonder if it might be too advanced 'Hitchhiker's Guide to SQL Server
2000 Reporting Services'
Thanks!I think that the Hitchhiker book is very good. The tone is conversational,
and I have found much of the material to apply to my own real-world
situations. Actually, you can download the first two chapters for free
(http://www.sqlreportingservices.net/BookSrs2000/preview/default.aspx) . You
may want to try it and see if it works for you.
Another good one is Brian Larson's Microsoft SQL Server 2000 Reporting
Services". I am sure that there are other good books besides those, but
these are the two that I am familiar with.
"Mike Harbinger" wrote:
> Any suggestions on the best reference book to help with building
> simple-moderately difficult reports for a beginner? This looks pretty good
> but wonder if it might be too advanced 'Hitchhiker's Guide to SQL Server
> 2000 Reporting Services'
> Thanks!
>
>

Best program for SQL database manipulation

Hello All

I am a relative beginner to SQL databases & new to this forum, so please bear with me if my query is too basic and advise if this question belongs somewhere else

I began working at a company that uses a program that stores data in an SQL database running off a Firebird engine

The program itself doesnt come with database management/administration module, so I'll need to use an external program for manipulating data in the tables that the database contains.

I have relatively little knowledge in SQL programming, which is why I would like to know which is the most powerful program for SQL database updating / manipulation?

This database has tables that has an infinite number of joins with other tables - Even MS Access wasnt able to open a few tables in this database because of the number of joins. I have tried Access & Lotus Approach, Approach manages to do a better job than Access, it open the tables & seems like it will let me import external data directly into the SQL table, but takes forever & usually just bums out giving an error after a very long wait..

My question is - Apart from Access & Approach, are there any more powerful, yet user friendly programs out there that can help me update data directly into SQL tables? What options do I have - the tasks I need to perform are pretty simple updating & cleaning of data already in there

Please, I will hugely appreciate any pointers that you guys the experts might have for me in this regard

Thanks
AlexTry Microsoft SQL Server 2005 Express. It's Free & Downloadable From http://msdn.microsoft.com/vstudio/express/sql/sql

Best Practice for this SP Scenario !

Hello All ..
This is the scenario I'm having :
-- I'm a beginner so bear the way I'm putting it ... sorry !

* I have a database with tables
- company: CompanyID, CompanyName
- Person: PersonID, PersonName, CompanyID (fk)
- Supplier: SupplierID, SupplierCode, SupplierName, CompanyID (fk)

In the Stored Procedures associated (insertCompany, insertPerson, insertSupplier), I want to check the existance of SupplierID .. which should be the 'Output' ...

There could be different ways to do it like:
1) - In the supplier stored procedure I can read the ID (SELECT) and :

if it exists (I save the existing SupplierID - to 'return' it at the end).
if it doesn't (I insert the Company, the Person and save the new SupplierID - to 'return' it at the end)
-----------
2) - Other way is by doing multiple stored procedures,
. one SP that checks,
. another SP that do inserts
. and a main SP that calls the check SP and gets the values and base the results according to conditions (if - else)

3) it could be done (maybe) using Functions in SQL SERVER...

There should be some reasons why I need to go for one of the methods or another method !
I want to know the best practice for this scenario in terms of performance and other issues - consider a similar big scenario .... !!!

I'll appreciate your help ...
Thanks in Advance . ! .Sql2k recompiles the entire sp if a recompilation is needed. Thus, it's best to split up the sproc into child sprocs. So, your #2 would be the way to go.

I suggest you read up on this excellent article.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Beginning

Hi, im a Beginner and I have to start next week with create and programming
some Reports with Reporting Services. Can anyone give me some good stuff
where I find the first lessons what I have to do etc. Perphaps some links or
e idea for some good books!? The installation wasn't a problem, it's about
the programming.
Thanks very much for your help!
schueppi/SwitzerlandOn Thu, 7 Apr 2005 23:35:06 -0700, "schueppi"
<schueppi@.discussions.microsoft.com> wrote:
>Hi, im a Beginner and I have to start next week with create and programming
>some Reports with Reporting Services. Can anyone give me some good stuff
>where I find the first lessons what I have to do etc. Perphaps some links or
>e idea for some good books!? The installation wasn't a problem, it's about
>the programming.
>Thanks very much for your help!
>schueppi/Switzerland
There was a series of 5 webcasts about a month ago. I included a link
from my blog, http://www.tfosorcim.org/archives/000340.html.
The webcasts are ideal for getting your head round SQL Reporting
Services 2000.
Once you have watched those, you will be in a better position to
understand the Reporting Services Books Online, link from here:
http://www.tfosorcim.org/archives/000352.html.
Andrew Watt
MVP - InfoPath

Beginner's questions

Hello,

I have two database servers that I am interested in sending messeges between them. I am not interested in security or encryption at this point, only a simple message sending and recieving.

Can someone please provide simple scripts for object setup (certificates, queues etc) and message send and recieve ?

a link to such a script will be great as well. I am just a bit lost understanding all the new concepts (certificates, service, service binding.....)

Thank you

I.

The Service Listing Manager tool from https://blogs.msdn.com/remusrusanu/archive/2006/04/07/571066.aspx can be used to set this up. It will do all the necesary settings for you, including creating and configuring the endpoints, setting up the routes and setting up security. Give it a try and see if still things look complex after you use it :)

HTH,
~ Remus

|||

I installed the tool and followed the instructions but all it did was to create a document with the service name and endpoint. what do I do from here ?

Thanks

I.

|||

Open the created document, connect to the other machine and select the 'Import' option.

HTH,
~ Remus

|||

Thanks Remus,

That was very helpful. now - I know how to send and recieve messages localy on the same instance. how do I send and recieve messages between two different instances ?

Thank you

I.

|||

The tool does it. Repeat the same steps as for local instance, but simply connect to a different instance when running the Import step. It will do all the necesary setup steps (creat an endpoint, setup endpoint security, setup transport routes in the database etc).

HTH,
~Remus

|||Thanks but I was talking about the Begin Dialog Conversation and recieve commands. is it any different than recieving or sending a message localy ?|||

No differences there. Servicve Broker enables an application designed localy to work distributed w/o any changes.

HTH,
~ Remus

|||

then I guess I have a syntax error...

I am using this code to send message from one instance to another :

BEGIN TRANSACTION ;

GO

DECLARE @.message XML ;

SET @.message = N'<message>Hello, World!</message>' ;

DECLARE @.conversationHandle UNIQUEIDENTIFIER ;

BEGIN DIALOG CONVERSATION @.conversationHandle

FROM SERVICE InitiatorService

TO SERVICE 'tcp://TCA02SQL603:4037','3771F429-0111-488C-9D0E-080226B1DD8F'

ON CONTRACT HelloWorldContract ;

SEND ON CONVERSATION @.conversationHandle

MESSAGE TYPE HelloWorldMessage

(@.message) ;

SET @.message = N'<message>Goodbye!</message>' ;

SEND ON CONVERSATION @.conversationHandle

MESSAGE TYPE HelloWorldMessage

(@.message) ;

END CONVERSATION @.conversationHandle ;

the message doesn't arrive and in the sys.transmission_queue table I get the error :

'The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied.'

|||

The error you mention in the sys.transmission_queue indicates that there is no route for the service named 'tcp://TCA02SQL603:4037'. Perhaps you used the route address instead of the service name? BEGIN DIALOG takes in the name of the target service (e.g. 'TargetService').

HTH,
~ Remus

|||

OK, I indeed used the route address instead of the service name so I replaced it :

BEGIN DIALOG CONVERSATION @.conversationHandle

FROM SERVICE InitiatorService

TO SERVICE 'TargetService','3771F429-0111-488C-9D0E-080226B1DD8F'

ON CONTRACT HelloWorldContract ;

However, I still get the same message. I must specify that in sys.transmission_queue the

to_service_name column = 'TargetService,3771F429-0111-488C-9D0E-080226B1DD8F'

and the 'to_broker_instance' is empty.

|||

This means you had a typo in the BEGIN DIALOG statement and you actually used TO SERVICE 'TargetService, 3771F...' instead of 'TargetService','3771F...'. You should see 'TargetService' in to_service_name column and '3771F..' in to_broker_instance column.

Also, make sure the row in sys.transmission_queue is the one from your lastes actual BEGIN DIALOG, and not left there from previous attempts. To clean up failed attempts, use END CONVERSATION ... WITH CLEANUP

HTH,
~ Remus

|||

Thanks Remus, that really helped.

I am getting a different error message now :

Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.

|||

I think I deleted your post by mistake.

Ilan D wrote:

ok. for some reason 'End conversation @.CoversationHandle WITH CLEANUP' doesnt clean up the sys.transmission_queue table. any suggestions ?

I tried to make sure that I use ',' between service and instance. it's really hard to tell which error message I got because the table doesn't clean up... but I believe that the error message I am getting right now is 'The private key for the security certificate bound to the database principal (ID 1) is password protected. Password protected private keys are not supported for use with secure dialogs.'

You need to cleanup the particular @.conversationHandle that shows in the conversation_handle column in sys.transmission_queue. You need to do this individually for each conversation you wish to clean up.

The error indicates that the 'dbo' certificate is encrypted by password and the database master key encryption is missing. I assume you used the Service Lsiting Manager tool to setup the certificates. I believe the problem then lieas with 'dbo' having some aditional certificates marked as 'active for begin dialog' that are not the ones deployed by the tool. Lookup in sys.certificates what other certificates are owned by 'dbo' and mark them as not active for begin dialog (ALTER CERTIFICATE [...] WITH ACTIVE FOR BEGIN_DIALOG = OFF, see http://msdn2.microsoft.com/en-us/library/ms189511.aspx)

HTH,
~ Remus

|||

I'd recommend to use certificate based authentication for the endpoints, is easier to get it right.

The error you see is because the NTLM/Kerberos authentication ends up with the anonymous login. The best document to solve these kind of issues is this: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx

Remeber that the accounts being authenticated are those of that are actually running the two SQL Server services. If the SQL Server instances are running as LocalSystem or NETWORK SERVICE, then you must register the SPN 'MSSQLSvc/<machinename>:<brokerport>' for Kerberos to work (use a tool like setspn.exe, available at MS download center).

HTH,
~ Remus

BEGINNER'S QUESTION: SORTING IN ALPHABETICAL ORDER

Hi

I am trying to convert an Access back end into a SQL Server back end and cannot work out how to convert a query that sorts addresses that start with numbers into alphabetical order.

In Access I defined a function that converted the full address to one without the initial numbers eg:
66 Acacia Gardens to Acacia Gardens

I then used this function as an expression in the field column of the query and set it to sort by this field. This put the rows into alphabetical order. (A second function was used to return just the number which was used as the second sort).

I am converting the Access query into a stored procedure. I have successfully converted the Access number removal function into a separate stored procedure and I was intending to execute it from within the main stored procedure but I cannot work out how to do this. I don't think you can insert an Execute statement into a field as you can with a function in Access.

Code for working number removal stored procedure:

ALTER PROCEDURE spRemoveInitialNumbers
@.JobName varchar(20), @.FirstNumbersRemoved varchar(20) OUTPUT
As
DECLARE @.Chopped varchar(20)
DECLARE @.CharacterCount tinyint
DECLARE @.CharacterCode tinyint
SET @.Chopped = @.JobName
IF Substring(@.Chopped,1,5) = 'Unit '
Begin
SET @.Chopped = Right(@.Chopped, Len(@.Chopped)-5)
End
ELSE
SET @.CharacterCount = 1
WHILE @.CharacterCount<=Len(@.Chopped)
BEGIN
SET @.CharacterCode=Ascii(Substring(@.Chopped,@.CharacterCount,1))
If @.CharacterCode>64 and @.CharacterCode<91
Begin
SET @.Chopped=Right(@.Chopped, Len(@.Chopped) - @.CharacterCount + 1)
Break
End
Else
Set @.CharacterCount = @.CharacterCount + 1
End
SET @.FirstNumbersRemoved=@.Chopped

Code for non-working main stored procedure:

SELECT dbo.tblJobs.JobID, dbo.tblJobs.JobNumber, dbo.tblJobs.Surveyor, dbo.tblClients.ClientName, dbo.tblJobs.JobName, dbo.tblJobs.Description,
dbo.tblJobs.Status, dbo.tblJobs.UrgencyCode, dbo.tblJobs.EstimatedFeesOutstanding, dbo.tblJobs.[Live/Dead],
[Intended to Execute SpRemoveInitialNumbers here] AS Expr1,
SUM(dbo.tblFeeAccounts.TotalLessVATandExpenses - dbo.tblFeeAccounts.SubContactors) AS TotalNet, dbo.tblJobs.InstructionDate
FROM dbo.tblJobs INNER JOIN
dbo.tblClients ON dbo.tblJobs.ClientID = dbo.tblClients.ClientID LEFT OUTER JOIN
dbo.tblFeeAccounts ON dbo.tblJobs.JobID = dbo.tblFeeAccounts.JobID
GROUP BY dbo.tblJobs.JobNumber, dbo.tblJobs.Surveyor, dbo.tblClients.ClientName, dbo.tblJobs.JobName, dbo.tblJobs.Description, dbo.tblJobs.Status,
dbo.tblJobs.UrgencyCode, dbo.tblJobs.EstimatedFeesOutstanding, dbo.tblJobs.[Live/Dead], dbo.tblJobs.InstructionDate, dbo.tblJobs.JobID
HAVING (dbo.tblJobs.[Live/Dead] = N'D')
ORDER BY dbo.tblClients.ClientName, dbo.tblJobs.JobName

I would like to undertake the sort on the server, not client side hence my attempt to use stored procedures. I'm not sure that I am going about this the right way. Can anyone help me?

Many thanks.

Whitebeam

Please put the logic of SpRemoveInitialNumbers SP into a TSQL scalar user-defined function. You can then call it from the SELECT list. Alternatively, you can also embed the required substring / charindex / patindex expression in the SELECT list itself for better performance. ( I am assuming you use such functions to remove the initial numbers).

Beginner's question

I just installed SQL Server 2005, Office 2007, and the add-in, but when I check services.msc, I don't see the SQL Server Analysis Services. Does anyone know how I can get this to load? Also, when I run the server configuration utility in the DM add-in folder, do you know what I should put in the server name to get it to run off my hard disk and not a server? local host doesn't seem to work. Thanks in advance!

Which edition of SQL Server 2005 did you install? If you installed the express edition, Analysis Services is not included in that edition. To access Data Mining features, you can install an eval edition of SQL Server 2005 from http://technet.microsoft.com/en-us/sqlserver/bb462637.aspx

The DM Addins needs Analysis Server to run and cannot run off a local file from your hard drive. You'll need Analysis Server installed to use the addins from Office.

|||I have the evaluation version, but don't know how to set it up to have my hard drive as the server. Do you know how I can get the SQL analysis services to run so that it will appear in services.msc?
|||While installing the evaluation edition, did you select Analysis Services as as installation option? You can go to Add/Remove Programs, find Microsoft SQL Server 2005, select Modify and check the list of products installed. Analysis Services should be one of the options.|||Thanks a lot for you help. If I had 50 products and 60,000 different customers with different assortments of products per customers (basket), do you know how microsoft's associate function can help determine the association rules that have the highest probability of occurring? Thanks again.
|||Are you referring to the PredictAssociation function? Or just how to setup the mining model to do a market-basket analysis?|||Market-basket analysis would be great. Is there any way to get it to give the probabilities of Product C given that Product A and B are also sold, or will it just be able to do the probability of product B given that A is sold? Thanks
|||

It does both. The Association Rules algorithm uses all rules that "fire" for the input set. For example if you had rules such as

A->C

A,B->C

A,D->C

and you had A,B, and D in your input, all three rules would "fire". The resultant probability is based on the probability of the rules, and may not match any one in particular.

|||So would I just select the 50,000 clients and 50 products and then run the associate function? Do you know how long it would take for the associate or clustering feature to run with this amount of data on a local hard drive?
|||

The amount of time to perform such an operation is not long. However, reading your original question - "the association rules with the highest probability of occurring", the answer is simply those rules whose LHS itemsets have the highest support. Take for example you have rules

A -> C

A,B -> D

A,B -> C

A, B, C -> E

Say you have 50,000 cases and you have support(A)=5,000, support (A,B) = 2,000, support(A,B,C) = 1000.

The probability given any input case of each rule firing would then be

10% A->C

4% A,B->D

4% A,B->C

2% A,B,C->E

BEGINNER: simple Delete trigger

Hello,
I am trying to learn SQL Server. I need to write a trigger which
deletes positions of the document depending on the movement type.
Here's my code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE TRIGGER [DeleteDocument]
ON [dbo].[Documents]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Movement = 'WZ' OR Documents.Movement = 'RW'
DELETE FROM PositionsWZRW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Ruch = 'MM'
DELETE FROM PositionsMM
WHERE Documents.Number IN (SELECT Number FROM deleted);
END

Unfortunatelly I receive errors which I don't understand:

Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 13
The multi-part identifier "Documents.Numer" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 16
The multi-part identifier "Documents.Number" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 18
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 19
The multi-part identifier "Dokuments.Number" could not be bound.

Please help to correct the code.
Thank you very much!
/RAM/How to forbid deleting Positions if Documents.WasDeleted bit is not
set?
Please help.
/RAM/|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

Hello,
I am trying to learn SQL Server. I need to write a trigger which
deletes positions of the document depending on the movement type.
Here's my code:
>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
>
CREATE TRIGGER [DeleteDocument]
ON [dbo].[Documents]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
>
IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Movement = 'WZ' OR Documents.Movement = 'RW'
DELETE FROM PositionsWZRW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Ruch = 'MM'
DELETE FROM PositionsMM
WHERE Documents.Number IN (SELECT Number FROM deleted);
END
>
Unfortunatelly I receive errors which I don't understand:

I understand the errors, but I understand about as little of your
trigger that SQL Server does. You seem to be making things up out of
thin air. When you say:

IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'

What are Documenty and Documents supposed to be? Maybe you mean

IF EXISTS (SELECT *
FROM deleted
WHERE movement IN ('PZ', 'ZW'))

The same goes for

DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);

This would compile if you have a column Documents in PositionsPZZW,
and this columns is of a CLR UDT and had an attribute named Number.
What this really should be, I don't even want to guess, since I know
nothing about PositiosnPZZW.

The standarad recommendation is that you post:

o CREATE TABLE statements for your tables.
o INSERT statments with sample data.
o In this case: a sample DELETE statement.
o The desired result given the sample.

It also helps to give a little more detailed description of the problem.

By the way, why are there three Positions tables? Maybe there is a good
reason for this, but I have a suspicion that one should do.

--
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|||On Thu, 6 Jul 2006 08:25:27 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>I understand the errors, but I understand about as little of your
>trigger that SQL Server does. You seem to be making things up out of
>thin air. When you say:
>
IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'

I meant Documents.Movement

Quote:

Originally Posted by

>
>What are Documenty and Documents supposed to be? Maybe you mean
>
IF EXISTS (SELECT *
FROM deleted
WHERE movement IN ('PZ', 'ZW'))

Exactly

Quote:

Originally Posted by

>
>
>The same goes for
>
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
>
>This would compile if you have a column Documents in PositionsPZZW,
>and this columns is of a CLR UDT and had an attribute named Number.
>What this really should be, I don't even want to guess, since I know
>nothing about PositiosnPZZW.

I need:
IF EXISTS (SELECT * FROM deleted WHERE Movement IN ('PZ', 'ZW'))
DELETE FROM PositionsPZZW
WHERE Number IN (SELECT Number FROM deleted);

Quote:

Originally Posted by

>By the way, why are there three Positions tables? Maybe there is a good
>reason for this, but I have a suspicion that one should do.

They have different columns describing items.

Thank you, you have helped me... Problem closed
Could you help me with post "one more question"? Thank you!
/RAM/|||Sorry, too short problem description.
Anyway, I solved.
/RAM/|||R.A.M.,
What was the solution you found? Please post as others might have a
simular problem.
TIA
Rob

R.A.M. wrote:

Quote:

Originally Posted by

Sorry, too short problem description.
Anyway, I solved.
/RAM/

|||On Thu, 06 Jul 2006 10:46:50 +0200, R.A.M. <r_ahimsa_m@.poczta.onet.pl>
wrote:

Quote:

Originally Posted by

>IF EXISTS (SELECT * FROM deleted WHERE Movement IN ('PZ', 'ZW'))
>DELETE FROM PositionsPZZW
>WHERE Number IN (SELECT Number FROM deleted);

That looks dangerous. If one row in DELETED has a 'PZ' value, all
rows in PositionsPZZW that match DELETED will be dropped, even those
that do NOT have 'PZ' or 'ZW'.

How about this alternative:

DELETE FROM PositionsPZZW
WHERE Number IN
(SELECT Number FROM deleted WHERE Movement IN ('PZ', 'ZW'));

It does not require the IF test at all, as if there are no matches it
will do nothing.

Roy Harvey
Beacon Falls, CT|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

Could you help me with post "one more question"? Thank you!

If you repost it, and clarify what you mean. I understood very little
of it.

--
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|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

But the code implies some design problems. What are the logical
differences among
PositionsPZZW, PositionsWZRW and PositionsMM ? This looks like
attribute splitting.

Why are you using triggers instead of DRI actions?|||On 6 Jul 2006 04:39:13 -0700, "rcamarda" <robc390@.hotmail.comwrote:

Quote:

Originally Posted by

>What was the solution you found? Please post as others might have a
>simular problem.
>TIA
>Rob

I decided not to use WasDeleted flag in Documents, so it was enough to
set Delete Rule in FK_Positions_Documents to "No Action".
/RAM/|||BEGINNER: simple Delete trigger

beginner: long running sp

hi,
i have a sp that takes around 5-6 sec even though there're around ONLY 5000,
therefore, i need to tune it.
any ideas for me to do so?
thanks a lot!
PS.
Table
====
OutboundQueue [JobID, ....] JobID - P.K
OutboundQueueItem [JobItemID, JobID, .....] JobItemID - P.K and JobID - F.K
SP
===
CREATE PROC GetJobItems @.Machine as varchar(20), @.OutboundType as int as
declare @.JobID as bigint
declare @.JobItemID as bigint
BEGIN
SET NOCOUNT ON
BEGIN Tran
-- change the un-caught local print failed jobs to failure (status =5)
update outboundqueueitem set Status =5 where JobItemID in (select
jobitemid from outboundqueueitem where OutboundType = 1 and isLocalPrint =
1 and Status = 2 and DateDiff(mi, LastWorkingDate, getDate()) > 10)
-- Select one JobItem from OutboundQueue
select top 1 @.JobID=JobID from OutboundQueueItem where isLocked = 0 and
AddToProcessing = 1 and OutboundType = @.OutboundType order by Priority desc,
SubmissionDate asc
-- ****************************************
*********************
-- select JobItems within that JobID just got
-- ****************************************
*********************
-- Create temp Table for storing JobItemIDs to be processed
CREATE TABLE #tmpJobItemID(tJobItemID bigint)
INSERT INTO #tmpJobItemID SELECT JobItemID FROM OutboundQueueItem where
JobID = @.JobID and IsLocked = 0 and AddToProcessing = 1 and OutboundType =
@.OutboundType
-- Lock the Job at OutboundQueue, and update the status to "Working"
update OutboundQueueItem set isLocked = 1, MachineLocked = @.Machine,
Status = 2, LastWorkingDate = getDate() where JobItemID in (select
tJobItemID from #tmpJobItemID)
-- return recordset based on the sequence
select * from OutboundQueueItem where JobItemID in (select tJobItemID from
#tmpJobItemID) order by submissiondate
COMMIT Tran
ENDHello Mullin,
A lot of this is going to depend on your indexing. In order to really tell
what is going on we will need more information. Some of the best information
comes from looking at the execution plan and also looking at what IO occurs.
To get the I/O information do this before you call your procedure.
SET STATISTICS IO ON
EXEC <proc>
SET STATISTICS IO OFF
To view the execution plan for posting here you can do
SET SHOWPLAN_TEXT ON
GO
exec <proc>
GO
SET SHOWPLAN_TEXT OFF
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> hi,
> i have a sp that takes around 5-6 sec even though there're around ONLY
> 5000, therefore, i need to tune it.
> any ideas for me to do so?
> thanks a lot!
> PS.
> Table
> ====
> OutboundQueue [JobID, ....] JobID - P.K
> OutboundQueueItem [JobItemID, JobID, .....] JobItemID - P.K and JobID
> - F.K
> SP
> ===
> CREATE PROC GetJobItems @.Machine as varchar(20), @.OutboundType as
> int as
> declare @.JobID as bigint
> declare @.JobItemID as bigint
> BEGIN
> SET NOCOUNT ON
> BEGIN Tran
> -- change the un-caught local print failed jobs to failure (status
> =5)
> update outboundqueueitem set Status =5 where JobItemID in (select
> jobitemid from outboundqueueitem where OutboundType = 1 and
> isLocalPrint =
> 1 and Status = 2 and DateDiff(mi, LastWorkingDate, getDate()) > 10)
> -- Select one JobItem from OutboundQueue
> select top 1 @.JobID=JobID from OutboundQueueItem where isLocked = 0
> and
> AddToProcessing = 1 and OutboundType = @.OutboundType order by Priority
> desc,
> SubmissionDate asc
> -- ****************************************
*********************
> -- select JobItems within that JobID just got
> -- ****************************************
*********************
> -- Create temp Table for storing JobItemIDs to be processed
> CREATE TABLE #tmpJobItemID(tJobItemID bigint)
> INSERT INTO #tmpJobItemID SELECT JobItemID FROM OutboundQueueItem
> where JobID = @.JobID and IsLocked = 0 and AddToProcessing = 1 and
> OutboundType = @.OutboundType
> -- Lock the Job at OutboundQueue, and update the status to "Working"
> update OutboundQueueItem set isLocked = 1, MachineLocked = @.Machine,
> Status = 2, LastWorkingDate = getDate() where JobItemID in (select
> tJobItemID from #tmpJobItemID)
> -- return recordset based on the sequence
> select * from OutboundQueueItem where JobItemID in (select
> tJobItemID from
> #tmpJobItemID) order by submissiondate
> COMMIT Tran
> END
>|||but, i'm using temp table, and can't output the text version of execution
plan with the following error. but i can view the graphical execution plan
Server: Msg 208, Level 16, State 1, Procedure GetJobItems, Line 23
Invalid object name '#tmpJobItemID'.
any ideas?
btw, the following is the server trace i select at Query Analyzer:
set noexec off set parseonly off SQL:StmtCompleted 0 0 0 0
select IS_SRVROLEMEMBER ('symin') SQL:StmtCompleted 0 0 0 0
SET STATISTICS PROFILE ON SQL:StmtCompleted 0 0 0 0
SET NOCOUNT ON SP:StmtCompleted 0 0 0 0
BEGIN Tran -- change the un-caught local print failed jobs to failure
(status =5) SP:StmtCompleted 0 0 0 0
CREATE TRIGGER tr_OutboundQueueItem_Update ON dbo.OutboundQueueItem FOR
UPDATE AS SP:StmtCompleted 0 0 0 0
IF NOT UPDATE(Status) SP:StmtCompleted 0 0 0 0
select JobItemID from inserted SP:StmtCompleted 0 0 2 0
OPEN jobitemid_cursor -- Perform the first fetch and store the values in
variables. -- Note: The variables are in the same order as the columns --
in the SELECT statement. SP:StmtCompleted 0 0 14 0
FETCH NEXT FROM jobitemid_cursor INTO @.JobItemID -- Check @.@.FETCH_STATUS
to see if there are any more rows to fetch. SP:StmtCompleted 0 0 0 0
WHILE @.@.FETCH_STATUS = 0 SP:StmtCompleted 0 0 0 0
CLOSE jobitemid_cursor SP:StmtCompleted 0 0 0 0
DEALLOCATE jobitemid_cursor SP:StmtCompleted 0 0 0 0
update outboundqueueitem set Status =5 where JobItemID in (select jobitemid
from outboundqueueitem where OutboundType = 1 and isLocalPrint = 1 and
Status = 2 and DateDiff(mi, LastWorkingDate, getDate()) > 10) --
Select one JobItem from SP:StmtCompleted 0 0 104 0
select top 1 @.JobID=JobID from OutboundQueueItem where isLocked = 0 and
AddToProcessing = 1 and OutboundType = @.OutboundType order by Priority desc,
SubmissionDate asc --
****************************************
************* SP:StmtCompleted 0 0
40 0
CREATE TABLE #tmpJobItemID(tJobItemID bigint) SP:StmtCompleted 0 0 10 0
INSERT INTO #tmpJobItemID SELECT JobItemID FROM OutboundQueueItem where
JobID = @.JobID and IsLocked = 0 and AddToProcessing = 1 and OutboundType =
@.OutboundType -- Lock the Job at OutboundQueue, and update the status
to "Working" -- SP:StmtCompleted 0 0 48 0
CREATE TRIGGER tr_OutboundQueueItem_Update ON dbo.OutboundQueueItem FOR
UPDATE AS SP:StmtCompleted 0 0 0 0
IF NOT UPDATE(Status) SP:StmtCompleted 0 0 0 0
select JobItemID from inserted SP:StmtCompleted 0 0 2 0
OPEN jobitemid_cursor -- Perform the first fetch and store the values in
variables. -- Note: The variables are in the same order as the columns --
in the SELECT statement. SP:StmtCompleted 0 0 14 0
FETCH NEXT FROM jobitemid_cursor INTO @.JobItemID -- Check @.@.FETCH_STATUS
to see if there are any more rows to fetch. SP:StmtCompleted 0 0 0 0
WHILE @.@.FETCH_STATUS = 0 SP:StmtCompleted 0 0 0 0
CLOSE jobitemid_cursor SP:StmtCompleted 0 0 0 0
DEALLOCATE jobitemid_cursor SP:StmtCompleted 0 0 0 0
update OutboundQueueItem set isLocked = 1, MachineLocked = @.Machine, Status
= 2, LastWorkingDate = getDate() where JobItemID in (select tJobItemID from
#tmpJobItemID) -- return recordset based on the sequence -- select
* from Outbou SP:StmtCompleted 0 0 68 0
select * from OutboundQueueItem where JobItemID in (select tJobItemID from
#tmpJobItemID) order by submissiondate SP:StmtCompleted 0 0 18 0
COMMIT Tran SP:StmtCompleted 0 0 0 0
getjobitems 'aaa', 1 SQL:StmtCompleted 62 62 66 0
SET STATISTICS PROFILE OFF SQL:StmtCompleted 0 0 0 0
thanks!
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:309826632428962507725664@.news.microsoft.com...
> Hello Mullin,
> A lot of this is going to depend on your indexing. In order to really tell
> what is going on we will need more information. Some of the best
information
> comes from looking at the execution plan and also looking at what IO
occurs.
> To get the I/O information do this before you call your procedure.
> SET STATISTICS IO ON
> EXEC <proc>
> SET STATISTICS IO OFF
> To view the execution plan for posting here you can do
> SET SHOWPLAN_TEXT ON
> GO
> exec <proc>
> GO
> SET SHOWPLAN_TEXT OFF
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://sqlprogrammer.org/
>
>
>|||Mullin
I'd move the CREATE tempoary table at the beginning of the stored procedure.
If you re-write it as
select * from OutboundQueueItem where JobItemID in (select tJobItemID from
#tmpJobItemID where #tmpJobItemID.JobItemID =OutboundQueueItem .JobItemID )
order by submissiondate
check it out.
Also . run SQL Server Profile to see what is going on?
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:%23RRxo6NCFHA.3588@.TK2MSFTNGP11.phx.gbl...
> but, i'm using temp table, and can't output the text version of execution
> plan with the following error. but i can view the graphical execution
plan
> Server: Msg 208, Level 16, State 1, Procedure GetJobItems, Line 23
> Invalid object name '#tmpJobItemID'.
> any ideas?
> btw, the following is the server trace i select at Query Analyzer:
> set noexec off set parseonly off SQL:StmtCompleted 0 0 0 0
> select IS_SRVROLEMEMBER ('symin') SQL:StmtCompleted 0 0 0 0
> SET STATISTICS PROFILE ON SQL:StmtCompleted 0 0 0 0
> SET NOCOUNT ON SP:StmtCompleted 0 0 0 0
> BEGIN Tran -- change the un-caught local print failed jobs to failure
> (status =5) SP:StmtCompleted 0 0 0 0
> CREATE TRIGGER tr_OutboundQueueItem_Update ON dbo.OutboundQueueItem
FOR
> UPDATE AS SP:StmtCompleted 0 0 0 0
> IF NOT UPDATE(Status) SP:StmtCompleted 0 0 0 0
> select JobItemID from inserted SP:StmtCompleted 0 0 2 0
> OPEN jobitemid_cursor -- Perform the first fetch and store the values
in
> variables. -- Note: The variables are in the same order as the
olumns --
> in the SELECT statement. SP:StmtCompleted 0 0 14 0
> FETCH NEXT FROM jobitemid_cursor INTO @.JobItemID -- Check
@.@.FETCH_STATUS
> to see if there are any more rows to fetch. SP:StmtCompleted 0 0 0 0
> WHILE @.@.FETCH_STATUS = 0 SP:StmtCompleted 0 0 0 0
> CLOSE jobitemid_cursor SP:StmtCompleted 0 0 0 0
> DEALLOCATE jobitemid_cursor SP:StmtCompleted 0 0 0 0
> update outboundqueueitem set Status =5 where JobItemID in (select
jobitemid
> from outboundqueueitem where OutboundType = 1 and isLocalPrint = 1 and
> Status = 2 and DateDiff(mi, LastWorkingDate, getDate()) > 10) --
> Select one JobItem from SP:StmtCompleted 0 0 104 0
> select top 1 @.JobID=JobID from OutboundQueueItem where isLocked = 0 and
> AddToProcessing = 1 and OutboundType = @.OutboundType order by Priority
desc,
> SubmissionDate asc --
> ****************************************
************* SP:StmtCompleted 0 0
> 40 0
> CREATE TABLE #tmpJobItemID(tJobItemID bigint) SP:StmtCompleted 0 0 10
0
> INSERT INTO #tmpJobItemID SELECT JobItemID FROM OutboundQueueItem where
> JobID = @.JobID and IsLocked = 0 and AddToProcessing = 1 and OutboundType =
> @.OutboundType -- Lock the Job at OutboundQueue, and update the
status
> to "Working" -- SP:StmtCompleted 0 0 48 0
> CREATE TRIGGER tr_OutboundQueueItem_Update ON dbo.OutboundQueueItem
FOR
> UPDATE AS SP:StmtCompleted 0 0 0 0
> IF NOT UPDATE(Status) SP:StmtCompleted 0 0 0 0
> select JobItemID from inserted SP:StmtCompleted 0 0 2 0
> OPEN jobitemid_cursor -- Perform the first fetch and store the values
in
> variables. -- Note: The variables are in the same order as the
olumns --
> in the SELECT statement. SP:StmtCompleted 0 0 14 0
> FETCH NEXT FROM jobitemid_cursor INTO @.JobItemID -- Check
@.@.FETCH_STATUS
> to see if there are any more rows to fetch. SP:StmtCompleted 0 0 0 0
> WHILE @.@.FETCH_STATUS = 0 SP:StmtCompleted 0 0 0 0
> CLOSE jobitemid_cursor SP:StmtCompleted 0 0 0 0
> DEALLOCATE jobitemid_cursor SP:StmtCompleted 0 0 0 0
> update OutboundQueueItem set isLocked = 1, MachineLocked = @.Machine,
Status
> = 2, LastWorkingDate = getDate() where JobItemID in (select tJobItemID
from
> #tmpJobItemID) -- return recordset based on the sequence --
select
> * from Outbou SP:StmtCompleted 0 0 68 0
> select * from OutboundQueueItem where JobItemID in (select tJobItemID from
> #tmpJobItemID) order by submissiondate SP:StmtCompleted 0 0 18 0
> COMMIT Tran SP:StmtCompleted 0 0 0 0
> getjobitems 'aaa', 1 SQL:StmtCompleted 62 62 66 0
> SET STATISTICS PROFILE OFF SQL:StmtCompleted 0 0 0 0
> thanks!
>
> "Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
> news:309826632428962507725664@.news.microsoft.com...
tell
> information
> occurs.
>

Beginner: How to group data?

Hello,
Thanks for reviewing my question. I am new to reporting services and
experimented with a couple of reports. I have the following data in a table:
# name slot
1 A1 1
1 B1 2
1 C1 3
2 A2 1
2 B2 2
2 C2 3
I would like to output in this fashison:
1 2
-- --
1 A1 A2
2 B1 B2
3 C1 C2
Should I be looking to use a Matrix objecjt? Do I need to use JOINs to
group rows/columns even though all my data I need is in my table?
Many Thanks
PeterA follow up:
I am not familar with how the reporting mechanism groups the data. I have a
long list of data already contained in a table.
Current list
1 Book A Action
1 Book B Drama
2 Book C Novel
2 Book D Action
Desired output
1 2
Action Book A Book D
Drama Book B
Novel Book C
Can I output the data in the desired output or do I have to do queries with
cross joins or something of that sort?
Many Thanks
Peter
"Peter" wrote:
> Hello,
> Thanks for reviewing my question. I am new to reporting services and
> experimented with a couple of reports. I have the following data in a table:
> # name slot
> 1 A1 1
> 1 B1 2
> 1 C1 3
> 2 A2 1
> 2 B2 2
> 2 C2 3
> I would like to output in this fashison:
> 1 2
> -- --
> 1 A1 A2
> 2 B1 B2
> 3 C1 C2
> Should I be looking to use a Matrix objecjt? Do I need to use JOINs to
> group rows/columns even though all my data I need is in my table?
> Many Thanks
> Peter
>

beginner: "Login failed for user sa."

Hello,
(sorry for my English...)
Could you help me with a SQL Server 2005 problem?
I had installed SQL Server 2005 and then I tried to setup some application
using SQL Server. Unfortunatelly setup fails because the application cannot
logon to SQL Server in SQL Server Authentication mode (user 'sa', password
'sa'). I checked Server Management Studio Express: I have sa/sa account
(because I've prepared it), but although I can logon in Windows
Authentication mode, I cannot logon in SQL Server Authentication (sa/sa)
because of:

Login failed for user 'sa'. The user is not associated with a trusted SQL
Server connection (Microsoft SQL Server, Error: 18452).

Could you help me plase? I suspect that solution is simple but my experience
is not enough.
Thank you very much.
/RAMtake a look here
http://sqlservercode.blogspot.com/2...reason-not.html|||Andrzej Magdziarz (andrzej.magdziarz@.wp.pl) writes:
> Could you help me with a SQL Server 2005 problem? I had installed SQL
> Server 2005 and then I tried to setup some application using SQL Server.
> Unfortunatelly setup fails because the application cannot logon to SQL
> Server in SQL Server Authentication mode (user 'sa', password 'sa').

That is not a very good password. :-)

> I checked Server Management Studio Express: I have sa/sa account
> (because I've prepared it), but although I can logon in Windows
> Authentication mode, I cannot logon in SQL Server Authentication (sa/sa)
> because of:
> Login failed for user 'sa'. The user is not associated with a trusted SQL
> Server connection (Microsoft SQL Server, Error: 18452).
> Could you help me plase? I suspect that solution is simple but my
> experience is not enough.

By default, SQL Server accepts only logins through Windows authentication,
and you must explicitly permit SQL authentication. Your first chance
to so is during setup, but you can also do this from Mgmt Studio.
Right-click the server itself in the Object Explorer, select Properies
and then find the Security page.

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

beginner.. :-)

Hello there

I am a very beginner..

I need to know if this is the write structure for doing this:

1. this func call for 4 other func: is it suppose to look like this?

2. with what i should replace the RETURNS int if its return date & int

thanks

ALTER FUNCTION [dbo].[StatisticsEx2_0Func]

(

@.Date_M_Y smalldatetime ,

@.FK nchar (20) ,

@.BizID int

)

RETURNS int

AS

BEGIN

DECLARE @.ResultVar int

set @.ResultVar = (SELECT

dbo.StatisticsEx_2_1_SingleMonthlySumFunc.A, dbo.Statistics_2_1_SingleAnnualAvgFunc.B, dbo.Statistics_2_1_AllGropsMonthlyTotalFunc.C, dbo.StatisticsEx_2_1_AllGroupsAnnualAvgFunc.D

FROM dbo.Statistics_2_1_AllGropsMonthlyTotalFunc, dbo.Statistics_2_1_SingleAnnualAvgFunc, dbo.StatisticsEx_2_1_AllGroupsAnnualAvgFunc, dbo.StatisticsEx_2_1_SingleMonthlySumFunc)

RETURN @.ResultVar

END

If you want to return more than one value then use Inline Table /Table Valued UDF.

If you use sql server 2005 then you can utilize the UDT (.NET Class) to return as Structure.

Create Function [dbo].[StatisticsEx2_0Func]()

Returns table

as

Return

(

Select 100 i, Cast('1/1/2007' as Datetime) d

)

go

Select * from [dbo].[StatisticsEx2_0Func]()

|||

amm..

the "mother" func suppose to get 3 parm – every child func use 2 param out of the 3

then let the table

so...?

Create Function [dbo].[StatisticsEx2_0Func]()

Returns table

as

Return

(

Select

@.Date_M_Y smalldatetime ,

@.BizID int ,

@.FK

)

go

Beginner with crosstab queries

I am having headaches trying to display some data in one table.
An example of some of the contents from the table are below:

ID ENTRY_ID SCHOOL
--------
1 10 Arizona
1 20 Arizona State
1 30 Texas
2 10 Baylor
2 20 Texas
3 10 Colorado

As you can see each ID has its own entry id, depending on how many school are assigned to that ID. The table has 1 to up to 10 entry_ids for each ID. The schools can be any of 70+ schools in the database, not just these that I listed.

I would like to list each school in its own column like below:

school1 school2 school3
----------
Arizona Arizona State Texas

school1 school2 school3
----------
Baylor Texas null

school1 school2 school3
----------
Colorado null null

I already know ahead of time that the highest count of entry_id per id is 10 because of a Having query I ran. Will I need to create 10 separate Select statements for each scenario??

This is what I have so far, but this is for ids with 5 entry_ids:
SELECT a.school, b.school, c.school, d.school, e.school
FROM table a,
table b,
table c,
table d,
table e
WHERE a.id = b.id
AND b.id = c.id
AND c.id = d.id
AND d.id = e.id
AND a.school > b.school
AND b.school > c.school
AND c.school > d.school
AND d.school > e.school

Unfortunately, it will not work for anything other than rows with 5 entry_ids per ID. I would hate to write out 10 different UNIONs if there is an easier way.

Your help is much appreciated and I hope I have given enough detail for an answer.Try this link (http://asktom.oracle.com/pls/ask/f?p=4950:8:7213782571237635933::NO::F4950_P8_DISPL AYID,F4950_P8_CRITERIA:7086279412131,)
:rolleyes:

Beginner T-SQL Question

I'd like to query my Tasks and TaskActivity Tables to return the following results:

Case Id
Estimated Hours
Total Hours
Remaining Hours

Because I'm an SQL novice, I've had to resort to a hack of creating three different views to get the results I want: I'm sure there's a better way (i.e. a single, well-formed query) but when I try that my numbers are all wrong.

Here's my hack to get the correct results:

First I have a view to get my Estimated Hours and CaseId from my TASKS table:
SELECT COALESCE (SUM(EstHrs), 0.00) AS EstHrs, CaseId
FROM dbo.Tasks
GROUP BY CaseId

Then I have a view to get my Total Hours and CaseId from my TASKACTIVITES table:
SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId

Finally I have a third view (that I actually use in my program) to put it all together:
SELECT vTaskActual.CaseId, vTaskEstimates.EstHrs, vTaskActual.TotalHrs,
vTaskActual.TotalHrs - vTaskEstimates.EstHrs AS RemHrs
FROM vTaskActual INNER JOIN
vTaskEstimates ON vTaskActual.CaseId = vTaskEstimates.CaseId

Any help would be greatly appreciated, and, if this is not the correct forum for newbie questions like this, please let me know.

TIA,
Rob

You can turn a query containing a single select statement into a derived table by surround it in parethesises and adding an alias. eg

select managers.name, managers.SSN, dept.departmentCode, dept.description

from department as dept

join (select name, SSN, deptid from employee where salary > 50.000) as managers

on dept.deptid = managers.deptid

So with the two views above, turn both into derived tables, and join:

select TaskActual.CaseId, TaskEstimates.EstHrs, TaskActual.TotalHrs,
TaskActual.TotalHrs - TaskEstimates.EstHrs AS RemHrs

from

(SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId
) as TaskEstimates

INNER JOIN

(SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId) as TaskActual

ON TaskActual.CaseId = TaskEstimates.CaseId

That should do it. As both derived table queries use the tasks table, it is likely that you can do the query in one query without using derived tables, but without having a better understanding of your schema and data I don't want to attempt that!

HTH

For more SQl tips, check out my blog:

|||Please indicate if this answered your question or not.|||

Thanks for the reply it was very helpful.

I did have to make a few changes to your response (see below) because my results are gathered from two different tables: Tasks contains the estimate, and TaskActivity contains the records of actual time. The info on derived tables will really help clean-up my DB.

Thanks again!

P.S. I use "COALESCE" to assign 0.00 to any NULL values in EstHrs. It is working so I assume that is correct)

Here's the final query that's working:

select TaskActual.CaseId, TaskEstimates.EstHrs, TaskActual.TotalHrs,

TaskActual.TotalHrs - TaskEstimates.EstHrs AS RemHrs

from

(SELECT COALESCE (SUM(EstHrs), 0.00) AS EstHrs, CaseId

FROM dbo.Tasks

GROUP BY CaseId

) as TaskEstimates

INNER JOIN

(SELECT dbo.Tasks.CaseId, SUM(dbo.TaskActivity.Minutes / 60.00) AS TotalHrs

FROM dbo.TaskActivity RIGHT OUTER JOIN

dbo.Tasks ON dbo.TaskActivity.TaskId = dbo.Tasks.TaskId

GROUP BY dbo.Tasks.CaseId) as TaskActual

ON TaskActual.CaseId = TaskEstimates.CaseId

Beginner to SQL server 2005 Express Edition

Hi,

I am fairly new to Visual Studio 2005 (using C#) and am writing an application for the company I work in. I have got a really nice functioning form working and am now planning what to do about the massive database I need to create (I don't think it will ever get to the 4GB limitation of SQL Server 2005 Express Edition).

Can someone please help with some simple questions please?

1: I am decoding a specific file format and extracting the information for the main database. Fortunately, each record in the data file I am decoding has unique identity codes (so this will be great for the ID of each entry). Because the software has to do this by itself (the encoded file contains more than 1,120,000 records which will shrink to about 30,000 records, all different sizes once decoded). Can each record in a SQL server database contain a different number of elements? If so, what is this known as (I have read a little about jagged arrays - is there a similar thing in SQL server databases)?

2: As the program will decode the file and assemble the database, can I make my program create the database from scratch and manipulate the database rather than me having to define a database for my program to fill? If so, where can I find some info on doing this?

3: The database file will be stored on our office network and has to be accessible to more than one person at a time (in this case, a maximum of 6 users - well within the 25 users my book says is possible with SSE2005). Is it more complex to develop multi-user access to the database?

Just in case you wonder what books I have got, they are "Introducing Microsoft SQL Server 2005 for Developers" and "Getting Started with SQL Server 2005 Express Edition".

Thank you!

1. Yes and No. You can store the information for the missing columns as NULL, but your code will have to handle that, and the columns will need to be nullable. If the file is in a standard format (eg a spreadsheet, or a csv file), you can use DTS (or SSIS in 2005) to load the file. That would be easier than writing a custom application to load the file.

2. You can build a dynamic sql statement based on the columns and column types of the tables you want to build. However, it would be easier and safer to define the database beforehand. Building tables is easy enough, just make sure you define the relationships in the correct order, and then load the data in that order aswell. You may want to leave defining relationships until after the data is loaded, just make sure the data is concurrent.

3. I am not sure, I don't know too much about this. The database schema itself would not have to be changed, you would just have to create SQL server user accounts for each user, or use windows integrated security which is much easier to set up. Then ensure they can connect to the server which the SQL server instance is based upon.

As this is a new product, you may learn more useful and up-to-date information from the books online. Microsoft are still offering a free e-learning course on 2005:

https://www.microsoftelearning.com/sqlserver2005/default.aspx

I have used parts of it and found it useful. Books tend to be overly wordy, whereas the e-learning courses get to the point quicker.

|||

Thank you,

Your reply will help me decide where to go next.

The file I am importing is not a straight-forward data file. It is a custom file format used by the British railway industry and some code is necessary to read in the data, join some elements to form a list, then the list will be placed into the database.

The database will have some fixed elements to start with, but will then be followed by my decoded list. The decoded list will contain a different number of entries. Some lists may contain around 10 entries. Some may contain as many as 200 entries.

Sean.

beginner student PL/SQL to T-SQL

This summary is not available. Please click here to view the post.

Beginner Stored Procedure Help

If create a stored procedure like this:
create procedure dbo.usp_trxn_enum
(
@.a_trxn_type_cd char(2) = null
, @.a_allowed_mainframe_file_cd char(7) = null
)
<snip other code>
Is it executed like this:
exec dbo.usp_trxn_enum 'XX', 'XXXXXXX'
Thanks,
--
JerryYes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jerry" <jerryalan@.gmail.com> wrote in message
news:1156878844.414057.127090@.74g2000cwt.googlegroups.com...
If create a stored procedure like this:
create procedure dbo.usp_trxn_enum
(
@.a_trxn_type_cd char(2) = null
, @.a_allowed_mainframe_file_cd char(7) = null
)
<snip other code>
Is it executed like this:
exec dbo.usp_trxn_enum 'XX', 'XXXXXXX'
Thanks,
--
Jerry|||Jerry wrote:
> If create a stored procedure like this:
> create procedure dbo.usp_trxn_enum
> (
> @.a_trxn_type_cd char(2) = null
> , @.a_allowed_mainframe_file_cd char(7) = null
> )
> <snip other code>
> Is it executed like this:
> exec dbo.usp_trxn_enum 'XX', 'XXXXXXX'
> Thanks,
>
Yes, or
EXEC dbo.usp_trxn_enum
@.a_trxn_type_cd = 'XX',
@.a_allowed_mainframe_file_cd = 'XXXXXXX'
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Beginner Stored Procedure Help

If create a stored procedure like this:
create procedure dbo.usp_trxn_enum
(
@.a_trxn_type_cd char(2) = null
, @.a_allowed_mainframe_file_cd char(7) = null
)
<snip other code>
Is it executed like this:
exec dbo.usp_trxn_enum 'XX', 'XXXXXXX'
Thanks,
JerryYes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jerry" <jerryalan@.gmail.com> wrote in message
news:1156878844.414057.127090@.74g2000cwt.googlegroups.com...
If create a stored procedure like this:
create procedure dbo.usp_trxn_enum
(
@.a_trxn_type_cd char(2) = null
, @.a_allowed_mainframe_file_cd char(7) = null
)
<snip other code>
Is it executed like this:
exec dbo.usp_trxn_enum 'XX', 'XXXXXXX'
Thanks,
Jerry|||Jerry wrote:
> If create a stored procedure like this:
> create procedure dbo.usp_trxn_enum
> (
> @.a_trxn_type_cd char(2) = null
> , @.a_allowed_mainframe_file_cd char(7) = null
> )
> <snip other code>
> Is it executed like this:
> exec dbo.usp_trxn_enum 'XX', 'XXXXXXX'
> Thanks,
>
Yes, or
EXEC dbo.usp_trxn_enum
@.a_trxn_type_cd = 'XX',
@.a_allowed_mainframe_file_cd = 'XXXXXXX'
Tracy McKibben
MCDBA
http://www.realsqlguy.com

beginner setting up to code in VB

Hey everyone. I'm trying to set up the free microsoft sql 2000 server desktop engine so I can create databases in visual basic using ADO. I would like to do this locally, not over the network or anything. I'm on windows 98. the steps I took are:

1. I downloaded the ms 2000 server desktop engine (msde2000a.exe)
2. Downloaded the .net framework and installed that (version 1.1)
3. Extracted the desktop engine setup to a directory
4. browsed to that directory in DOS prompt and typed 'setup SAPWD="AStrongSAPwd" ... and then it installed
5. opened ODBC Data sources in the control panel
6. in the user DSN tab i highlighted "MQIS" and then pressed 'configure'
7. Pressed 'next' with the default values for all the fields: (name=MQIS, Description=SQL Server' Server=(local) )
8. with the radio button "with sql server authentication.." selected i click on 'client configuration'
9. after i click on that a box comes up saying 'add network library configuration'.

so.. i'm confused on this part.. What should i put for 'server alias' and 'server name'? I want to do this locally. I tried putting my local IP address and the word (local) but after i press 'OK' and then 'Next' (in that previous window that displays the client configuration button) i get an error that says:

Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect())
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied

So.. how do I set up my computer to be able to use the engine locally? I have the vbcode I want to work. It consists of creating a database using the "provider=SQLOLEDB..." line. I can submit the code if anyone wants to see. Any help would be greatly appreciated. Thanks!By the way, i DO NOT have SQL Server installed. I was thinking the engine would sort of be like MS Access where you don't actually need Microsoft Access installed to do database manipulation because the Access Jet Engine is built into windows. So I figured installing the SQL Server engine would do the same thing for me. Is this correct? Or is it mandatory that I have SQL Server installed?|||You will definitely need to have SQL Server installed, it won't ship as part of the OS until LongHorn. You can buy the developer edition for about $50, and you get MSDE (the engine alone, like Jet is to MS-Access) included with any of Microsoft's Professional level developer products.

-PatP

Beginner seeks help

My company needs a database. My first thought was to do it in Access, because it's available! But I wanted to check that that was smart, what its limitations are, when it's better to move to bigger / more expensive software, etc.

It's probably going to have several hundred thousand records in and will grow by more than a hundred thousand every year. Is there a size limit?

Not that many fields, though - it's not that complicated a database.

Many thanks in advance for any help :beer:MS Access 2000 has a 2 gig limit.|||Does it still have the 1 gig limit on table size? Even that would suffice for this user's requirements, most likely.|||For this situation, Access should be doable, but here are a few things to consider:

1. How many users are you going to need to support?
2. How many concurrent users do you expect at any given time?

Hope this helps in your decision-making...

-Tim

P.S. - got any more beer? :D|||If you are looking for a low price database engine , MSDE may meet your needs . MSDE if a free sql server database desktop engine , you can download it from microsoft.com .Just like sql server enterprise edition , msde also provide an easy way to back up your important data , it supports full and diffenentail backup , for access db , you have to copy the whole file for each time you back it up .

--David
databk.com