Need a good insert statement

Hi, I need some help. I've spent nearly a w of work and lunch time trying
to crack this nut, and I guess I am finally stuck.
I have a table with financial data like invoices. The tables are huge.
Hundreds of millions of rows per year.
I want to store some additional information related to gambling in one of
these tables.
I want to put this new data way off in the corner of the table temporarily
until I am able to come back to work.
For reasons that are private, I do not want to create a new table.
So if I could stick this information out starting at say row 104,000 and
column 333,000 that would be great. No one's going to look there. And if
they do it will look like some kind of mistake. I can't figure out how to
get the data there.
Any good Ideas would naturally be of great help. Also, since I am in sort of
a hurry (tax day is april 15) I would really appreciate it if I could just
get some of those "only the quality answers." I know a lot of people like to
yik yak, but I just need da facts. Thanks a lot!Hi
I'm not sure I understand you. Do you want to add additional column? If you
do , use ALTER TABLE TableName ADD Column.....
If you want to do paging I'd recomend you vist Aaron's web site
www.aspfaq.com .
"TargetBleigh" <ddsd*f.com> wrote in message
news:2c6dnRzmUNP2ec3fRVn-1g@.comcast.com...
> Hi, I need some help. I've spent nearly a w of work and lunch time
trying
> to crack this nut, and I guess I am finally stuck.
> I have a table with financial data like invoices. The tables are huge.
> Hundreds of millions of rows per year.
> I want to store some additional information related to gambling in one of
> these tables.
> I want to put this new data way off in the corner of the table temporarily
> until I am able to come back to work.
> For reasons that are private, I do not want to create a new table.
> So if I could stick this information out starting at say row 104,000 and
> column 333,000 that would be great. No one's going to look there. And if
> they do it will look like some kind of mistake. I can't figure out how to
> get the data there.
> Any good Ideas would naturally be of great help. Also, since I am in sort
of
> a hurry (tax day is april 15) I would really appreciate it if I could just
> get some of those "only the quality answers." I know a lot of people like
to
> yik yak, but I just need da facts. Thanks a lot!
>

Need a good idea

Hi guys
We have a following problem. For security reasons in each table in our
DB we have addition field which is calculated as hash value of all
columns in particular row.
Every time when some field in particular row is changed we create and
call select query from our application to obtain all fields for this
row and then re-calculate and update the hash value again.
Obviously such approach is very ineffective, the alternative is to
create trigger on updating event and then execute stored procedure
which will re-calculate and update the hash value. The problem with
this approach is that end user could then change the date in the
tables and then run this stored procedure to adjust hash value.
We are looking for some solution that could speed up the hash value
updating without allowing unauthorized user to do itHave you checked out CHECKSUM() in BOL?
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||If you want to enforce that values can only be changed through your
application, the best way to do that is make sure that only your application
has permissions to use certain stored procedures and tables. For this you
can use application roles.
Having a trigger on the table to calculate the hash value won't do anything
useful, because anyone who updates the table, will fire that trigger and the
hash value will updated correctly.
Jacco Schalkwijk
SQL Server MVP
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||How are you using this hash value? Is it supposed to provide some
user-authentication? Without understanding the application of this it's
difficult to recommend an alternative.
David Portas
SQL Server MVP
--|||If the Checksum() function is not sufficient, you might try a computed colum
n.
Thomas
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||Jacco raised a very good point. If your intent is to store a hash value of
some sort to indicate that a row has not been tampered with by any means
outside of your application, you should probably generate the hash code and
insert it from the application side, not via a trigger or other mechanism
internal to the database that users would have access to via QA.
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||you could continue to use a trigger, but the trigger only does something if
called by your application, so if someone changed a row via query analyser
the trigger will not fire.. see below
create trigger mytrigger on mytable after update
as
begin
if app_name() = 'myapp'
begin
' do stuff here
end
end
go
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||Thanks Mark!
The only one remark. To prevent end-user from looking and modifying the
trigger code we could create trigger using 'WITH ENCRYPTION' flag.
This flag encrypts the syscomments entries that contain the text of
CREATE TRIGGER. Using WITH ENCRYPTION prevents the trigger from being
published as part of SQL Server replication. So tamper will never know
what we check within trigger.
Mark wrote:
> you could continue to use a trigger, but the trigger only does
something if
> called by your application, so if someone changed a row via query
analyser
> the trigger will not fire.. see below
> create trigger mytrigger on mytable after update
> as
> begin
> if app_name() = 'myapp'
> begin
> ' do stuff here
> end
> end
> go
> "Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
> news:50540181.0505030708.b3397b6@.posting.google.com...
our
and
this

Need a good book...

I've been a SQL Server dba for 5 or 6 years now. With the upcoming release (eventually, I'm sure) of Yukon/SQL2005, I've read that it's important for DBA's to pick up one of the .NET languages - I've figured, I'll try to learn VB.NET - I've had a little exposure to it, and can usually figure out what's going on in VB code I've read - however, I seriously doubt I could write anything in it from scratch - I want to learn it in a bad way - can you all recommend any self-paced books that will walk me thru it? I've never had any formal training with it, don't know a class from a DLL...Thanks in advance for your help!!What did you do as a DBA in 5 or 6 years?|||lots of TSQL, performance tuning, trouble shooting - not much at all with VB...little bit of Crystal, a little ASP...|||lots of TSQL, performance tuning, trouble shooting - not much at all with VB...little bit of Crystal, a little ASP...
http://www.w3schools.com/

Need a function for banker's round

Hi all,
has anyone of you an approved banker's round function for sql?
IT needs to work with positive as well as with negative amounts...
I'd be very very happy if anyone could help me out!
Best regards
Peter Widmercan you give an example of what the output should be for +ve and -ve values?|||Hi Omnibuzz,
I'll try to get some samplevalues until tommorow from the bank. I'll post
them as soon as I got them.
Thank you!
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> schrieb im Newsbeitrag
news:730B6730-DBA8-49D5-BC30-0F16318CE506@.microsoft.com...
> can you give an example of what the output should be for +ve and -ve
> values?|||Hi, Peter
This KB article is a good starting point for informations about various
rounding methods:
http://support.microsoft.com/?kbid=196652
Here is a T-SQL function that performs Banker's rounding (tested with
the sample data provided in the above article):
CREATE FUNCTION BankersRound(@.Number numeric(38,18))
RETURNS numeric(20,0) AS BEGIN
DECLARE @.Result numeric(20,0)
SET @.Result=ROUND(@.Number,0)
IF ABS(@.Number-@.Result)=0.5 BEGIN
IF @.Result*0.5<>ROUND(@.Result*0.5,0) BEGIN
SET @.Result=@.Result-SIGN(@.Number)
END
END
RETURN @.Result
END
Razvan

Need a function

I'm looking for a string function that is similar to the INSTR
function in VB. I haven't seen anything in the help files that I can
use. Does anyone have any suggestions?

Here's what I'm trying to do:

There is a field in a table that will look something like this -
"XXXXXX - YY".
I want to separate it on the dash and get two strings out of it -
"XXXXXX" and "YY". I'm trying to keep it all in a stored procedure
and avoid a vb script or exe.

I'm envisioning something like this:

declare @.CDT datetime

select @.CDT = createdatetime from imOrderHdr
where VendorCode = 'SYG' and createdatetime is not null
and status in (1,2,3)

select d.VendorStockNumber, substring(i.ItemDescription, 1,
instr(iItemDescription, '-') - 1),
substring(i.ItemDescription, instr(iItemDescription, '-') + 1),
d.QtyOrdered, d.PurchasePrice, (d.QtyOrdered * d.PurchasePrice) as
Extension
from imOrderDetail d
join imItem i on i.ItemCode = d.ItemCode
where d.CreateDateTime = @.CDT

I'd write my own function, but the computers this will be run on have
SQL 7.

Any suggestions will be appreciated.

Thanks!
JenniferHave a look at CHARINDEX and PATINDEX to get the positions that you need for
subsequest SUBSTRING calls to parse out your data.

"Jennifer" <jennifer1970@.hotmail.com> wrote in message
news:3358f49d.0308150815.2a818c27@.posting.google.c om...
> I'm looking for a string function that is similar to the INSTR
> function in VB. I haven't seen anything in the help files that I can
> use. Does anyone have any suggestions?
> Here's what I'm trying to do:
> There is a field in a table that will look something like this -
> "XXXXXX - YY".
> I want to separate it on the dash and get two strings out of it -
> "XXXXXX" and "YY". I'm trying to keep it all in a stored procedure
> and avoid a vb script or exe.
> I'm envisioning something like this:
> declare @.CDT datetime
> select @.CDT = createdatetime from imOrderHdr
> where VendorCode = 'SYG' and createdatetime is not null
> and status in (1,2,3)
> select d.VendorStockNumber, substring(i.ItemDescription, 1,
> instr(iItemDescription, '-') - 1),
> substring(i.ItemDescription, instr(iItemDescription, '-') + 1),
> d.QtyOrdered, d.PurchasePrice, (d.QtyOrdered * d.PurchasePrice) as
> Extension
> from imOrderDetail d
> join imItem i on i.ItemCode = d.ItemCode
> where d.CreateDateTime = @.CDT
> I'd write my own function, but the computers this will be run on have
> SQL 7.
> Any suggestions will be appreciated.
> Thanks!
> Jennifer

Need a faster paging in a wesite search result page

have over million rows in the our table and we are looking forward to increase the speed of our query .Any ideas?

set ANSI_NULLS OFFset QUOTED_IDENTIFIER OFFGOALTER PROCEDURE [dbo].[mainSearch] @.startRowIndexint,@.maximumRowsint,@.rowCountint out,@.countedRowint,@.QUERY nvarchar(400)ASSELECT _ID,_NAME,_TYPE,_CREATEDATE,ESTATETYPE,ESTATEDISPLAYPRICE,ESTATEDISPLAYPRICECURRENCY,ESTATEDISTRICT,ESTATECITY,ESTATEROOMCOUNT,NUMBEROFPICTURES FROM (SELECT ROW_NUMBER() OVER (ORDER BY _CREATEDATE DESC) AS ROWRANK,*FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@.QUERY)as KEY_TBLON ADTBL._ID = KEY_TBL.[KEY]Where (_DELETIONSTATUS=0))AS RANKEDADDSWHERE ROWRANK > @.startRowIndex AND ROWRANK <= @.startRowIndex + @.maximumRows -1if(@.countedRow < 1)SET @.rowCount =(SELECT COUNT(_ID) FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@.QUERY)as KEY_TBLON ADTBL._ID = KEY_TBL.[KEY]Where _DELETIONSTATUS=0)else SET @.rowCount = @.countedRowRETURN

Do you have any idea about the Full Text Indexing?

|||

I don't know if you designed the table but have you also checked to ensure that all the proper indexes have been added ? You also might want to look at the query execution plan to see which part is consuming most of the execution time such as a hash or merge join etc.

Need a fast queue using a table

I am trying to implement a very fast queue using SQL Server.

The queue table will contain tens of millions of records.

The problem I have is the more records completed, the the slower it
gets. I don't want to remove data from the queue because I use the
same table to store results. The queue handles concurrent requests.

The status field will contain the following values:
0 = Waiting
1 = Started
2 = Finished

Any help would be greatly appreciated.

Here is a simplified script to demonstrate what has been done.

CREATE TABLE [dbo].[Queue] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[JobID] [int] NOT NULL ,
[Status] [tinyint] NOT NULL
) ON [PRIMARY]
GO

CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.NextItem
@.JobID integer,
@.ID integer output
AS
SELECT TOP 1 @.ID = [ID]
FROM Queue WITH (READPAST, XLOCK)
WHERE (Status = 0) AND (JobID = @.JobID)
RETURN
GOmy idea: i would use 3 different tables, one for waiting entries,
one for the running entries and one for the finished.
And when status is changing instead of updating field "status"
(which is now not longer necessary) move the record from one
table to the other.

hth,
Helmut

"Chris Foster" <chrisfoster@.btinternet.com> schrieb im Newsbeitrag
news:b311a0b8.0307020655.9a5beeb@.posting.google.co m...
> I am trying to implement a very fast queue using SQL Server.
> The queue table will contain tens of millions of records.
> The problem I have is the more records completed, the the slower it
> gets. I don't want to remove data from the queue because I use the
> same table to store results. The queue handles concurrent requests.
> The status field will contain the following values:
> 0 = Waiting
> 1 = Started
> 2 = Finished
> Any help would be greatly appreciated.
> Here is a simplified script to demonstrate what has been done.
> CREATE TABLE [dbo].[Queue] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [JobID] [int] NOT NULL ,
> [Status] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
> GO
> CREATE PROCEDURE dbo.NextItem
> @.JobID integer,
> @.ID integer output
> AS
> SELECT TOP 1 @.ID = [ID]
> FROM Queue WITH (READPAST, XLOCK)
> WHERE (Status = 0) AND (JobID = @.JobID)
> RETURN
> GO|||[posted and mailed, please reply in public]

Chris Foster (chrisfoster@.btinternet.com) writes:
> The queue table will contain tens of millions of records.
> The problem I have is the more records completed, the the slower it
> gets. I don't want to remove data from the queue because I use the
> same table to store results. The queue handles concurrent requests.
> The status field will contain the following values:
> 0 = Waiting
> 1 = Started
> 2 = Finished
>...
> CREATE TABLE [dbo].[Queue] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [JobID] [int] NOT NULL ,
> [Status] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
> GO
> CREATE PROCEDURE dbo.NextItem
> @.JobID integer,
> @.ID integer output
> AS
> SELECT TOP 1 @.ID = [ID]
> FROM Queue WITH (READPAST, XLOCK)
> WHERE (Status = 0) AND (JobID = @.JobID)
> RETURN
> GO

Since you have no index on JobID, I am not surprise if this is running
slow beyond all belief. The index you have on Status is likely to be
worthless, not being selective enough.

Helmut Wss suggested using three tables, and this may be worth considering.

However, you should start with getting a decent index structure. It
is from the table definition unclear to be whether there can be more
than one row for the same JobID. If there is, add a clustered index on
JobID and Status. If JobID is infact unique, get rid of that Identity
column. If your aim is to get high speed, then you should trim the
table, since the smaller the rows, the more rows you can fit on a page,
and the faster you can do I/O.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you for your reply

The script I submitted is a smaller sample of what I have. I forgot to
state that the JobID has a foreign key to another table, there will be
many JobIds. Am I correct in assuming that a foreign key is indexed?
I can't set the index to be clustered, because it will only allow one
clustered index, ID is the primary key and is clustered.

The final queue will contain more fields than stated, if this effects
the speed I guess I will have to use a separate queuing table.

My first thought was that if status is indexed and has a value between
0 and 2, the index would be sorted ascending and 0's would be at the
top, therefore would remain the same speed throughout the queue. I am
not sure exactly what indexing does behind the scenes, I should
probably look into this. What difference would it make creating a
composite index of JobID and Status?

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93AD579174Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in public]
> Chris Foster (chrisfoster@.btinternet.com) writes:
> > The queue table will contain tens of millions of records.
> > The problem I have is the more records completed, the the slower it
> > gets. I don't want to remove data from the queue because I use the
> > same table to store results. The queue handles concurrent requests.
> > The status field will contain the following values:
> > 0 = Waiting
> > 1 = Started
> > 2 = Finished
> >...
> > CREATE TABLE [dbo].[Queue] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [JobID] [int] NOT NULL ,
> > [Status] [tinyint] NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
> > GO
> > CREATE PROCEDURE dbo.NextItem
> > @.JobID integer,
> > @.ID integer output
> > AS
> > SELECT TOP 1 @.ID = [ID]
> > FROM Queue WITH (READPAST, XLOCK)
> > WHERE (Status = 0) AND (JobID = @.JobID)
> > RETURN
> > GO
> Since you have no index on JobID, I am not surprise if this is running
> slow beyond all belief. The index you have on Status is likely to be
> worthless, not being selective enough.
> Helmut Wss suggested using three tables, and this may be worth considering.
> However, you should start with getting a decent index structure. It
> is from the table definition unclear to be whether there can be more
> than one row for the same JobID. If there is, add a clustered index on
> JobID and Status. If JobID is infact unique, get rid of that Identity
> column. If your aim is to get high speed, then you should trim the
> table, since the smaller the rows, the more rows you can fit on a page,
> and the faster you can do I/O.|||Hi Chris

If you are after speed and with those sorts of volumes, I would not be
recommending that you used SQL Server to implement a TP queue in the
first place to store requests that are either waiting or started.
There are other options worth considering including IBM WebSphere
MQSeries.

chrisfoster@.btinternet.com (Chris Foster) wrote in message news:<b311a0b8.0307020655.9a5beeb@.posting.google.com>...
> I am trying to implement a very fast queue using SQL Server.
> The queue table will contain tens of millions of records.
> The problem I have is the more records completed, the the slower it
> gets. I don't want to remove data from the queue because I use the
> same table to store results. The queue handles concurrent requests.
> The status field will contain the following values:
> 0 = Waiting
> 1 = Started
> 2 = Finished
> Any help would be greatly appreciated.
> Here is a simplified script to demonstrate what has been done.
> CREATE TABLE [dbo].[Queue] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [JobID] [int] NOT NULL ,
> [Status] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
> GO
> CREATE PROCEDURE dbo.NextItem
> @.JobID integer,
> @.ID integer output
> AS
> SELECT TOP 1 @.ID = [ID]
> FROM Queue WITH (READPAST, XLOCK)
> WHERE (Status = 0) AND (JobID = @.JobID)
> RETURN
> GO|||Chris Foster (chrisfoster@.btinternet.com) writes:
> The script I submitted is a smaller sample of what I have. I forgot to
> state that the JobID has a foreign key to another table, there will be
> many JobIds. Am I correct in assuming that a foreign key is indexed?

No. There are no automatic indexes created on foriegn-key columns. You need
to add any index yourself.

> I can't set the index to be clustered, because it will only allow one
> clustered index, ID is the primary key and is clustered.

On second thought, a non-clustered index on (JobId, Status) is likely
to be ideal. You could add ID explicitly to this index, but it is in fact
already there, because for a non-clustered index, SQL Server uses the
clustered key as the address to the data page.

The point here is that you get a *covering index*, which means that
SQL Server can resolve the query from the index alone. This is good
for speed. Since the index nodes are smaller than the complete rows,
you get more rows per page, and fewer pages to read.

> My first thought was that if status is indexed and has a value between
> 0 and 2, the index would be sorted ascending and 0's would be at the
> top, therefore would remain the same speed throughout the queue. I am
> not sure exactly what indexing does behind the scenes, I should
> probably look into this.

Yes, you should. :-) In a non-clustered index on Status only, SQL
Server needs to go to the data pages to find JobId so it can compare
this condition. This can be a more expensive operation that scanning
the table from left to right, because some pages may have to be read
more than twice. SQL Server uses the statistics is has on Status
to determine whether using the index is a viable way to go. You can
force SQL Server to use the index, by means of an index hint. You may find
that this gives even worse performance.

> What difference would it make creating a composite index of JobID and
> Status?

Because now SQL Server has all the information to resolve the query in
the index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Indexing both JobID and Status has made the queue work at an acceptable speed.

Thanks you

Chris

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93AF515713E6Yazorman@.127.0.0.1>...
> Chris Foster (chrisfoster@.btinternet.com) writes:
> > The script I submitted is a smaller sample of what I have. I forgot to
> > state that the JobID has a foreign key to another table, there will be
> > many JobIds. Am I correct in assuming that a foreign key is indexed?
> No. There are no automatic indexes created on foriegn-key columns. You need
> to add any index yourself.
> > I can't set the index to be clustered, because it will only allow one
> > clustered index, ID is the primary key and is clustered.
> On second thought, a non-clustered index on (JobId, Status) is likely
> to be ideal. You could add ID explicitly to this index, but it is in fact
> already there, because for a non-clustered index, SQL Server uses the
> clustered key as the address to the data page.
> The point here is that you get a *covering index*, which means that
> SQL Server can resolve the query from the index alone. This is good
> for speed. Since the index nodes are smaller than the complete rows,
> you get more rows per page, and fewer pages to read.
> > My first thought was that if status is indexed and has a value between
> > 0 and 2, the index would be sorted ascending and 0's would be at the
> > top, therefore would remain the same speed throughout the queue. I am
> > not sure exactly what indexing does behind the scenes, I should
> > probably look into this.
> Yes, you should. :-) In a non-clustered index on Status only, SQL
> Server needs to go to the data pages to find JobId so it can compare
> this condition. This can be a more expensive operation that scanning
> the table from left to right, because some pages may have to be read
> more than twice. SQL Server uses the statistics is has on Status
> to determine whether using the index is a viable way to go. You can
> force SQL Server to use the index, by means of an index hint. You may find
> that this gives even worse performance.
> > What difference would it make creating a composite index of JobID and
> > Status?
> Because now SQL Server has all the information to resolve the query in
> the index.

Need a double join with a where clause

SELECT Person.PersonID as HospitalID,
Person.Name, ...
IPList.AttendingMD as Attending,
...
PTList.Status as Active
FROM (
Person INNER JOIN
IPList ON
Person.PersonID = IPList.PersonID)
LEFT JOIN (Select PTList.PersonID,PTList.Status from PTList
WHERE PtList.ProviderCode='John') ON PTList.PersonID=IPList.PersonID

The Select after the Left Join gives an error, but when I join PTList and IPList on PersonID, the where clause gets left out in the main set. This causes me to get only the records where 'John' is the provider code.

What I want is to get all the records of IPList inner Join with Person, and then I want the PTList.Active Status for Only those records in PTList where the ProviderCode='John'

How can I get the where clause to hit ONLY on the PTList?try this:SELECT Person.PersonID as HospitalID
, Person.Name
, ...
. IPList.AttendingMD as Attending
, ...
, PTList.Status as Active
FROM Person
INNER
JOIN IPList
ON Person.PersonID = IPList.PersonID
LEFT
JOIN PTList
ON IPList.PersonID = PTList.PersonID
AND PtList.ProviderCode='John'rudy
http://r937.com/|||Perfecto. Thank you very much. The key is the word AND.

Without it, the clause gets applied wrong.

Need a detail section which runs horizontally

I'm make a report which has a detail section that runs left to right (records 1 to 3) then to the next line down etc... so line 2 will have records 4 to 5.

Traditionally detail sections run top to bottom and this is very easy to design but I'm at a complete loss as to how to do it with the records running left to right as well. Perhaps you'd need to somehow manual increment the record pointer ??

Crystal reports v9

Thanks for the helpDoes CR9 have a 'Format with multiple columns' checkbox in the section expert?
If so, check it and then choose the 'Layout' tab that appears. You'll then need to experiment with the values in that tab - see help on 'Layout tab (Section Expert)'.

Need a connection string for sql server via vb6

Hey guys,
sorry to bother you with something so newbish, but I am trying to connect to a remote sql server db, using vb6.
I want to use ado to do so but have been having problem with some of the samples I have found online.

I will probably just test the connection out on an existing database like pubs or northwind.

Can anyone provide me a code snip please?

This forum has always been a great resource for me, thanks again guys!Driver={SQL Server};Server=localhost;Address=localhost,1433;Ne twork=DBMSSOCN;Database=northwind;Uid=sa;Pwd=;

Switch the appropriate data out

Rob|||You also might want to add www.connectionstrings.com to your favorites.

Need a book for reporting services.

http://www.amazon.ca/s/ref=nb_ss_gw/701-8175374-6201155?url=search-alias%3Daps&field-keywords=reporting+services
I need a book to do the following:
Create a format of a report.
Define parameters of a format of a report, name, description.
Choose options of period of report
Modify the format of a report.
Create a chart by name, description, grouping by.
Modify the chart.
Delete the chart.
Create a time period (Range)
Define the range of parameters of the name
description formula
Modify time period
Delete time period.
Associate a format of the report. PDF, Excel, HMTL
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.comThis Hitchhikers Guide to Reporting Services is your best bet IMHO.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<matt cupryk> wrote in message news:20061010104027macupryk@.videotron.ca...
> http://www.amazon.ca/s/ref=nb_ss_gw/701-8175374-6201155?url=search-alias%3Daps&field-keywords=reporting+services
> I need a book to do the following:
> Create a format of a report.
> Define parameters of a format of a report, name, description.
> Choose options of period of report
> Modify the format of a report.
> Create a chart by name, description, grouping by.
> Modify the chart.
> Delete the chart.
> Create a time period (Range)
> Define the range of parameters of the name
> description formula
> Modify time period
> Delete time period.
> Associate a format of the report. PDF, Excel, HMTL
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com|||Hi,
I own both hitch hikers and the Wrox Professional book...both are good
but if you only have money for one I'd go for the hitchhikers one...
Peter
Hilary Cotter wrote:
> This Hitchhikers Guide to Reporting Services is your best bet IMHO.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <matt cupryk> wrote in message news:20061010104027macupryk@.videotron.ca...
> > http://www.amazon.ca/s/ref=nb_ss_gw/701-8175374-6201155?url=search-alias%3Daps&field-keywords=reporting+services
> >
> > I need a book to do the following:
> >
> > Create a format of a report.
> >
> > Define parameters of a format of a report, name, description.
> >
> > Choose options of period of report
> >
> > Modify the format of a report.
> >
> > Create a chart by name, description, grouping by.
> >
> > Modify the chart.
> >
> > Delete the chart.
> >
> > Create a time period (Range)
> >
> > Define the range of parameters of the name
> > description formula
> >
> > Modify time period
> >
> > Delete time period.
> >
> > Associate a format of the report. PDF, Excel, HMTL
> >
> > EggHeadCafe.com - .NET Developer Portal of Choice
> > http://www.eggheadcafe.com

Need a "Replace" sp with rugular expressions

Hi all,
I need a more powerfull function (or stored proc) to make string replacement
(like the REPLACE SQL function) but with regular expressions (like the
wildcard characters used with the LIKE).
Who has this function ?
Thanks.
Lilian.Hi Lilian,
I have a tool that does that. You can dowload your free copy at:
http://www.nobhillsoft.com/EnterCus...prod_name=diana
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u62RFrXSEHA.240@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I need a more powerfull function (or stored proc) to make string
replacement
> (like the REPLACE SQL function) but with regular expressions (like the
> wildcard characters used with the LIKE).
> Who has this function ?
> Thanks.
> Lilian.
>|||Thanks, but finally I've found by myself (thanks google): a good and FREE
solution: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27205
"Yoni Sion" <yoni@.nobhillsoft.com> wrote in message
news:O1D7E0XSEHA.972@.tk2msftngp13.phx.gbl...
> Hi Lilian,
> I have a tool that does that. You can dowload your free copy at:
>
http://www.nobhillsoft.com/EnterCus...prod_name=diana
>
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:u62RFrXSEHA.240@.TK2MSFTNGP11.phx.gbl...
> replacement
>

Need a "Replace" sp with rugular expressions

Hi all,
I need a more powerfull function (or stored proc) to make string replacement
(like the REPLACE SQL function) but with regular expressions (like the
wildcard characters used with the LIKE).
Who has this function ?
Thanks.
Lilian.Hi Lilian,
I have a tool that does that. You can dowload your free copy at:
http://www.nobhillsoft.com/EnterCust_download.php?prod_id=1&prod_name=diana
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u62RFrXSEHA.240@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I need a more powerfull function (or stored proc) to make string
replacement
> (like the REPLACE SQL function) but with regular expressions (like the
> wildcard characters used with the LIKE).
> Who has this function ?
> Thanks.
> Lilian.
>|||Thanks, but finally I've found by myself (thanks google): a good and FREE
solution: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27205
"Yoni Sion" <yoni@.nobhillsoft.com> wrote in message
news:O1D7E0XSEHA.972@.tk2msftngp13.phx.gbl...
> Hi Lilian,
> I have a tool that does that. You can dowload your free copy at:
>
http://www.nobhillsoft.com/EnterCust_download.php?prod_id=1&prod_name=diana
>
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:u62RFrXSEHA.240@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> >
> > I need a more powerfull function (or stored proc) to make string
> replacement
> > (like the REPLACE SQL function) but with regular expressions (like the
> > wildcard characters used with the LIKE).
> >
> > Who has this function ?
> >
> > Thanks.
> >
> > Lilian.
> >
> >
>

Need a "Replace" sp with rugular expressions

Hi all,
I need a more powerfull function (or stored proc) to make string replacement
(like the REPLACE SQL function) but with regular expressions (like the
wildcard characters used with the LIKE).
Who has this function ?
Thanks.
Lilian.
Hi Lilian,
I have a tool that does that. You can dowload your free copy at:
http://www.nobhillsoft.com/EnterCust...rod_name=diana
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u62RFrXSEHA.240@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I need a more powerfull function (or stored proc) to make string
replacement
> (like the REPLACE SQL function) but with regular expressions (like the
> wildcard characters used with the LIKE).
> Who has this function ?
> Thanks.
> Lilian.
>
|||Thanks, but finally I've found by myself (thanks google): a good and FREE
solution: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27205
"Yoni Sion" <yoni@.nobhillsoft.com> wrote in message
news:O1D7E0XSEHA.972@.tk2msftngp13.phx.gbl...
> Hi Lilian,
> I have a tool that does that. You can dowload your free copy at:
>
http://www.nobhillsoft.com/EnterCust...rod_name=diana
>
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:u62RFrXSEHA.240@.TK2MSFTNGP11.phx.gbl...
> replacement
>

Need 2nd set of eyes on a CASE statement

I am going batty looking at this! I keep getting ORA-00907:Missing Right parenthesis when I have the 2nd case statement in this SQL (in bold). If I remove that line, it works just fine. As I don't see any left ( w/o a right ), I have no idea what the real issue is. (this is against a Oracle DB)

select rownum as IndexNum,
lm.hostlocid "Loc. Code",
pm.hostpartid "Comcode",
pm.partname "Description",
pc.hostplannercodeid "Plnr Code",
pc.codedescription "PC Descr",
sa.ohneworig "On Hand New Orig",
sa.ohfixedorig "On Hand Fixed Orig",
sa.ohbadorig "On Hand Bad Orig",
sa.onorderorig "On Order Orig",
sa.backorderorig "Backorder Orig",
sa.inrepairorig "In Repair Orig",
sa.inreturnorig "In Return Orig",
sa.allocatedorig "Allocated Orig",
sa.amountcustom1 "Amt Cus 1",
sa.amountcustom2 "Amt Cus 2",
sa.amountcustom3 "Amt Cus 3",
sa.amountcustom4 "Amt Cus 4",
sa.amountcustom5 "Amt Cus 5",
upper(ip.isaslitem) "Is ASL Item",
upper(ip.aslstatus) "ASL Status",
ip.stockmax "Stock Max",
ip.rop "Reorder Point",
pm.price "Std Cost",
fd.ForecastAmount,
to_char(fd.ForecastSliceDate,'MM-YYYY') "Current Period",
case when (sa.ohfixedorig + sa.ohneworig)=0 or fd.ForecastAmount=0 then null else round(((sa.ohfixedorig + sa.ohneworig)/(fd.ForecastAmount/30))) end Out_Of_Stock_Days,
pcd.ChainParentID,
pcd.RelationType,
case when pcd.relationtype = 1 then (N'Alt') else (N'Rep') end Chain_Type
from
stock_amount sa,
part_master pm,
loc_master lm,
loc_type lt,
stock_level ip,
planner_codes pc,
Forecasted_data fd,
part_chain_details pcd
where
sa.partid=pm.partid and
sa.locid=lm.locid and
lm.loctypeid=lt.loctypeid and
ip.partid=sa.partid and
pm.plannercodeid=pc.PLANNERCODEID and
ip.locid=sa.locid and
fd.partid = sa.partid and
fd.locid = sa.locid and
lm.hostlocid = 'B014' and
pcd.partid (+) = pm.partid and
(pc.hostplannercodeid = 'C50' or pc.hostplannercodeid = 'C51' or pc.hostplannercodeid = 'C52' or pc.hostplannercodeid = 'ZZ5') and
fd.ForecastSliceDate >ADD_MONTHS(SYSDATE,-1) and
fd.ForecastSliceDate<ADD_MONTHS(SYSDATE,0)

Any help showing the evil of my ways would be greatly appreciated!case when pcd.relationtype = 1 then (N'Alt') else (N'Rep') end Chain_Type

Your problem, is that both (N'Alt') and ('N'Rep') are invalid. The syntax to append text to a column is, (column||' text');|||try it without the Ns

Edit: there's no concatenation; ChainType is a column alias|||Thanks R123456 and R937.

For some reason I had in my head that when using a case statement to output a text value I had to preced the 'text' with (N'my text').

No I was not trying to append to a column! Big OOPS!

I decided to try a DECODE statement that works. (I also was not taking into account nulls!)

With decode( pcd.relationtype,1,'Alt',0,'Rep','') as Chain_Type, the output is what I expect.

Hopefully I can be back to the dynamic SQL issue I posted earler this week!

Thanks for the slap in the face to wake me up! :-)

Need 2005 Wizard to create INSERT and UPDATE stored procedures.

In SQL Server 2000, the database wizard would create insert and update stored procedures for all of my tables.

I have a large SQL Server 2005 database with many tables and columns. I need wizard support to create insert and update stored procedures for them. Typing them all in manually is out of the question.

The 2005 Generate SQL Server Scripts Wizard does not create stored procuedure scripts. It only scripts tables.

Is there some other way to make Management Studio create stored procedure scripts the way Enterprise Manager did?

Are there any plans to restore this critical functionality that was left out of SQL Server 2005 and the service pack?

Do you know of any utilities that can fill this gap?

Any help would be appreciated.

Please file a suggestion for this functionality on http://connect.microsoft.com/sqlserver. We use customer feedback to prioritize future work. Suggestions and defect reports filed on the Connect site are placed directly into our internal issue tracking system so the appropriate team will see your request.

I don't think there is an easy way to do what you want using built-in functionality in SSMS. If you are comfortable writing .NET code, you could write a small Visual Basic or C# application using the SQL Management Objects to iterate through all your tables and generate the stored procedure code you need.

Thanks,
Steve

Need "commercial grade" FileWatcher task

Does anyone know of such a task than runs under both x86 and x64? (I know a FileWatcher add-in but it was built using Beta verisons of VS.NET)

TIA,

barkingdog

I would suggest using the WMI Event Watcher task - see http://msdn2.microsoft.com/en-us/library/ms141130(SQL.90).aspx

Donald

need -> max(sum(salary)) query

hi

i have a table employee:

dept ename salary
-- --- ---
10 A 2500
20 B 3500
30 C 4000
20 D 5500
10 E 4500
30 F 5200

FIRST QUERY:
select dept,sum(salary) from employee group by dept

the above one is working fine..

after working the first query output,
i want to select the dept,max(sum(salary)) from the table...

how?? could any one send me immediately...

thanks in advancethe below one query will give only the max(sum(salay))

-----
select max(sal) from (select sum(salary) as sal from employee
group by dept) as a
-------

but i need - Which DEPT has the max(sum(salary)) ??

need these two values:

dept, max(sum(salary))

thanks|||select top 1
dept
, sum(salary)
from employee
group
by dept
order
by sum(salary) desc|||hi
it works .

thanks rudy....

Need %complete stat from stored procedure to vb

Is there a way to call a SQLServer stored procedure from within VB6
and have the % completion stat be returned to the calling program as
it is updated? Our clients hate seeing static screens and while this
procedure is processing I want to be able to display it's progress.
Thanks!Not from a single statement, there's not any way to get back the completion
status from a query.
Some operations, which are predictable, like BACKUP, has an option to get ST
ATS back, but not for
general DML statements. If your procedure is executing *several* statements,
you can use RAISERROR
using the NO_WAIT option to make SQL server flush the output buffer for each
RAISERROR statement.
For a single query, fake it... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark V" <mgvincent@.excite.com> wrote in message
news:96887154.0411170859.77244be0@.posting.google.com...
> Is there a way to call a SQLServer stored procedure from within VB6
> and have the % completion stat be returned to the calling program as
> it is updated? Our clients hate seeing static screens and while this
> procedure is processing I want to be able to display it's progress.
> Thanks!

Need %complete stat from stored procedure to vb

Is there a way to call a SQLServer stored procedure from within VB6
and have the % completion stat be returned to the calling program as
it is updated? Our clients hate seeing static screens and while this
procedure is processing I want to be able to display it's progress.
Thanks!Not from a single statement, there's not any way to get back the completion status from a query.
Some operations, which are predictable, like BACKUP, has an option to get STATS back, but not for
general DML statements. If your procedure is executing *several* statements, you can use RAISERROR
using the NO_WAIT option to make SQL server flush the output buffer for each RAISERROR statement.
For a single query, fake it... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark V" <mgvincent@.excite.com> wrote in message
news:96887154.0411170859.77244be0@.posting.google.com...
> Is there a way to call a SQLServer stored procedure from within VB6
> and have the % completion stat be returned to the calling program as
> it is updated? Our clients hate seeing static screens and while this
> procedure is processing I want to be able to display it's progress.
> Thanks!

Need %complete stat from stored procedure to vb

Is there a way to call a SQLServer stored procedure from within VB6
and have the % completion stat be returned to the calling program as
it is updated? Our clients hate seeing static screens and while this
procedure is processing I want to be able to display it's progress.
Thanks!
Not from a single statement, there's not any way to get back the completion status from a query.
Some operations, which are predictable, like BACKUP, has an option to get STATS back, but not for
general DML statements. If your procedure is executing *several* statements, you can use RAISERROR
using the NO_WAIT option to make SQL server flush the output buffer for each RAISERROR statement.
For a single query, fake it... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark V" <mgvincent@.excite.com> wrote in message
news:96887154.0411170859.77244be0@.posting.google.c om...
> Is there a way to call a SQLServer stored procedure from within VB6
> and have the % completion stat be returned to the calling program as
> it is updated? Our clients hate seeing static screens and while this
> procedure is processing I want to be able to display it's progress.
> Thanks!

Need "refresh connection" or something ?

HI,

I have a problem with the data that I want to delete and insert new one on my SQL.
It will work if I just delete a row. And work well if I just insert a row.
But it will not work if I delete and insert at once on one procedure.

Here's the code :

Protected Sub RolesRadioButtonList_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles RolesRadioButtonList.SelectedIndexChanged
Session("CurrentRoleId") = Me.RolesRadioButtonList.SelectedValue
' Call Sub RemoveUsersInRoles
RemoveUsersInRoles()
' Call Sub AddNewUsersInRoles
AddNewUsersInRoles()
End Sub

Sub RemoveUsersInRoles()
' Create SQL database connection
Dim sqlConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.Text
' Delete that row with correct UserId
cmd.CommandText = "DELETE aspnet_UsersInRoles WHERE (UserId = '" & Session("CurrentUserId") & "')"
cmd.Connection = sqlConn
sqlConn.Open()
cmd.ExecuteNonQuery()
' Close SQL connecton
sqlConn.Close()
End Sub

Sub AddNewUsersInRoles()
' Create SQL database connection
Dim sqlConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")
sqlConn.Open()
' Create new row with new UserId
Dim sqlString As String = "INSERT INTO aspnet_UsersInRoles (UserId, RoleId) VALUES( '" & Session("CurrentUserId") & "','" & Session("CurrentRoleId") & "')"
Dim sqlComm As New SqlCommand(sqlString, sqlConn)
Dim sqlExec As Integer = sqlComm.ExecuteNonQuery
' Close SQL connection
sqlConn.Close()
End Sub

--------------

I can delete the row if I call 'RemoveUsersInRoles'
And I can insert new one if I call 'AddNewUsersInRoles'

But if 'RolesRadioButtonList_SelectedIndexChanged' is called, it will not work well.
If the row not exist, it will insert new row. And that what I want.
But If the row is exist, It wont delete that row and wont insert the new one. That's the problem.

Do I need some 'pause' or 'refresh connection' here ?

Thank You.

Why don't you create one procedure that checks if the row exists, then delete and insert if it does (or just amends/updates it), otherwise it just performs an insert?

|||

uh, Mike, thanks for your respon.

My mistake. I put this at my page load. It always reset the variable when the page is reload.

If Len(Session("CurrentRoleId")) Then
Me.RolesRadioButtonList.SelectedValue = Session("CurrentRoleId")
End If

And Mike, thanks for the advise : use update. I already use it.

And thank you all at that 2nd class, keep programming ...

Need - help to create DLOOKUP with SSIS

Hi,

Does anybody knows how to create a DLOOKUP (dynamic lookup) in SSIS withour writing any kind of script?
I need to test records existance in destination from the source before inserting or updating in the destination (if the record exist in destination then update, else insert).

Any help apreciated.
I find the Lookup component works best for this. Jamie wrote an article that compares lookup as well as other methods.
http://www.sqlis.com/default.aspx?311
Adrian

Neebie Q: .MDFs and SQL Server

I'm just starting out with VWD 2005...wow this product and language has come along way, but I have a couple of simple questions of confusion. It seems that Microsoft is really pushing SQL via SQL Express over Access...and depending on the answers to these questions below...it seems like it has been accomplished.

Q

1. When I create a .mdf via VWD 2005, why doesn't this database show up in SQL Server Management Express?

2. Are you able now to simple publish the .mdf and .ldf (/appdata) folder up to any webserver and be able to access that SQL database like you normally would an Access database via SQL express on the server? And, if this is the case, doesn't this imply that an ISP will be providing SQL support via the Express edition without even explicitly allowing SQL through their actual SQL database server?

3. Besides VWD2005, how can you manage the .mdf's within the web site?

I think you almost answered yourself because the MDF (Microsoft data file) and LDF ( log data file ) are not stored in the database they are stored in the data subfolder in Microsoft SQL Server folder in programs by the SQL Server engine not you the user. So you see you cannot manage the MDF in a web server, because SQL Server from version 7.0 uses ODS(on disk) file system. Now to the ISP question you need the full version of SQL Server the developer edition cost a few dollars, it is Enterprise edition with no deployment restrictions and ask the ISP to give you management studio access and you just back up and restore your database on their server. Hope this helps.|||

Thanks Caddre:

Yeh, I'm not totally understanding this. The VWD gives you a great interface to work on modifying an SQL database on the fly while writing code. How can you get an existing SQL database already in the SQL server proper into VWD so that you can add fields and manage tables on the fly from this interface as opposed to having another Enterprise Manager or SQL client window up to do that separately. Its confusing to me.

I don't understand how SQL server knows about this database yet doesn't know about it via the Management Studio and visa versa, why can't VWD see existing databases that Management Studio can see. I see Add Exsting Item in the VWD App_Data but it doesn't pull up the SQL Management Studio or anything to let me choose an existing database.

If a Connection String can find this database via just two files within a web folder locally, why wouldn't it be able to find it on a web server in the same fashion?

|||

You can do what you want in server explorer but I don't know if you have server explorer in VDW. The quick answer is people should not be allowed to move RDBMS (relational database management systems) file as local operating system file because you risk corrupting the database. Management studio is SQL Server and VWD is an IDE (integrated development environment). So it is a choice, use inproc session which is file system or do some extra work to use SQL Server as out of proc session. The difference is algebraically processed data in SQL Server or data that is just stored in the file system of the operation system.

If a Connection String can find this database via just two files within a web folder locally, why wouldn't it be able to find it on a web server in the same fashion?

That will take Microsoft's SQL Server the new kid on the block out of the RDBMS business for serious application.

Hope this helps.

|||

double post

|||

Let's see if I can answer a few of these:

1. Because the database isn't attached to the same instance of SQL Server Management Express at the time it's running.

2. Sort of, you can if it has SQL Express installed. No.

3. Depends on what you mean by "within the web site". You can attach the .mdf/.ldf files to a non-user instance of SQL Express (The one the management studio normally sees), then you can use management studio to do whatever you want. Just don't forget to unattach it so that when you are debugging or want to use it within VWD that it'll be able to instantiate a user instance of SQL Express, and attach the files correctly.

|||

How can I get an existing SQL database into the VWD environment?

Neebie needing help asap restoring sql

I am having problem which is getting worst by the moment. I had a power failure which my battery backup fail while running a large report in my SSRS all of a sudden I started getting reportservertempdb.dbo.persistedstream error. I could not get my reports to run through the iis webservice. I could get them to run from within my reportbuilder. I was told to reload my sql and restore it but I can not get my sql to successfully reinstall I am using the sql2005 dev ed. It either gives me a name instance issue or fails the database, report server and notifaction portion of the reload. I am not sure why it will not reload. any assistance would be great.

You dont have to reinstall SQL Server just for a power failure? Try restarting all the services - SQL server as well as IIS.

neebie Needed help with sql reports query

I am getting an error that says there is ann error near the union all This is the tsql query that I am tring to use in a sql report in my vs 2005 business int. can someone look at this query and tell me what is wrong with it.

SELECT
'Quarter 1' as 'qtr',
COUNT(JOB.JOBID) AS 'transcount',
COUNT(DISTINCT JOB.PATIENTID) AS 'patient count',
SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
INVOICE_AR.INVOICE_DATE AS Expr1,
INVOICE_AR.AMOUNT_DUE

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS
WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN @.startdate AND @.enddate)
AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3))

GROUP BY
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE
ORDER BY 'tcost' DESC

union all

SELECT
'Quarter 2' as 'qtr',
COUNT(JOB.JOBID) AS 'transcount',
COUNT(DISTINCT JOB.PATIENTID) AS 'patient count',
SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
INVOICE_AR.INVOICE_DATE AS Expr1,
INVOICE_AR.AMOUNT_DUE

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS
WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN @.startdate AND @.enddate)
AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (4,5,6))

GROUP BY
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE
ORDER BY 'tcost' DESC

union all

SELECT
'Quarter 3' as 'qtr',
COUNT(JOB.JOBID) AS 'transcount',
COUNT(DISTINCT JOB.PATIENTID) AS 'patient count',
SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
INVOICE_AR.INVOICE_DATE AS Expr1,
INVOICE_AR.AMOUNT_DUE

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS
WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN @.startdate AND @.enddate)
AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (7,8,9))

GROUP BY
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE
ORDER BY 'tcost' DESC

union all

SELECT
'Quarter 4' as 'qtr',
COUNT(JOB.JOBID) AS 'transcount',
COUNT(DISTINCT JOB.PATIENTID) AS 'patient count',
SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
INVOICE_AR.INVOICE_DATE AS Expr1,
INVOICE_AR.AMOUNT_DUE

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS
WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN @.startdate AND @.enddate)
AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (10,11,12))

GROUP BY
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE
ORDER BY 'tcost' DESC

The ORDER BY clause should appear only once, at the very end. And I would not enclose column names in single quotes. And, you will likely need to ORDER BY 4 (that is, refer to the sorting column by position, not by the alias you are giving it).

nee stored procedure please help

Dear Experts,
I'm vinod, a junior DBA. working in HYD, india. i need one stored procedure.

Using one procedure i want to know the columns that are associated with the primary key and the name of the primary key.

The output should be as follows

Table Name PK_Constraint Name Columns

TABLE017 PK_TABLE009 COLUMN001

TABLE017 FK_TABLE009 COLUMN002

please send me the stored procedure, it will be a great help for me. thanks in advance.
vinod.mallolu@.exensys.com
vinodselect kcu.Table_Name, kcu.Constraint_Name, kcu.Column_Name from information_schema.table_constraints tc join information_schema.key_column_usage kcu
on tc.Constraint_Name = kcu.Constraint_Name and tc.Constraint_Type = 'PRIMARY KEY'

Mahesh|||i'M TRYING WITH THAT FRIEND. THANK YOU VERY MUCH FOR YOU REPLY

Nee help - How To get AVG in his situation?

Hi All,
Below is my initial query, that returns totals for each w within the
month specified. I hard coded the W Columns to make it easier to work
with. This works fine.
/*---*/
Select T1.DataSource AS [Service Line],
COUNT(T1.PoNumber) AS [Total Of PONumber],
Sum(Case When T1.ReqSubmitDate
Between '04/01/2005' AND '04/2/2005' Then 1 End) [04/2/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/03/2005' AND '04/9/2005' Then 1 End) [04/9/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/10/2005' AND '04/16/2005' Then 1 End) [04/16/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/17/2005' AND '04/23/2005' Then 1 End) [04/23/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/24/2005' AND '04/30/2005' Then 1 End) [04/30/2005]
FROM OPW AS T1,
(SELECT PoNumber
FROM OPW
GROUP BY PoNumber
HAVING COUNT(*)>1) AS T2
WHERE (T1.PoNumber = T2.PoNumber) AND (T1.ReqSubmitDate BETWEEN '04/01/2005'
AND '04/30/2005')
GROUP BY T1.DataSource
/*---*/
The current resulting data is:
[Service Line] [Total Of PONumber] [04/2/2005] [04/9/2005] [04/16/2005]
[04/23/2005] [04/30/2005]
EVPN 4 NULL NULL 4 NULL NULL
MNS 526 NULL 209 313 NULL 4
/*---*/
Now, one of my tasks assign to me is to find the average cycle time for each
w per [Service Line]. This will include the following fields to the
query:
REQCreateDate DateTime,
REQCreateTime VarChar(10),
ReqSubmitTime VarChar(10),
ReqSubmitDate DateTime (This one is already used within the above query)
So, I'm thinking I need to concatinate the following columns, then get the
total number per w and derive the average:
T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME) AS RC,
T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME) AS RS
I hope I am making sense and you can help m understand this.
Thanks for taking the time though.
John.John,
If you can provide some sample data and also show
the exact results you want, it will be easier to help. You
may know exactly what "average cycle time" means, but
I don't. Sample data will also help to show why and how
you are storing values like REQCreateTime as VarChar(10),
since the time columns appear to be important.
See http://www.aspfaq.com/etiquett_e.asp?id=5006
Steve Kass
Drew University
John Rugo wrote:

>Hi All,
>Below is my initial query, that returns totals for each w within the
>month specified. I hard coded the W Columns to make it easier to work
>with. This works fine.
>/*---*/
>Select T1.DataSource AS [Service Line],
> COUNT(T1.PoNumber) AS [Total Of PONumber],
> Sum(Case When T1.ReqSubmitDate
> Between '04/01/2005' AND '04/2/2005' Then 1 End) [04/2/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/03/2005' AND '04/9/2005' Then 1 End) [04/9/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/10/2005' AND '04/16/2005' Then 1 End) [04/16/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/17/2005' AND '04/23/2005' Then 1 End) [04/23/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/24/2005' AND '04/30/2005' Then 1 End) [04/30/2005]
>FROM OPW AS T1,
>(SELECT PoNumber
> FROM OPW
> GROUP BY PoNumber
> HAVING COUNT(*)>1) AS T2
>WHERE (T1.PoNumber = T2.PoNumber) AND (T1.ReqSubmitDate BETWEEN '04/01/2005
'
>AND '04/30/2005')
>GROUP BY T1.DataSource
>/*---*/
>The current resulting data is:
>[Service Line] [Total Of PONumber] [04/2/2005] [04/9/2005] [04/16/2005]
>[04/23/2005] [04/30/2005]
>EVPN 4 NULL NULL 4 NULL NULL
>MNS 526 NULL 209 313 NULL 4
>/*---*/
>Now, one of my tasks assign to me is to find the average cycle time for eac
h
>w per [Service Line]. This will include the following fields to the
>query:
>REQCreateDate DateTime,
>REQCreateTime VarChar(10),
>ReqSubmitTime VarChar(10),
>ReqSubmitDate DateTime (This one is already used within the above query)
>So, I'm thinking I need to concatinate the following columns, then get the
>total number per w and derive the average:
>T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME) AS RC,
>T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME) AS RS
>I hope I am making sense and you can help m understand this.
>Thanks for taking the time though.
>John.
>
>|||The times are stored in a varchar format because they are derived solely
from an Excel Spreadsheet import that has the time in a spererate column,
and I was asked to mimic the data structure of the excel file. I don't like
it ether :(.
The Cycle Time is the Number of Minutes between ReqCreated Date/Time and
ReqSubmit Date/Time.
At the bottom of the my current message I have my newest version of the
query that at least shows the total Cycle Times per w. But I need to
show the averages, not the total.
Thanks very much for helping me.
/*Data*/
/*--*/
Select DataSource, PoNumber, REQCreateDate, REQCreateTime, ReqSubmitDate,
ReqSubmitTime
FROM OPW
DataSource | PoNumber | REQCreateDate | REQCreateTime | ReqSubmitDate |
ReqSubmitTime
EVPN PO82959 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82956 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82957 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82958 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82957 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82959 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
MNS PO82755 2005-03-30 00:00:00.000 4:42:27 2005-03-31 00:00:00.000 1:33:18
MNS PO82840 2005-04-13 00:00:00.000 3:31:16 2005-04-14 00:00:00.000 10:27:57
MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82968 2005-04-15 00:00:00.000 2:35:37 2005-04-15 00:00:00.000 2:42:01
MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
/*Newest Query*/
/*--*/
Select T1.DataSource AS [Service Line],
COUNT(T1.PoNumber) AS [Total Of PONumber],
Sum(Case When T1.ReqSubmitDate
Between '04/01/2005' AND '04/2/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/2/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/03/2005' AND '04/9/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/9/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/10/2005' AND '04/16/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/16/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/17/2005' AND '04/23/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/23/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/24/2005' AND '04/30/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/30/2005]
FROM OPW AS T1,
(SELECT PoNumber
FROM OPW
GROUP BY PoNumber
HAVING COUNT(*)>1) AS T2
WHERE (T1.PoNumber = T2.PoNumber) AND (T1.ReqSubmitDate BETWEEN '04/01/2005'
AND '04/30/2005')
GROUP BY T1.DataSource
/*--*/
"Steve Kass" <skass@.drew.edu> wrote in message
news:u8Ejbk$SFHA.3620@.TK2MSFTNGP09.phx.gbl...
> John,
> If you can provide some sample data and also show
> the exact results you want, it will be easier to help. You
> may know exactly what "average cycle time" means, but
> I don't. Sample data will also help to show why and how
> you are storing values like REQCreateTime as VarChar(10),
> since the time columns appear to be important.
> See http://www.aspfaq.com/etiquett_e.asp?id=5006
> Steve Kass
> Drew University
> John Rugo wrote:
>|||John,
Did you try using AVG() instead of SUM() ? To be safe from rounding
surprises, write any AVG() expression as AVG(1.0*(yourvalue)) if yourvalue
is an integer.
SK
John Rugo wrote:

>The times are stored in a varchar format because they are derived solely
>from an Excel Spreadsheet import that has the time in a spererate column,
>and I was asked to mimic the data structure of the excel file. I don't lik
e
>it ether :(.
>The Cycle Time is the Number of Minutes between ReqCreated Date/Time and
>ReqSubmit Date/Time.
>At the bottom of the my current message I have my newest version of the
>query that at least shows the total Cycle Times per w. But I need to
>show the averages, not the total.
>Thanks very much for helping me.
>/*Data*/
>/*--*/
>Select DataSource, PoNumber, REQCreateDate, REQCreateTime, ReqSubmitDate,
>ReqSubmitTime
>FROM OPW
>DataSource | PoNumber | REQCreateDate | REQCreateTime | ReqSubmitDate |
>ReqSubmitTime
>EVPN PO82959 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82956 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82957 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82958 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82957 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82959 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>MNS PO82755 2005-03-30 00:00:00.000 4:42:27 2005-03-31 00:00:00.000 1:33:18
>MNS PO82840 2005-04-13 00:00:00.000 3:31:16 2005-04-14 00:00:00.000 10:27:5
7
>MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82968 2005-04-15 00:00:00.000 2:35:37 2005-04-15 00:00:00.000 2:42:01
>MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>/*Newest Query*/
>/*--*/
>Select T1.DataSource AS [Service Line],
> COUNT(T1.PoNumber) AS [Total Of PONumber],
> Sum(Case When T1.ReqSubmitDate
> Between '04/01/2005' AND '04/2/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/2/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/03/2005' AND '04/9/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/9/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/10/2005' AND '04/16/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/16/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/17/2005' AND '04/23/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/23/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/24/2005' AND '04/30/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/30/2005]
>FROM OPW AS T1,
>(SELECT PoNumber
> FROM OPW
> GROUP BY PoNumber
> HAVING COUNT(*)>1) AS T2
>WHERE (T1.PoNumber = T2.PoNumber) AND (T1.ReqSubmitDate BETWEEN '04/01/2005
'
>AND '04/30/2005')
> GROUP BY T1.DataSource
>/*--*/
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:u8Ejbk$SFHA.3620@.TK2MSFTNGP09.phx.gbl...
>
>
>

nedd some help with datetime

Hi,
I am passing datetime from my vb.net app for storage in sql server like this
Dim DT As DateTime = DateTime.Now
Dim str As String
str = DT.ToString("MMddyyyyHHmmss")
...insert to database str (example datetime 10312005135802)
The table column datatype is char(15). How can I convert this to datetime in
sql server? I have an sp that need to look at both the date and time. I was
trying this
declare @.dateandtime datetime
select @.dateandtime = convert(datetime,datetimecolumn,120) from tablea where
id = 1234
select @.dateandtime
I am getting the error "syntax error converting datetime from character
string".
Or should I change the format I am sending the datetime from my vb.net app?
Thanks"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:01A96FD0-238C-42A8-AEF5-3BE330EC9EDC@.microsoft.com...
> Hi,
> I am passing datetime from my vb.net app for storage in sql server like
> this
> Dim DT As DateTime = DateTime.Now
> Dim str As String
> str = DT.ToString("MMddyyyyHHmmss")
> ...insert to database str (example datetime 10312005135802)
> The table column datatype is char(15). How can I convert this to datetime
> in
> sql server? I have an sp that need to look at both the date and time. I
> was
> trying this
> declare @.dateandtime datetime
> select @.dateandtime = convert(datetime,datetimecolumn,120) from tablea
> where
> id = 1234
> select @.dateandtime
> I am getting the error "syntax error converting datetime from character
> string".
> Or should I change the format I am sending the datetime from my vb.net
> app?
> Thanks
Any reason why you appear to be using dynamic SQL to do this? Pass the date
as an adDBTimeStamp type using the ADO parameters collection. That way the
conversion to SQL DATETIME is implicit. Also, you should typically call a
proc for a simple INSERT rather than constuct a dynamic SQL string in code.
See the Using Parameters topic in the ADO section of Books Online.
David Portas
SQL Server MVP
--|||> The table column datatype is char(15).
WHY? Do you put ice cream in your medicine cabinet too?
Pass it as a proper date time value, store it as a proper date time value,
and you will be amazed how many of these silly string conversion problems
magically disappear.|||Honestly, I was thinking the same thing, however, I have noticed in a few
articles a while ago where data is stored as strings and taught I was wrong
all along to use datetime type in my database when I need to pass and store
date and time form my front end app.
Thanks for rebooting that section.
"Aaron Bertrand [SQL Server MVP]" wrote:

> WHY? Do you put ice cream in your medicine cabinet too?
> Pass it as a proper date time value, store it as a proper date time value,
> and you will be amazed how many of these silly string conversion problems
> magically disappear.
>
>

Necessity.....

Installing multiple instances of SQL Server is really good.
Some one can tell me when exactly those situations arises.
SQL 2K.
Thanks,
Smith
I have 6 production environments. I have two test boxes, each with 3
instances of SQL. Same with staging. Just too costly for each test/ stage
environment to have its own box.
"Branden Smith" <BradSmith@.hotmail.com> wrote in message
news:OLToO%23LiFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Installing multiple instances of SQL Server is really good.
> Some one can tell me when exactly those situations arises.
> SQL 2K.
> Thanks,
> Smith
>
|||What is the necessity of having three instances of SQL Server on one box?
You create all the USER databases in one instance only. What we are gaining
by doing that approach? What is that we are missing by not going to multiple
instances?
Thanks,
Smith
"ChrisR" <noemail@.bla.com> wrote in message
news:eqxS2AMiFHA.1444@.TK2MSFTNGP10.phx.gbl...
> I have 6 production environments. I have two test boxes, each with 3
> instances of SQL. Same with staging. Just too costly for each test/ stage
> environment to have its own box.
>
> "Branden Smith" <BradSmith@.hotmail.com> wrote in message
> news:OLToO%23LiFHA.3656@.TK2MSFTNGP09.phx.gbl...
>
|||For example, staging is supposed to be a mirror(or as close as possible) of
production. Not even close if only on 1 instance.
"Branden Smith" <BradSmith@.hotmail.com> wrote in message
news:%23xthHdMiFHA.2644@.TK2MSFTNGP09.phx.gbl...
> What is the necessity of having three instances of SQL Server on one box?
> You create all the USER databases in one instance only. What we are
> gaining
> by doing that approach? What is that we are missing by not going to
> multiple
> instances?
> Thanks,
> Smith
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eqxS2AMiFHA.1444@.TK2MSFTNGP10.phx.gbl...
>

Necessity.....

Installing multiple instances of SQL Server is really good.
Some one can tell me when exactly those situations arises.
SQL 2K.
Thanks,
SmithI have 6 production environments. I have two test boxes, each with 3
instances of SQL. Same with staging. Just too costly for each test/ stage
environment to have its own box.
"Branden Smith" <BradSmith@.hotmail.com> wrote in message
news:OLToO%23LiFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Installing multiple instances of SQL Server is really good.
> Some one can tell me when exactly those situations arises.
> SQL 2K.
> Thanks,
> Smith
>|||What is the necessity of having three instances of SQL Server on one box?
You create all the USER databases in one instance only. What we are gaining
by doing that approach? What is that we are missing by not going to multiple
instances?
Thanks,
Smith
"ChrisR" <noemail@.bla.com> wrote in message
news:eqxS2AMiFHA.1444@.TK2MSFTNGP10.phx.gbl...
> I have 6 production environments. I have two test boxes, each with 3
> instances of SQL. Same with staging. Just too costly for each test/ stage
> environment to have its own box.
>
> "Branden Smith" <BradSmith@.hotmail.com> wrote in message
> news:OLToO%23LiFHA.3656@.TK2MSFTNGP09.phx.gbl...
> > Installing multiple instances of SQL Server is really good.
> > Some one can tell me when exactly those situations arises.
> > SQL 2K.
> > Thanks,
> > Smith
> >
> >
>|||For example, staging is supposed to be a mirror(or as close as possible) of
production. Not even close if only on 1 instance.
"Branden Smith" <BradSmith@.hotmail.com> wrote in message
news:%23xthHdMiFHA.2644@.TK2MSFTNGP09.phx.gbl...
> What is the necessity of having three instances of SQL Server on one box?
> You create all the USER databases in one instance only. What we are
> gaining
> by doing that approach? What is that we are missing by not going to
> multiple
> instances?
> Thanks,
> Smith
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eqxS2AMiFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> I have 6 production environments. I have two test boxes, each with 3
>> instances of SQL. Same with staging. Just too costly for each test/ stage
>> environment to have its own box.
>>
>> "Branden Smith" <BradSmith@.hotmail.com> wrote in message
>> news:OLToO%23LiFHA.3656@.TK2MSFTNGP09.phx.gbl...
>> > Installing multiple instances of SQL Server is really good.
>> > Some one can tell me when exactly those situations arises.
>> > SQL 2K.
>> > Thanks,
>> > Smith
>> >
>> >
>>
>

Necessity.....

Installing multiple instances of SQL Server is really good.
Some one can tell me when exactly those situations arises.
SQL 2K.
Thanks,
SmithI have 6 production environments. I have two test boxes, each with 3
instances of SQL. Same with staging. Just too costly for each test/ stage
environment to have its own box.
"Branden Smith" <BradSmith@.hotmail.com> wrote in message
news:OLToO%23LiFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Installing multiple instances of SQL Server is really good.
> Some one can tell me when exactly those situations arises.
> SQL 2K.
> Thanks,
> Smith
>|||What is the necessity of having three instances of SQL Server on one box?
You create all the USER databases in one instance only. What we are gaining
by doing that approach? What is that we are missing by not going to multiple
instances?
Thanks,
Smith
"ChrisR" <noemail@.bla.com> wrote in message
news:eqxS2AMiFHA.1444@.TK2MSFTNGP10.phx.gbl...
> I have 6 production environments. I have two test boxes, each with 3
> instances of SQL. Same with staging. Just too costly for each test/ stage
> environment to have its own box.
>
> "Branden Smith" <BradSmith@.hotmail.com> wrote in message
> news:OLToO%23LiFHA.3656@.TK2MSFTNGP09.phx.gbl...
>|||For example, staging is supposed to be a mirror(or as close as possible) of
production. Not even close if only on 1 instance.
"Branden Smith" <BradSmith@.hotmail.com> wrote in message
news:%23xthHdMiFHA.2644@.TK2MSFTNGP09.phx.gbl...
> What is the necessity of having three instances of SQL Server on one box?
> You create all the USER databases in one instance only. What we are
> gaining
> by doing that approach? What is that we are missing by not going to
> multiple
> instances?
> Thanks,
> Smith
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eqxS2AMiFHA.1444@.TK2MSFTNGP10.phx.gbl...
>

Necessary help-changing language of date

Hi all,
I have a problem in converting English Calendar into Iranian
Calendar.
How I can change that?
What should I do?
Thanks,
Nassa
Google is your friend.
http://www.ortelius.de/kalender/pers_en.php
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nassa" <nassim.czdashti@.gmail.com> wrote in message
news:1171084998.186349.170490@.m58g2000cwm.googlegr oups.com...
> Hi all,
> I have a problem in converting English Calendar into Iranian
> Calendar.
> How I can change that?
> What should I do?
> Thanks,
> Nassa
>

Necessary Help-->Database Mirroring

Hi Everyone,
I want to start database mirroring in SQL server 2005 -Microsoft SQL
server management studio Express-,I use windows XP P2.
I try to use database mirroring but it doesn't work.What should I
do?Which Operating System, and SQL server should I use?
Thanks,
Nassa
data base mirroring is not supported on SQL Server Express.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nassa" <nassim.czdashti@.gmail.com> wrote in message
news:1169268992.469697.24710@.s34g2000cwa.googlegro ups.com...
> Hi Everyone,
> I want to start database mirroring in SQL server 2005 -Microsoft SQL
> server management studio Express-,I use windows XP P2.
> I try to use database mirroring but it doesn't work.What should I
> do?Which Operating System, and SQL server should I use?
> Thanks,
> Nassa
>
|||Hilary Cotter
thanks,
I use SQL server 2005-Standard.
Nassa
Hilary Cotter wrote:[vbcol=seagreen]
> data base mirroring is not supported on SQL Server Express.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Nassa" <nassim.czdashti@.gmail.com> wrote in message
> news:1169268992.469697.24710@.s34g2000cwa.googlegro ups.com...
|||Hilary Cotter
thanks,
I use SQL server 2005-Standard.
Nassa
Hilary Cotter wrote:[vbcol=seagreen]
> data base mirroring is not supported on SQL Server Express.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Nassa" <nassim.czdashti@.gmail.com> wrote in message
> news:1169268992.469697.24710@.s34g2000cwa.googlegro ups.com...
|||Hilary Cotter
thanks,
I use SQL server 2005-Standard.
Nassa
Hilary Cotter wrote:[vbcol=seagreen]
> data base mirroring is not supported on SQL Server Express.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Nassa" <nassim.czdashti@.gmail.com> wrote in message
> news:1169268992.469697.24710@.s34g2000cwa.googlegro ups.com...
|||Ok can you try this then:
--On your principal
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = PARTNER);
--on your mirror
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION);
--on your witness
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = WITNESS);
--Start endpoints
ALTER ENDPOINT [Mirroring] STATE = STARTED;
--now backup your database on the principal copy it to your mirror and
restore it there with norecovery
--Connect to your mirror
-- Specify the partner from the mirror server - note this can be an ip
address rather than the fqdn
ALTER DATABASE [AdventureWorks] SET PARTNER
='TCP://Mirror.corp.mycompany.com:5022';
--Connect to your principle
-- Specify the witness from the principal server
ALTER DATABASE [AdventureWorks] SET WITNESS =
'TCP://Wittness.corp.mycompany.com:5022';
That's it
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nassa" <nassim.czdashti@.gmail.com> wrote in message
news:1169355344.015758.44280@.m58g2000cwm.googlegro ups.com...
> Hilary Cotter
> thanks,
> I use SQL server 2005-Standard.
> Nassa
> Hilary Cotter wrote:
>
|||Thanks Hilary Cotter,
if I dont have witness how I should specify the principle?
Thanks,
Nassa
On Jan 23, 4:23 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Ok can you try this then:
> --On your principal
> CREATE ENDPOINT [Mirroring]
> AS TCP (LISTENER_PORT = 5022)
> FOR DATABASE_MIRRORING (ROLE = PARTNER);
> --on your mirror
> CREATE ENDPOINT [Mirroring]
> AS TCP (LISTENER_PORT = 5022)
> FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION);
> --on your witness
> CREATE ENDPOINT [Mirroring]
> AS TCP (LISTENER_PORT = 5022)
> FOR DATABASE_MIRRORING (ROLE = WITNESS);
> --Start endpoints
> ALTER ENDPOINT [Mirroring] STATE = STARTED;
> --now backup your database on the principal copy it to your mirror and
> restore it there with norecovery
> --Connect to your mirror
> -- Specify the partner from the mirror server - note this can be an ip
> address rather than the fqdn
> ALTER DATABASE [AdventureWorks] SET PARTNER
> ='TCP://Mirror.corp.mycompany.com:5022';
> --Connect to your principle
> -- Specify the witness from the principal server
> ALTER DATABASE [AdventureWorks] SET WITNESS =
> 'TCP://Wittness.corp.mycompany.com:5022';
> That's it
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Nassa" <nassim.czdas...@.gmail.com> wrote in message
> news:1169355344.015758.44280@.m58g2000cwm.googlegro ups.com...
>
>
>
>
>
Thanks Hilary

>
> - Show quoted text -
|||Hi Hilary Cotter,
It gives me some error messages.
1- after I backup the database in the primary server,then I copy it to
the mirror server and when I try to restore it in the mirror server,it
gives me an error.
2-after I write the code
ALTER DATABASE [AdventureWorks] SET PARTNER
='TCP://Mirror.corp.mycompany.com:5022';
again gives me an error which said : database mirroring transport is
disable in the endpoint configuration.
please inform me as soon as possible.
Thanks,
Nassa
On Feb 14, 9:05 am, "Nassa" <nassim.czdas...@.gmail.com> wrote:[vbcol=seagreen]
> Thanks Hilary Cotter,
> if I dont have witness how I should specify the principle?
> Thanks,
> Nassa
> On Jan 23, 4:23 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
>
|> > ALTER DATABASE [AdventureWorks] SET PARTNER
>
>
>
>
>
>
>
> Thanks Hilary
>
>
>
> - Show quoted text -- Hide quoted text -
> - Show quoted text -