Help on database choice?

Hi there,
I am currently writing an application for NNTP, the application requires
very FAST inserts, updates etc.
I started to write it with mySQL and I got good results, I was wondering if
anyone can confirm speed comparisions (and other comparisons) using MSDE
rather than mySQL.
As far as i know my app which runs on the desktop requires no special
licenses to use MSDE but mySQL does.
The application with only have 1 concurrent user and is being developed with
c# Visual Studio.net
Any help or information anybody can offer would be gratefully appreciated
Thanks in advance
Ian
Oh I forgot to mention, there is only 1 concurrent user but that user has
approximately 10 connections to the database. Maybe this will have an effect
on performance,.
THanks again
Ian
"Ian" <ianinmalaga@.hotmail.com> wrote in message
news:eLL8qdnZEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> I am currently writing an application for NNTP, the application requires
> very FAST inserts, updates etc.
> I started to write it with mySQL and I got good results, I was wondering
if
> anyone can confirm speed comparisions (and other comparisons) using MSDE
> rather than mySQL.
> As far as i know my app which runs on the desktop requires no special
> licenses to use MSDE but mySQL does.
> The application with only have 1 concurrent user and is being developed
with
> c# Visual Studio.net
> Any help or information anybody can offer would be gratefully appreciated
> Thanks in advance
> Ian
>
sql

Help on ANSI

Hi all!!!!!!
My requirement is to write DDL and DML scripts which are ANSI complient, so that it can run on MS SQL server 7.0 & 2000, Oracle 8i & 9i, DB@. and Sybase.
So could U plz tell me where can I find ANSI Standards for this.Originally posted by sameer_mehta198
Hi all!!!!!!

My requirement is to write DDL and DML scripts which are ANSI complient, so that it can run on MS SQL server 7.0 & 2000, Oracle 8i & 9i, DB@. and Sybase.

So could U plz tell me where can I find ANSI Standards for this.

How about:

http://webstore.ansi.org/ansidocstore/default.asp

a little google will do ya...

Help Needed: For Creating Synchronous Transform Component

Hi
I am currently trying to write a custom transform componet in c# that will take a row of data, perform a look-up via an external system,
then if there is a match then send the data from the extranel system down macth ouptut (which will have different columns to the input) and drop the data that
was read, else send the data down the unmacthed output which will be the same as the input.

So I would like to write a synchrons transform becuase I don't need read all the rows from the input buffer before I started processing, also I wish have millions of rows
load in memory.

Can this be done? also does any have explame code of how to do this? becuse I can't see how to send data down the match output buffer,
as this will have the lookup results data which will have diffent columns to the input data and how disgard the input data as well.

Thanks Steve

There are a whole host of samples now out, as well as those that shipped in the box, one of them will help I'm sure-

http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=SQL%20Server%20SSIS%20Sample%20Component&DisplayLang=en

(Or call jamie and he'll tell you, even if he does ask me!)

|||

Just been talking with Steve about this offline.

Turns out that, based on his requirement, he needed an asynch component. However, the same number of rows will be coming in as are going out, its just that the "shape" of the output (i.e. the metadata) needs to change.

Steve doesn't need to cache all the data in memory which is usually what asynch components do. They don't have to though. ProcessInput() can push rows to the output as soon as they are encountered without storing internally - thus giving the "illusion" of it being a synch component.

-Jamie

|||

I would first like to clear up some confusion on asynchronous outputs (I have tried before but it persists :)). Having an async output does not mean that the component waits until all the data has been seen to output any data. All it means is that the data coming out of the component is a copy in a new buffer. The Union All transform has an async output but it clearly does not wait until it receives all the data from all its inputs before outputing any data. In fact, most of the stock components with async outputs start generating output data well before all the input data has been seen.

In specific anwer to your question you can do one of 2 things. The first is to add additional output columns to the match output and keep it in sync with the input. The 2nd is to have your match output be an async output. Normally the 1st is better if you can do it because you don't have a memory copy. However, since you stated you don't want to keep any data from the input data on a match then in your case making the output async is actually the better alternative because you will actually wind up saving memory by doing so (due to the internals of the buffering system that have to do with sync outputs with additional columns causing buffer row widening).

As for sample, as Darren pointed out there are plenty to choose from. Although there probably are none that have a component with one async and one sync output there will be very little difference with just looking at both a sync and async sample and mixing and matching the appropriate code that is needed.

Thanks,

Matt

|||

I had a look and could not see a sample with two synch outputs which is what I thought was required at first. Steve now has a simple example I wrote for him earlier, but for reference the only (public) asynch sample I know of is the RemoveDuplicates component which ships in the box. This does cache data, which is a good example of this style of transform, but since all he work is done in ProcessInput, although it would spane several invocations of this method, it is fairly easy to see how you can change this blocking nature, and start passing rows straight to the output buffer as soon as received from the input.

|||

Interesting thread. I feel a blog post coming on but in this case I'm gonna leave it to Steve as he is keen to share what he's learnt here. Keep a look out!

-Jamie

|||

I was hoping for a little bit more information about Asynchronous output, especially where the input is sent to the output right away instead of using blocking.

Is there any more information on developing Async components besides what has already listed? I've already looked at Remove Duplicates from the samples but find it a litte confusing without a document to go with it explaining the process and why certain techniques were used.

I'm trying to create an Async component with 1 input, 3 outputs. I basically want some processing to be done that will determine which of the three outputs to go to. I'm not concerned about the decision making part (which output to go to) but more so on how to move the data to the outputs right way.

|||

If all the rows that come in, and no more than those that come in, end up in one of the outputs, then this would be best done as a synch component.

You would create your 3 outputs in ProvideComponentProperties, setting the SynchronousInputID property to that of yuor input. Since you have several outputs you also need to set the ExclusionGroup property for each output, each should have a unique value to differentiate them. You then use the DirectRow method of the PipelineBuffer to send each row to the output you decide.

Lookup the ExclusionGroup property help topic for a simple example.

Help Needed: For Creating Synchronous Transform Component

Hi
I am currently trying to write a custom transform componet in c# that will take a row of data, perform a look-up via an external system,
then if there is a match then send the data from the extranel system down macth ouptut (which will have different columns to the input) and drop the data that
was read, else send the data down the unmacthed output which will be the same as the input.

So I would like to write a synchrons transform becuase I don't need read all the rows from the input buffer before I started processing, also I wish have millions of rows
load in memory.

Can this be done? also does any have explame code of how to do this? becuse I can't see how to send data down the match output buffer,
as this will have the lookup results data which will have diffent columns to the input data and how disgard the input data as well.

Thanks Steve

There are a whole host of samples now out, as well as those that shipped in the box, one of them will help I'm sure-

http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=SQL%20Server%20SSIS%20Sample%20Component&DisplayLang=en

(Or call jamie and he'll tell you, even if he does ask me!)

|||

Just been talking with Steve about this offline.

Turns out that, based on his requirement, he needed an asynch component. However, the same number of rows will be coming in as are going out, its just that the "shape" of the output (i.e. the metadata) needs to change.

Steve doesn't need to cache all the data in memory which is usually what asynch components do. They don't have to though. ProcessInput() can push rows to the output as soon as they are encountered without storing internally - thus giving the "illusion" of it being a synch component.

-Jamie

|||

I would first like to clear up some confusion on asynchronous outputs (I have tried before but it persists :)). Having an async output does not mean that the component waits until all the data has been seen to output any data. All it means is that the data coming out of the component is a copy in a new buffer. The Union All transform has an async output but it clearly does not wait until it receives all the data from all its inputs before outputing any data. In fact, most of the stock components with async outputs start generating output data well before all the input data has been seen.

In specific anwer to your question you can do one of 2 things. The first is to add additional output columns to the match output and keep it in sync with the input. The 2nd is to have your match output be an async output. Normally the 1st is better if you can do it because you don't have a memory copy. However, since you stated you don't want to keep any data from the input data on a match then in your case making the output async is actually the better alternative because you will actually wind up saving memory by doing so (due to the internals of the buffering system that have to do with sync outputs with additional columns causing buffer row widening).

As for sample, as Darren pointed out there are plenty to choose from. Although there probably are none that have a component with one async and one sync output there will be very little difference with just looking at both a sync and async sample and mixing and matching the appropriate code that is needed.

Thanks,

Matt

|||

I had a look and could not see a sample with two synch outputs which is what I thought was required at first. Steve now has a simple example I wrote for him earlier, but for reference the only (public) asynch sample I know of is the RemoveDuplicates component which ships in the box. This does cache data, which is a good example of this style of transform, but since all he work is done in ProcessInput, although it would spane several invocations of this method, it is fairly easy to see how you can change this blocking nature, and start passing rows straight to the output buffer as soon as received from the input.

|||

Interesting thread. I feel a blog post coming on but in this case I'm gonna leave it to Steve as he is keen to share what he's learnt here. Keep a look out!

-Jamie

|||

I was hoping for a little bit more information about Asynchronous output, especially where the input is sent to the output right away instead of using blocking.

Is there any more information on developing Async components besides what has already listed? I've already looked at Remove Duplicates from the samples but find it a litte confusing without a document to go with it explaining the process and why certain techniques were used.

I'm trying to create an Async component with 1 input, 3 outputs. I basically want some processing to be done that will determine which of the three outputs to go to. I'm not concerned about the decision making part (which output to go to) but more so on how to move the data to the outputs right way.

|||

If all the rows that come in, and no more than those that come in, end up in one of the outputs, then this would be best done as a synch component.

You would create your 3 outputs in ProvideComponentProperties, setting the SynchronousInputID property to that of yuor input. Since you have several outputs you also need to set the ExclusionGroup property for each output, each should have a unique value to differentiate them. You then use the DirectRow method of the PipelineBuffer to send each row to the output you decide.

Lookup the ExclusionGroup property help topic for a simple example.

sql

Help needed with an update trigger

Hi All,
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
ThanksJason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) =
POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) =
> POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:

>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed with an update trigger

Hi All,
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
ThanksJason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) = POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) => POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>> Hi All,
>> I need to write a trigger to catch all updates made to a table and insert
>> the old and new values into a new table.
>> The problem is that the table being "Audited" has a lot of fields in
>> (more than 120) and a trigger that was written for it takes too long to
>> execute.
>> Splitting the table up into smaller tables not an option right now
>> unfortunatly.
>> Is it possible to write an update trigger that can be fired and is clever
>> enough to only validate updated fields and still be as fast as possible.
>> Thanks
>|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:
>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed with an update trigger

Hi All,
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
Thanks
Jason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) =
POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>
|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) =
> POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>
|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:

>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed with SQL SERVER 2000 Analysis Manager

I am going through the tutorial and I got to the portion on Set Dimension Custom Rollups, Write Back and more. I selected the Parent-Child dimension, and followed all of the steps as directed until it asked me to Select advanced options. The Write Back option is not listed and it has to be selected in order for me to complete this section. Please help.Same for me, I even try to change connexion without any success
any help ?

Help Needed

hi,
i have table with following feilds

ProgramId ProgramType AccountName ProgramName ProgramBudget ProgramStatus

I need to write a search query to generate a report where search criteria will be (ProgramType=1,2,3 or All of above),(AccountName=a,b,c or All of above),(ProgramStatus=y,n or all of above)

any clues,

thnx in advance
sudheer.hi,

try something like this:

declare @.PID int, @.PTY int
set @.PID=null -- will search "all"
set @.PID=2 -- will search for ProgramID=2 etc
:
:
select *
from <tablename>
where ((@.PID is null) or (ProgramID=@.PID)) and
((@.PTY is null) or (ProgramType=@.PTY))
:

markus

Help needed

Hi all,

I am trying to write a stored procedure, which has two insert statements.

the first insert statement, is simple which inserts data into a table and returns the primary key for the new row added. using this primary key, i am writing another insert statement, which passes a list of elements which would be then entered into another table, with the primary key of the first table.

Is this possible to do in a single stored procedure? I have implemented this using two different sp, but am wondering if it can be done other way?

thanks for your help!

Hello rasesh_dave,

Yes, you can do this with one stored procedure.

The newly inserted primary key value can be retrieved with @.@.Identity.

|||

Ofcourse, just put the two insert statements into one proc.

|||

yup, but how do i pass a list / array of items to be inserted into the second table?

|||

Post the code you currently have so we can see what you are doing..

|||

Okay,

I have two stored procs. The first one is

CREATE PROCEDURE [dbo].[sp_EventNew_Insert]

@.EventName VARCHAR(50),
@.StartDate DATETIME,
@.EndDate DATETIME,
@.Organiser VARCHAR(50),
@.Telephone VARCHAR(15),
@.Fax VARCHAR(15),
@.Email VARCHAR(50),
@.EventURL VARCHAR(50),
@.PendingVerification BIT,
@.SubmitersName VARCHAR(50),
@.SubmitersEmail VARCHAR(50),
@.EventVenue VARCHAR(50),
@.EventCity VARCHAR(50),
@.EventState VARCHAR(50),
@.CountryID VARCHAR(50),
@.SubmiterName VARCHAR(50),
@.SubmiterEmail VARCHAR(50)

AS

INSERT INTO tblEventTest (EventName, CountryID, StartDate, EndDate, Venue, City, State, Organiser, Telephone, Fax,
Email, EventWebsite, DisplayURL, PendingVerification, SubmitersName, SubmitersEmail)
VALUES (@.EventName, @.CountryID, @.StartDate, @.EndDate, @.EventVenue, @.EventCity, @.EventState, @.Organiser, @.Telephone, @.Fax, @.Email, @.EventURL, @.EventURL, @.PendingVerification,
@.SubmiterName, @.SubmiterEmail)
SELECT @.@.Identity as EventID
GO

which returns the EventID which is received by the DataAdapter class, and

then executes a loop for say 10 SiteID and calls another stored procedure for each siteID which is

CREATE PROCEDURE [dbo].[sp_EventSitesNew_Insert]
@.EventID INT,
@.SiteID INT,
@.LiveOnWeb BIT
AS

INSERT into tblEventSitesTest (EventID, SiteID, LiveOnWeb)
VALUES (@.EventID, @.SiteID, @.LiveOnWeb)
GO

is there a way i can pass an array of siteID into the sp, and execute a loop without generating a loop at the Data Adapter?

|||

Here's how I would modify your proc

(1) DO NOT name your procs with "sp_..". sp_ means system proc and SQL Server looks for the proc under master db thinking its a system proc and if it doesnt find there, it will look under the db you are running queries from. So this is an unnecessary overhead.

(2) Create a function that takes a concatenated string and returns a table of parsed values. Refer:http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx

(3) Finally modify the proc as follows:

CREATE PROCEDURE [dbo].[SP_EVENTNEW_INSERT]@.EventNameVARCHAR(50),@.StartDateDATETIME,@.EndDateDATETIME,@.OrganiserVARCHAR(50),@.TelephoneVARCHAR(15),@.FaxVARCHAR(15),@.EmailVARCHAR(50),@.EventURLVARCHAR(50),@.PendingVerificationBIT,@.SubmitersNameVARCHAR(50),@.SubmitersEmailVARCHAR(50),@.EventVenueVARCHAR(50),@.EventCityVARCHAR(50),@.EventStateVARCHAR(50),@.CountryIDVARCHAR(50),@.SubmiterNameVARCHAR(50),@.SubmiterEmailVARCHAR(50),@.SiteIDVarchar(100),@.LiveOnWebBITASBEGINSET NOCOUNT ONDeclare @.EventIdINTINSERT INTO tblEventTest (EventName, CountryID, StartDate, EndDate, Venue, City, State, Organiser, Telephone, Fax,Email, EventWebsite, DisplayURL, PendingVerification, SubmitersName, SubmitersEmail)VALUES (@.EventName, @.CountryID, @.StartDate, @.EndDate, @.EventVenue, @.EventCity, @.EventState, @.Organiser, @.Telephone, @.Fax,@.Email, @.EventURL, @.EventURL, @.PendingVerification,@.SubmiterName, @.SubmiterEmail)SELECT @.EventId = SCOPE_IDENTITY()Declare @.SiteIdTableTable (SiteIdINT)INSERT into @.SiteIdTableSELECT *FROM dbo.fnGetIDs (@.SiteID)INSERT into tblEventSitesTest (EventID, SiteID, LiveOnWeb)SELECT @.EventID, SiteID, @.LiveOnWebFROM @.SiteIdTableSET NOCOUNT OFFEND

|||

Thanks for the code, i am trying to implement it but is gives me an error

Server: Msg 208, Level 16, State 1, Procedure sp_EventNew_Insert, Line 35
Invalid object name 'dbo.fnGetIDs'.

I am trying this in query analyser! i dont know if i neet to add any udf as well with that?

|||

You need to create the function based on the code I provided in the article link.

|||

Thanks Dinakar,

I wonder how much efficient code a developer can write, if they know the system copletely!

|||

rasesh_dave:

Thanks Dinakar,

I wonder how much efficient code a developer can write, if they know the system copletely!

Definetely...

Help need to write a Query in VB - MsSql !

Hai ,

My database is Ms Sql and I am devolping in VB
The below is my query, it seems to be working but at the last there is some problem. If possible kindly correct the query. Actually the problem is the SalesIn Quantity value is not shown correctly, it seems to be working in SP which I created in Ms Sql, so I tried the same here, I hope with some modifications it could be correctly executed.

sql = "select Item as Itemid,Itemid as SoldItemId,Date as SoldDate,"
sql = sql & "(Qty * Unitcost) as Cost,Qty as SoldQty,ItemId as StockId,"
sql = sql & "Qty as StockQty,ItemId as SalesinId,Qty as SalesInQty from"
sql = sql & " Stock,Sales,StockDetail,SalesIn where Stock.Itemid *= Sales.Itemid AND "
sql = sql & "StockDetail.Itemid = Stock.Itemid AND SalesIn.ItemId = Sales.ItemId AND "
sql = sql & "StockDetail.WareHouse ='" & Text3.Text & "' AND SalesIn.Type ='" & Text4.Text & "' AND "
sql = sql & "Date Between '" & Text1.Text & "' AND '" & Text2.Text & "' Group By"
sql = sql & " Stock.ItemId,StockDetail.ItemId,Sales.ItemId,Sales In.ItemId,Sales.Date,Sales.Qty,Sales.UnitCost,Stoc kDetail.Qty,"
sql = sql & "SalesIn.Qty order by Stock.ItemId"
rst.Open sql, cnn, adOpenStatic, adLockReadOnly, adCmdText

Everything is coming correct except the SalesIn Quantity. If I remove and try means then the other things are showing correctly. I mean the ItemId and the Soldqty and the stockqty and everything is showing correctly here just for my reference I am showing all the ItemId.

Kindly view and reply me.

SalesTable, StockTable, StockDetail, SalesIn are the Four table I am taking here. All the four tables are Linked by the ItemId.

Thank you very much,
Chockyou need to qualify the columns in the SELECT the same way you have qualified them in the GROUP BY

suggestion: switch immediately to JOIN syntax rather than the "old style" joins using that darned asterisk beside the equal sign
select Stock.Item as Itemid
, StockDetail.Itemid as SoldItemId
, Sales.Date as SoldDate
, Sales.Qty * Sales.Unitcost as Cost
, Sales.Qty as SoldQty
, Stock.ItemId as StockId
, StockDetail.Qty as StockQty
, SalesIn.ItemId as SalesinId
, SalesIn.Qty as SalesInQty
from Stock
inner
join StockDetail
on Stock.Itemid = StockDetail.Itemid
left outer
join Sales
on Stock.Itemid = Sales.Itemid
left outer
join SalesIn
on Sales.ItemId = SalesIn.ItemId
where Sales.Date Between 'Text1.Text'
and 'Text2.Text'
and StockDetail.WareHouse = 'Text3.Text'
and SalesIn.Type ='Text4.Text'
group
by Stock.ItemId
, StockDetail.ItemId
, Sales.ItemId
, SalesIn.ItemId
, Sales.Date
, Sales.Qty
, Sales.UnitCost
, StockDetail.Qty
, SalesIn.Qty
order
by Stock.ItemIdfinal tip: never use a reserved word like Date as a column name

rudy
http://r937.com/|||Hi,

I modified the Left outer join as you said, the below is the query which i am currently using.

sql = "select Itemid as StockId,Itemid as StkDetailId,
ItemId as SoldItemId,ItemId as SalesinId,
SellingDate as SoldDate,"
sql = sql & "(SoldQty * UnitCost) as Cost,SoldQty as SoldQty,"
sql = sql & "StockQty as QtyInHnd,SaleinQty as SalesInQty from"
sql = sql & " Stock inner join StockDetail on
Stock.Itemid = StockDetail.Itemid"
sql = sql & " left outer join Sales on Stock.Itemid = Sales.Itemid"
sql = sql & " left outer join SalesIn on Sales.ItemId = SalesIn.Itemid"
sql = sql & " where Sales.Date Between '" & Text1.Text &
"' AND '" & Text2.Text & "' AND StockDetail.WareHouse ='" &
Text3.Text & "' AND "
sql = sql & "SalesIn.Type ='" & Text4.Text & "' Group By "
sql = sql & "Stock.Itemid,StockDetail.Itemid,Sales.Itemid,
SalesIn.Itemid,Sales.SellingDate,Sales.SoldQty,
Sales.UnitCost,StockDetail.QtyinHand,
SalesIn.SalesInQty order by Stock.ItemId

its executing, but I didn't get the output correctly, It didn't shows the record as per the Left Outer Join. Actually I need the output as below

StkId SoldCost QtyInHand SoldQty SaleInQty SDate SalesIn WareHouse
sl001 120 4 2 2 05/01/03 00 01
sl002 0 10 0 0 00 01
sl003 30 2 10 0 05/01/03 00 01
sl004 0 120 0 0 00 01

whethere the Item Sold or not all the Item Id should be listed with their details. The Stock and StockDetail is the Master for the ItemId, So i take that as Inner Join , now what I am getting the oupt put is

sl001 60 4 1 2 05/01/03 00 01
sl001 60 4 1 2 05/01/03 00 01
sl003 30 2 10 0 05/01/03 00 01

if above is not clear, I have attatched the Excel sheet.|||hai friend,

Now I am trying like this, will it work, is the way I am writing is correct or not. kindly let me know also now I will post this to UA,

here I am facing the error

Run-time error '-2147217900(80040e14)':
The Column Prefix 'subquery' doesnot match with a tablename or alias name used in the query.

sql1 = "select distinct ItemId,Description from Stock"
sql2 = "select Itemid,SoldQty,UnitCost,SoldDate from Sales where Sales.SoldDate Between ='" & Text1.Text & "' AND '" & Text2.Text & "'"

sql3 = "select distinct ItemId,WareHouse,StockQty from StockDetail where WareHouse ='" & Text3.Text & "'"

sql4 = "select distinct SalesType,ItemId,Date,ItemId,SalesInQty from Stock Left Join SalesIn on Stock.ItemId = SalesIn.ItemId where SalesInType ='" & Text4.Text & "' AND SalesIn.Date ='" & Text1.Text & "' AND '" & Text2.Text & "'

this is the Subquery

subquery = "select sql1.StockItemId,sql1.Description,sql2.SalesItemId ,"
subquery = subquery & "(sql2.SoldQty * sql2.Cost),sql2.SoldDate,sum(sql3.StockQty),"
subquery = subquery & "sum(sql4.SalesInQty) from "
subquery = subquery & "(((sql1 Left Join Sql2 on sql1.StockItemId=sql2.SaleItemId) Left Join sql3 on sql1.StockItemId = sql3.StockDetail.ItemId) Left Join sql4 on sql1.StockItemId = sql4.SalesInItemId) order by sql1.StockItemId"
this is the main query
mainquery = "select subquery.StockItemId,subquery.Decription,subquery. SoldItemId,"
mainquery = mainquery & "(subquery.SoldQty * subquery.Cost),subquery.SoldDate,subquery.StockQty ,"
mainquery = mainquery & "subquery.SalesInQty"

kindly when you have time view and reply me

Thankyou very much,
Chock.

help me....

hi everyone

i got 2 questions plz

1- how to use the datadiff function ....plz write the code

2- is there a Trim function or statment that cuts the unneeded spaces before and after the word

thanx ...

Hi there,
1. http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_5vxi.asp?frame=true
samples imcluded :-)
2. What do you mean by unneeded ? You can trail characters, by using the function which I wrote some time ago:
CREATE FUNCTION dbo.fn_removetrailingchars
(
@.strValue VARCHAR(200),
@.TrailingChar VARCHAR(200),
@.RemoveLeading BIT
)
--Coded by Jens Suessmeyer, 2005 Available on http://www.sqlserver2005.de
RETURNS VARCHAR(200)
AS
BEGIN

DECLARE @.intCount int
SET @.intCount = 0

WHILE @.intCount <= LEN(@.strValue)
BEGIN
SET @.intCount = @.intCount +1
IF SUBSTRING(@.strValue, @.intCount, 1) NOT LIKE @.TrailingChar
BREAK
ELSE
CONTINUE
END
IF @.RemoveLeading = 1
SET @.strValue =
REVERSE(dbo.fn_removetrailingchars(REVERSE(RIGHT(@.strValue,
LEN(@.strValue) - @.intCount +1 )),@.TrailingChar,0))
ELSE
SET @.strValue = RIGHT(@.strValue, LEN(@.strValue) - @.intCount +1
)

RETURN @.strValue
END

Could be called by: Select dbo.fn_removetrailingchars (SomeColumn,CHAR(32),1) FROM Sometable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

thanx for answering

but what i mean is a function or starment Called trim which removes the spaces ....and i think it should be used in the insertion

for an example lets say wa have a string " hi " i want to remove the spaces before and after hi

so how can i use it ?

|||Hi,

if you don't want to use my function, simply use LTRIM(RTRIM(Expression))

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Help me!

Hi Master!
I'm students in NIIT. I need info of Data management Strategies of SQL
Server to write Project and I dont' know Find Topic where. Pls send to me
Everything like i talk above.
I speak Eng not good, beacause I'm VietNamese. So sorry!!
Pls send back faster, i very need it.
Thanks for your Help!!
Hi
"Inside Microsoft SQL Server 2000" by Kalen Delaney
Hardcover: 1088 pages
Publisher: Microsoft Press; Book & CD edition (November 15, 2000)
Language: English
ISBN: 0735609985
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thanh Hai" wrote:

> Hi Master!
> I'm students in NIIT. I need info of Data management Strategies of SQL
> Server to write Project and I dont' know Find Topic where. Pls send to me
> Everything like i talk above.
> I speak Eng not good, beacause I'm VietNamese. So sorry!!
> Pls send back faster, i very need it.
> Thanks for your Help!!
>
>
|||http://www.microsoft.com/technet/its.../smfstomg.mspx
"Thanh Hai" wrote:

> Hi Master!
> I'm students in NIIT. I need info of Data management Strategies of SQL
> Server to write Project and I dont' know Find Topic where. Pls send to me
> Everything like i talk above.
> I speak Eng not good, beacause I'm VietNamese. So sorry!!
> Pls send back faster, i very need it.
> Thanks for your Help!!
>
>

Help me!

Hi Master!
I'm students in NIIT. I need info of Data management Strategies of SQL
Server to write Project and I dont' know Find Topic where. Pls send to me
Everything like i talk above.
I speak Eng not good, beacause I'm VietNamese. So sorry!!
Pls send back faster, i very need it.
Thanks for your Help!!Hi
"Inside Microsoft SQL Server 2000" by Kalen Delaney
Hardcover: 1088 pages
Publisher: Microsoft Press; Book & CD edition (November 15, 2000)
Language: English
ISBN: 0735609985
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thanh Hai" wrote:
> Hi Master!
> I'm students in NIIT. I need info of Data management Strategies of SQL
> Server to write Project and I dont' know Find Topic where. Pls send to me
> Everything like i talk above.
> I speak Eng not good, beacause I'm VietNamese. So sorry!!
> Pls send back faster, i very need it.
> Thanks for your Help!!
>
>|||http://www.microsoft.com/technet/itsolutions/cits/mo/smf/smfstomg.mspx
"Thanh Hai" wrote:
> Hi Master!
> I'm students in NIIT. I need info of Data management Strategies of SQL
> Server to write Project and I dont' know Find Topic where. Pls send to me
> Everything like i talk above.
> I speak Eng not good, beacause I'm VietNamese. So sorry!!
> Pls send back faster, i very need it.
> Thanks for your Help!!
>
>

Help me!

Hi Master!
I'm students in NIIT. I need info of Data management Strategies of SQL
Server to write Project and I dont' know Find Topic where. Pls send to me
Everything like i talk above.
I speak Eng not good, beacause I'm VietNamese. So sorry!!
Pls send back faster, i very need it.
Thanks for your Help!!Hi
"Inside Microsoft SQL Server 2000" by Kalen Delaney
Hardcover: 1088 pages
Publisher: Microsoft Press; Book & CD edition (November 15, 2000)
Language: English
ISBN: 0735609985
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thanh Hai" wrote:

> Hi Master!
> I'm students in NIIT. I need info of Data management Strategies of SQL
> Server to write Project and I dont' know Find Topic where. Pls send to me
> Everything like i talk above.
> I speak Eng not good, beacause I'm VietNamese. So sorry!!
> Pls send back faster, i very need it.
> Thanks for your Help!!
>
>|||http://www.microsoft.com/technet/it...f/smfstomg.mspx
"Thanh Hai" wrote:

> Hi Master!
> I'm students in NIIT. I need info of Data management Strategies of SQL
> Server to write Project and I dont' know Find Topic where. Pls send to me
> Everything like i talk above.
> I speak Eng not good, beacause I'm VietNamese. So sorry!!
> Pls send back faster, i very need it.
> Thanks for your Help!!
>
>

Help Me Write This Query

Sorry for my newbieness:
I have a table (Cases) where Case_Number is the unique id field.
Another table (CommentsTable) contains these fields
Case_Number
Comment_Date
Comment
As users enter comments to the database, the comment gets a date/time
stamp (Comment_Date) and is recorded in the Comment field, and the case
number for that comment is also recorded.
I want my query to return fields from the Cases table, but also return
the last comment from the CommentsTable. I know I need to use the
Case_Number and MAX(Comment_Date) then return the Comment for that max
date and that case number, and I'm pretty sure this will be a sub
query, I just can't figure out how to put it all together.
Could somebody get me going in the right direction?
TIA,
todTod wrote:
> Sorry for my newbieness:
> I have a table (Cases) where Case_Number is the unique id field.
> Another table (CommentsTable) contains these fields
> Case_Number
> Comment_Date
> Comment
>
select * from CommentsTable ct where not exists(select 1 from
CommentsTable ct1
where ct.Case_Number = ct1.Case_Number and ct.Comment_Date <
ct1.Comment_Date)|||Tod wrote:
> Sorry for my newbieness:
> I have a table (Cases) where Case_Number is the unique id field.
> Another table (CommentsTable) contains these fields
> Case_Number
> Comment_Date
> Comment
> As users enter comments to the database, the comment gets a date/time
> stamp (Comment_Date) and is recorded in the Comment field, and the case
> number for that comment is also recorded.
> I want my query to return fields from the Cases table, but also return
> the last comment from the CommentsTable. I know I need to use the
> Case_Number and MAX(Comment_Date) then return the Comment for that max
> date and that case number, and I'm pretty sure this will be a sub
> query, I just can't figure out how to put it all together.
> Could somebody get me going in the right direction?
> TIA,
> tod
Untested:
SELECT C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases AS C
JOIN dbo.CommentsTable AS T
ON C.Case_Number = T.Case_Number
WHERE T.Comment_Date =
(SELECT MAX(Comment_Date)
FROM dbo.CommentsTable
WHERE Case_Number = T.Case_Number);
Unless (Case_Number, Comment_Date) is unique in the CommentsTable you
could still get more than one row per Case_Number.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This might be neater if you are returning a single case. The Case_Number is
stored in the @.CaseNumber variable in this example:
SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases C
INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
WHERE T.Case_Number = @.CaseNumber
ORDER BY T.Comment_Date DESC
If you wish to remove time adjustments from the equation (e.g. daylight
saving adjustments) then you could add an IDENTITY column to the
CommentsTable table (Comment_ID) and use the following:
SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases C
INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
WHERE T.Case_Number = @.CaseNumber
ORDER BY T.Comment_ID DESC
Chris
"David Portas" wrote:

> Tod wrote:
> Untested:
> SELECT C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases AS C
> JOIN dbo.CommentsTable AS T
> ON C.Case_Number = T.Case_Number
> WHERE T.Comment_Date =
> (SELECT MAX(Comment_Date)
> FROM dbo.CommentsTable
> WHERE Case_Number = T.Case_Number);
> Unless (Case_Number, Comment_Date) is unique in the CommentsTable you
> could still get more than one row per Case_Number.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanx. I think this is closest to what I need.
Chris Howarth wrote:
> This might be neater if you are returning a single case. The Case_Number i
s
> stored in the @.CaseNumber variable in this example:
> SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases C
> INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
> WHERE T.Case_Number = @.CaseNumber
> ORDER BY T.Comment_Date DESC
>
> If you wish to remove time adjustments from the equation (e.g. daylight
> saving adjustments) then you could add an IDENTITY column to the
> CommentsTable table (Comment_ID) and use the following:
> SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases C
> INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
> WHERE T.Case_Number = @.CaseNumber
> ORDER BY T.Comment_ID DESC
> Chris
>
> "David Portas" wrote:
>

Help me write sql script

Hi I need to create query which would calculate weekly change of some values. There is table with values for every day. At the end of week I need to calculate % of change. It is something like this:

SELECT ((LastFridayValue - PreviousFridayValue) / PreviousFridayValue) * 100 from myTable.

or it could be something like this:

(LastValue - FirstValue) / FirstValue * 100 from top 5 values from my table order by ID DESC.

Please help me translate this into real sql query :)

Could you provide your table structure with some data example like last two weeks?

Thanks

|||There are two related tables. First table tblValues has structure like this:

ItemID

DayID

Value

1

261

1086,8986

2

262

1110,3700

3

263

1110,3700

4

264

1167,9900

5

265

1121,2900

6

266

1121,2900

7

267

1100,9600

8

268

1100,9600

9

269

1061,1000

10

270

1061,1000

11

271

985,6700

12

272

918,1300

13

273

908,5200

14

274

908,5200

and tblDate has structure like this

DayID

Date

261

28.1.2007

262

29.1.2007

263

30.1.2007

264

31.1.2007

265

1.2.2007

266

2.2.2007

267

3.2.2007

268

4.2.2007

269

5.2.2007

270

6.2.2007

271

7.2.2007

272

8.2.2007

273

9.2.2007

274

10.2.2007

|||

try this I hope it will point you in good direction:

createtable #test1(ItemIDint,

DayID

int,Valuenumeric(12,4))

insert

into #test1

Values

(1, 261, 1086.8986)

insert

into #test1

Values

(2, 262, 1110.3700)

insert

into #test1

Values

(3, 263, 1110.3700)

insert

into #test1

Values

(4, 264, 1167.9900)

insert

into #test1

Values

(5, 265, 1121.2900)

insert

into #test1

Values

(6, 266, 1121.2900)

insert

into #test1

Values

(7, 267, 1100.9600)

insert

into #test1

Values

(8, 268, 1100.9600)

insert

into #test1

Values

(9, 269, 1061.1000)

insert

into #test1

Values

(10, 270, 1061.1000)

insert

into #test1

Values

(11, 271, 985.6700)

insert

into #test1

Values

(12, 272, 918.1300)

insert

into #test1

Values

(13, 273, 908.5200)

insert

into #test1

Values

(14, 274, 908.5200)

create

table #days(DayIDint,

Date

datetime)

insert

into #days

Values

(261,convert(datetime,'28.1.2007',104))

insert

into #days

Values

(262,convert(datetime,'29.1.2007',104))

insert

into #days

Values

(263,convert(datetime,'30.1.2007',104))

insert

into #days

Values

(264,convert(datetime,'31.1.2007',104))

insert

into #days

Values

(265,convert(datetime,'1.2.2007',104))

insert

into #days

Values

(266,convert(datetime,'2.2.2007',104))

insert

into #days

Values

(267,convert(datetime,'3.2.2007',104))

insert

into #days

Values

(268,convert(datetime,'4.2.2007',104))

insert

into #days

Values

(269,convert(datetime,'5.2.2007',104))

insert

into #days

Values

(270,convert(datetime,'6.2.2007',104))

insert

into #days

Values

(271,convert(datetime,'7.2.2007',104))

insert

into #days

Values

(272,convert(datetime,'8.2.2007',104))

insert

into #days

Values

(273,convert(datetime,'9.2.2007',104))

insert

into #days

Values

(274,convert(datetime,'10.2.2007',104))

select

day1 [WeekDay],(cc.value-dd.value) [difference],*from(select day1,max(dayID) cur,MIN(dayID) prevfrom(selectdatepart(weekday,date) day1, dayIDfrom #days

where

date>dateadd(day,-13,getdate()))aa

group

by day1)aa

left

join #test1 ccON cc.dayid=aa.cur

left

join #test1 ddON dd.dayid=aa.prev

select

*from #test1

drop

table #days

drop

table #test1

Help me write my first Update Trigger (sql svr 2000)

can someone help me write a Trigger? I have never written a trigger. This is for SQL Server 2000

Table FOO:
----
ID (numberic counter)
Status (Char)
etc..

Table BAR:
----
ID
Status
DateUpdated (getdate())

Whenever the Status in Table FOO is updated, I need to INSERT a new record into BAR with the ID and Status

~LeCREATE TRIGGER FOO_Update ON [FOO]
FOR Insert, Update
AS
Insert into Bar
(ID,
Status,
DateUpdated)
Select ID,
Status,
Getdate()
From inserted

...but you should really think of just adding the DateUpdated field to FOO with a default of getdate() for new records and having the trigger update it:

CREATE TRIGGER FOO_Update ON [FOO]
FOR Update
AS
Update FOO
set DateUpdated = Getdate()
From FOO
inner join inserted on FOO.ID = inserted.ID

blindman|||B.E.A.U.-tiful

Works Perfectly!

Thank you very very much!

~Le

Help me write a Search function please

Hi all,

I'm very new to ASP.NET stuffs, I'm trying to write a Search function for my website... I have two text boxes, one if called "SongTitle" and the other is "Artist"... Now I need to populate the GridView to display the result, based on the input of the textbox... So if only the "SongTitle" have input, it will search for the Song Titles on the database... if the Artist is searched, then it will return the artist... If both text boxes have value in them, then it need to check for both fields in the database and return the correct item...

For the "Artist", I have 2 columns in the Database (originalArtist and performer), so for the Artist select statement, it need to check both columns on the table, if any of them match then it will return the item.

Any help would be greatly appreciated,

Thank you all,

Kenny.

Try something like this:

SELECTFROM YourTableWHERE (@.SongTitleISNULL OR SongTitle = @.SongTitle)AND (@.ArtistISNULL OR (originalArtist = @.ArtistOR Performer = @.Artist ))

|||

ndinakar,

Thank you very much, here is what I put for my SqlDataSource, but it doesn't appear to work... Nothing returned when I try to search, using just the artist, the song, or both for the input:

<asp:SqlDataSource ID="DSResults" runat="server" ConnectionString="<%$ ConnectionStrings:notesnhacConnectionString1 %>"
SelectCommand="SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER] FROM [t_music] WHERE (@.MUSIC_TITLE IS NULL OR [MUSIC_TITLE2] LIKE '%' + @.MUSIC_TITLE + '%') AND (@.MUSIC_ARTIST IS NULL OR [MUSIC_ORIGINAL_SINGER] LIKE '%' + @.MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @.MUSIC_ARTIST + '%')">
<SelectParameters>
<asp:QueryStringParameter Name="MUSIC_TITLE" QueryStringField="title" Type="String" />
<asp:QueryStringParameter Name="MUSIC_ARTIST" QueryStringField="artist" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

|||

If you run thre query in your query analyzer with some values for the parameters does it work as expected? If not, post some sample data from your table and the query you used so we can better understand why its not working.

|||

Here is what I put on the Query window of SQL Manager Studio Express:

DECLARE @.MUSIC_TITLE NVARCHAR(100)
DECLARE @.MUSIC_ARTIST NVARCHAR(100)

SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]
FROM [t_music]
WHERE (@.MUSIC_TITLE IS NULL OR [MUSIC_TITLE] LIKE '%' + @.MUSIC_TITLE + '%') AND (@.MUSIC_ARTIST IS NULL OR [MUSIC_ORIGINAL_SINGER] LIKE '%' + @.MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @.MUSIC_ARTIST + '%')

SET @.MUSIC_TITLE = 'Everytime'
SET @.MUSIC_ARTIST = 'Cascada'

I've tried to use just @.MUSIC_TITLE, or just @.MUSIC_ARTIST, and both... each time it return ALL the records in the table... But if I execute the same select statement in VS 2005, it returns nothing... In the table, there is a song called "Everytime We Touch" performed by Cascada.

|||

Are you setting the values after running the SELECT? post some sample data from your table so I can test it on my machine..

|||

sorry. you need to use OR instead of AND in your WHERE clause.

|||

As you can see I have the SET @.MUSIC_TITLE = 'Everytime' in the previous post... Here are some sample data:

MUSIC_TITLE MUSIC_ORIGINAL_SINGER MUSIC_PERFORMER

Everytime We Touch Cascada Cascada

Dancing Queen ABBA Purity

Heaven Bryan Adam

Hotel California Eagles Eagles

Thank you very much for your help,

Kenny.

|||

Changed the "AND" to "OR" have helped, but I have some problem:

1. If I don't enter anything for the "Artist" or in the "Title" box (meaning only of the box have input), no records returned even if I type part of the name of the song/artist, or even the full name of the song/artist.

2. For example if I have two songs that have some similiar words, i.e. "Hotel", in the Title box I typed "Hotel" and in the Artist box, I typed "Eagles", which should return 1 record only that contain both Hotel & Eagles in the record... But instead it returns all the songs with "Hotel" in it.

Thanks again,

Kenny.

|||

I think we had it right the first time itself. AND should work.

|||

Dinakar,

You are right, the "AND" should work... The problem is that it only works if both the Title and Artist text box have values in them... Leaving one or the other blank does not return any results even if they should be... Any help?

Thank you,

Kenny.

|||

HEre's a sample I set up. I was able to get both records when I used "hotel" for music_title and left the second field blank.

Declare @.ttable (col1int identity, MUSIC_TITLEvarchar(50), MUSIC_ORIGINAL_SINGERvarchar(50), MUSIC_PERFORMERvarchar(50))insert into @.tvalues ('Everytime We Touch','Cascada','Cascada')insert into @.tvalues ('Dancing Queen','ABBA','Purity')insert into @.tvalues ('hotel','Bryan Adam',null)insert into @.tvalues ('Hotel California','Eagles','Cascada')DECLARE @.MUSIC_TITLENVARCHAR(100)DECLARE @.MUSIC_ARTISTNVARCHAR(100)SET @.MUSIC_TITLE ='hotel'SET @.MUSIC_ARTIST =nullSELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]FROM @.t--where ([MUSIC_ORIGINAL_SINGER] LIKE '%' + @.MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @.MUSIC_ARTIST + '%')WHERE (@.MUSIC_TITLEISNULL OR [MUSIC_TITLE]LIKE'%' + @.MUSIC_TITLE +'%')AND (@.MUSIC_ARTISTISNULL OR ([MUSIC_ORIGINAL_SINGER]LIKE'%' + @.MUSIC_ARTIST +'%'OR [MUSIC_PERFORMER]LIKE'%' + @.MUSIC_ARTIST +'%'))

|||

I tried the code above in SQL Manager Studio and they seems to work as expected... but somehow it doesn't work with my SqlDataSource! I don't know what else I have to do...

Thank you very, very much for your help Dinakar.

Kenny.

|||

I think that it doesn't work with SqlDataSource because the empty string (when nothing was entered in either one of the textbox) doesn't mean null in ADO.NET... Can anyone help me convert this to a code-behind, or help me convert that empty string to null? I would love to put this in code-behind file but like I said in my first post, I'm very new to ASP.NET.

Thanks,

Kenny.

|||

Unfortunately I dont do any .NET code so I cant help you there. Perhaps you can create a new post and someone might help you there. Posts with 0 replies have a better chance of being "looked at" than the ones with 12 replies.

help me with MSDE 2000A sp3

Hi,
i tried to install MSDE 2000A sp3, but the installation wasn't seccessful.
Can somebody help me and write me what I have to do to install MSDE 2000A sp3 with SQL authentification (and how can I add some user, database and table)?
I'm using Win XP sp2, .NET Framework 1.1 and VS .NET 2003 (but I haven't got SQL Enterprise manager)
Thank'sTry the solution in this post. Hope this helps.

http://forums.asp.net/941156/ShowPost.aspx
|||

Hi,
I tried to install MSDE via the solution in the previous post, but when I run OSQL, there is an error (SQL Server doesn't exist, or acces denied).
So, I haven't solved my problem yet.

|||The second option was to download the eval edition which will allow you to install the MSDE as a second instance and I forgot to tell you OSQL is not a very user friendly tool. If you cannot download most SQL Server books from Osborne press have the eval edition I am assuming you can get them from library check outs. Hope this helps.|||Ok,
can you send me some hyperlink to that files?
Thank's
|||

Here is the link and SQL Server developer edition is $40 less on the web. Hope this helps.
http://www.microsoft.com/sql/evaluation/trial/default.mspx

|||Thank's for help.