Multiple Instances on the same virtual server?

Is this possible?
I would like to have 2 instances on my virtual SQL 2005 cluster,
listening on different ports.
For example, if my virtual hostname is HOSTNAME123 can I have one
instance on HOSTNAME123, port 1433 and the second instance on
HOSTNAME123, port 1432?
Or does each clustered instance need its own, distinct hostname?
Thanks in advance.
No, not with SQL2005 (or SQL7/2000). Each clustered SQL instance must have
its own network name (i.e. its own virtual server name).
Linchi
"brimoore@.state.pa.us" wrote:

> Is this possible?
> I would like to have 2 instances on my virtual SQL 2005 cluster,
> listening on different ports.
> For example, if my virtual hostname is HOSTNAME123 can I have one
> instance on HOSTNAME123, port 1433 and the second instance on
> HOSTNAME123, port 1432?
> Or does each clustered instance need its own, distinct hostname?
> Thanks in advance.
>
|||Thanks for your response.
Linchi Shea wrote:[vbcol=seagreen]
> No, not with SQL2005 (or SQL7/2000). Each clustered SQL instance must have
> its own network name (i.e. its own virtual server name).
> Linchi
> "brimoore@.state.pa.us" wrote:
|||I dont think Linchi has correct information here.
Without having tested this, SQL 2005 supports Volume Mount Points(VMP). So,
you can install numerous instances on one VirtualServerName. They need to
have unique IP and Instance Name.
Am I off here or is this VMP only supported on non-clustered installations?
VMP should help the driveletter saturations problems that large installations
experienced during SQL2000.
Any thoughts on this?
"Linchi Shea" wrote:
[vbcol=seagreen]
> No, not with SQL2005 (or SQL7/2000). Each clustered SQL instance must have
> its own network name (i.e. its own virtual server name).
> Linchi
> "brimoore@.state.pa.us" wrote:
|||Linchi is correct, you need a separate clustered group and network name for
every instance.
Now onto the VMP issue. I think you are confusing disks with names now. Yes,
SQL2005 supports VMP. The support is for any type of SQL install, clustered
or not.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"ipconfig2" <ipconfig2@.discussions.microsoft.com> wrote in message
news:DF86C624-8B4F-4FB6-9F44-B301D7EE8BA6@.microsoft.com...[vbcol=seagreen]
>I dont think Linchi has correct information here.
> Without having tested this, SQL 2005 supports Volume Mount Points(VMP).
> So,
> you can install numerous instances on one VirtualServerName. They need to
> have unique IP and Instance Name.
> Am I off here or is this VMP only supported on non-clustered
> installations?
> VMP should help the driveletter saturations problems that large
> installations
> experienced during SQL2000.
> Any thoughts on this?
> "Linchi Shea" wrote:
sql

Multiple Instances and Processor

Does anyone know how sql handles processor delegation when you install
multiple instances of sql?
For example:
Default installation will use up to 100% of the processor.
Default installation with 1 named instance will each take 50%.
Default installation with 2 named instances will each take 33.333%
Thanks,
Markeach instance will potentially schedule spids on each processor unless you
use affinity mask within each instance...
once a spid is released to a processor SQL doesn't have any control over
it's excution...
actually... SQL assigns spids to something called a user mode scheduler
which is affinitized to a processor and the UMS hands the spids off...
books like Inside SQL Server 2000 or Ken Henderson's new internals books
have great content on topics like that...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Mark Newton" <mark.newton@.bdsmarketing.com> wrote in message
news:v11svv82d84m8rvn1vaic6fskbn0jabfo8@.4ax.com...
> Does anyone know how sql handles processor delegation when you install
> multiple instances of sql?
> For example:
> Default installation will use up to 100% of the processor.
> Default installation with 1 named instance will each take 50%.
> Default installation with 2 named instances will each take 33.333%
> Thanks,
> Marksql

Multiple Instance & Licensing

Can multiple instances of SQL Server be installed on the same machine under the same license?For example, a named instanced called WAREHOUSE and another called CHGMGMTSYS.

Yes, you can run multiple instances under the same license. For more detailed license info, check out this link:

http://www.microsoft.com/sql/howtobuy/default.mspx

Thanks,
Sam Lester (MSFT)

Multiple insert command

Is it possible to insert a row, where you want to insert the same data in the row, but just change a number?

In vb i would use this for example (not actually the code, just an example)

For x=1 to 10

sql = "Insert into (Number, .... .. ) values (" & x & ", ......)

execute command

Next

Is it possible to make one command where i could something like this?

There are many ways of doing it depending on your requirements. Below is a simple example that assumes that you have variables with the data and you want to insert say 10 rows:

Code Snippet

insert into your_table (id, ....)

select top(10) row_number() over(order by object_id) as seq, @.col1, @.col2, @.col3

from sys.objects

order by object_id

-- You can also generate more rows on the fly using cross join of several tables like

insert into your_table (id, ....)

select top(1000) row_number() over(order by o1.object_id) as seq, @.col1, @.col2, @.col3

from sys.objects as o1 cross sys.objects as o2

order by o1.object_id

Multiple indexes on the same column

Is it possible to create one clustered index and one nonclustered index on the same column of a table?

For example, if we have a table Employees (EmpID, EmpName), then will SQL Server execute the following commands successfully?

Create clustered index indx1 on Employees (EmpID)
Go

Create nonclustered index indx2 on Employees (EmpID)
Go

yes, it is possible but it is not clear why you would want to do that. Here is the output of sp_help on the table empoyee and you will notice that there are two indexes one clustered and other non-clustered on the same key.
Name Owner Type Created_datetime

-- -- - --

Employees dbo user table 2005-10-28 09:30:42.733

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation

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

EmpID int no 4 10 0 yes (n/a) (n/a) NULL

EmpName varchar no 100 yes no yes SQL_Latin1_General_CP1_CI_AS

Identity Seed Increment Not For Replication

-- -

No identity column defined. NULL NULL NULL

RowGuidCol

--

No rowguidcol column defined.

Data_located_on_filegroup

--

PRIMARY

index_name index_description index_keys

-- -

indx1 clustered located on PRIMARY EmpID

indx2 nonclustered located on PRIMARY EmpID

No constraints are defined on object 'employees', or you do not have permissions.

No foreign keys reference table 'employees', or you do not have permissions on referencing tables.

No views with schema binding reference table 'employees'.

|||Well, I was asked this question in an interview :)
Even I am not aware about any practical aspect of the question.

Thank you for your response, anyway.|||Thinking more about it, I think it can be useful under following scenarios
(1) you have range queries on empid that retrieves large number of columns in the table so it may make sense to have clustered index on empid.
AND
(2) you have range query on empid where you need only a subset of columns. you can make those subset of columns as included columns such that your query is covered by the index. This will eliminate access to the data page.

thanks

Multiple expressions.

Good Morning all.

Is it possible to put multiple expressions in one cell.

Here is an example of the expressions I'm using. I'm currently having to put them horizontally in a seperate cell.

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "Y", 1, Nothing))

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "N", 1, Nothing))

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "NA", 1, Nothing))

Desired output will look sim to this in one cell

Y = 5

N = 3

NA = 0

Thanks,

Rick

Yes you can use line feed and carriage return characters like this to achieve your result:

="Y = " & CStr(Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "Y", 1, Nothing))) & Chr(10) & Chr(13) &

"N = " & CStr(Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "N", 1, Nothing))) & Chr(10) & Chr(13) &

"NA = " & CStr(Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "NA", 1, Nothing)))

Shyam

sql

Multiple expressions.

Good Morning all.

Is it possible to put multiple expressions in one cell.

Here is an example of the expressions I'm using. I'm currently having to put them horizontally in a seperate cell.

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "Y", 1, Nothing))

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "N", 1, Nothing))

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "NA", 1, Nothing))

Desired output will look sim to this in one cell

Y = 5

N = 3

NA = 0

Thanks,

Rick

Yes you can use line feed and carriage return characters like this to achieve your result:

="Y = " & CStr(Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "Y", 1, Nothing))) & Chr(10) & Chr(13) &

"N = " & CStr(Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "N", 1, Nothing))) & Chr(10) & Chr(13) &

"NA = " & CStr(Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "NA", 1, Nothing)))

Shyam

|||This is perfect! Thanks|||

Please mark my post as answer. Thanks.

Shyam

Multiple entries script...help

Ok,
In one table, I need to find all instances of multiple entries of an
invoice number in the column InvoiceNumber.

For example, there may be more than one entrie of 321, 567, and 987
in column InvoiceNumber.

321 skin care product
321 vitamins
321 bottled water

567 rubbing alcohol
567 dental floss

987 fabric softener
987 tissue paper
987 marlboro cigaretts

Any help is appreciated.
Thanks,
Trintselect InvoiceNumber, count(*) as 'Occurrences'
from dbo.MyTable
group by InvoiceNumber
having count(*) > 1

Simon|||Simon,
Thanks!
Trint

..Net programmer
trinity.smith@.gmail.com

*** Sent via Developersdex http://www.developersdex.com ***

Multiple DISTINCTS?

Hello,
Is it possible to constrain a query so that I receive only the duplicate rows (all column data matches)? For example:
SELECT DISTINCT COL1,
DISTINCT COL2,DISTINCT COL3
FROM TABLE
I did a search on multiple DISTINCTS but came up with nothing. Thanks in advance.DISTINCT applys to the whole row of the result set, not individual columns of it. When you do aSELECT DISTINCT col1, col2, col3
FROM myTableyou will get one row for any given combination of col1, col2, and col3. You can use DISTINCT within aggragate functions to get aggrigates of the unique values, but when applied against a result set, DISTINCT applies to the whole set (row).

-PatP|||uh, huh?

SELECT DISTINCT Col1, Col2, Col3

Will give you 1 unique row..

You want to only see dups?

SELECT Col1, Col2, Col3
FROM myTable99
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1

Is that what your after?|||Originally posted by Brett Kaiser
uh, huh?

SELECT DISTINCT Col1, Col2, Col3

Will give you 1 unique row..

You want to only see dups?

SELECT Col1, Col2, Col3
FROM myTable99
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1

Is that what your after?

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

Thanks for your suggestions. I am looking for a resultset of:

COL1 COL2 COL3
---------
brown tall dog
brown tall dog
brown tall dog

Thanks again for your help.|||Did the code give you what you wanted...I'm not sure...|||Originally posted by Brett Kaiser
Did the code give you what you wanted...I'm not sure...

Not yet, but I could be doing something wrong.

I've tried the following:
SELECT DISTINCT COL1, COL2, COL3
FROM myTABLE

and:
SELECT COL1, COL2, COL3
FROM myTABLE
GROUP BY COL1, COL2, COL3

and:
SELECT DISTINCT COL1, COL2, COL3
FROM myTABLE
HAVING COUNT(*) > 1

and I've tried:
SELECT COL1, COL2, COL3
FROM myTABLE
GROUP BY COL1, COL2, COL3
HAVING COUNT(*) > 1|||Well that should work...

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(25),Col2 varchar(25),Col3 varchar(25))
GO

INSERT INTO myTable99(Col1,Col2,Col3)
select 'Brown','Tall','Dog' UNION ALL
select 'Brown','Tall','Dog' UNION ALL
select 'Brown','Tall','Dog' UNION ALL
select 'Blonde','Small','Pussy cat' UNION ALL
select 'Red','Medium','Snapper Turtle'
GO

SELECT Col1, Col2, Col3
FROM myTable99
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1
GO

DROP TABLE myTable99
GO

Oh, are you looking for all three?|||SELECT * FROM myTable99 o WHERE EXISTS(
SELECT *
FROM myTable99 i
WHERE o.Col1 = i.Col1
AND o.Col2 = i.Col2
AND o.Col3 = i.Col3
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1)
GO

All three...|||Brett,
That should do it! Combined with the info in the links provided in this thread: http://www.dbforums.com/t991775.html

I should be able to put something together. I really do appreciate all your hard work.

Regards,
Americus Johnson

Thanks also to Pat!|||Hard Work...Lord no...

That's why I became a dba...

:D

PS Don't forget to take ALL of your animal freinds...ya might get lucky|||Originally posted by Brett Kaiser
Hard Work...Lord no...

That's why I became a dba...

:D

PS Don't forget to take ALL of your animal freinds...ya might get lucky

True dat.|||yup

Multiple dimensions from single dimension table

Hi,
SQL 2000 AS.
I have a dimension table 'Periods' example :
1 = Jan 2005
2 = Feb 2005
3 = Mar 2005
etc. NB This is not a standard time dimension.
The table has a key on 'PeiodID' = 1, 2, 3 etc.
My Fact table has say 2 period entries per row (say Billing, Collection) -
these can be different per row - eg Billing = 2, Collection = 4 etc.).
How do I add my Period dimension twice but linked to different columns, so
that each one shows up with a different name (say BillingPeriod,
CollectionPeriod).
I tried to create separate dimensions 'BillingPeriod' and 'CollectionPeriod'
but these both display on the table view as 'Period'. I CAN link both key
fields to Period but then can't see how each one is differentiated '.
Many Thanks
Regards
GrahamCreate and use a View for one of the dimensions.
HTH,
Mike
"GrahamS" wrote:

> Hi,
> SQL 2000 AS.
> I have a dimension table 'Periods' example :
> 1 = Jan 2005
> 2 = Feb 2005
> 3 = Mar 2005
> etc. NB This is not a standard time dimension.
> The table has a key on 'PeiodID' = 1, 2, 3 etc.
> My Fact table has say 2 period entries per row (say Billing, Collection) -
> these can be different per row - eg Billing = 2, Collection = 4 etc.).
> How do I add my Period dimension twice but linked to different columns, so
> that each one shows up with a different name (say BillingPeriod,
> CollectionPeriod).
> I tried to create separate dimensions 'BillingPeriod' and 'CollectionPerio
d'
> but these both display on the table view as 'Period'. I CAN link both key
> fields to Period but then can't see how each one is differentiated '.
> Many Thanks
> Regards
> Graham
>|||Mike,
Yup - thanks for the reply - I tried this today and it works pretty well.
I was hoping for something maybe not so 'dimension intensive', as I actually
have a number of similar dimensions to 'double up on'.
Thanks again.
regards
Graham
"Mike Austin" wrote:
[vbcol=seagreen]
> Create and use a View for one of the dimensions.
> HTH,
> Mike
> "GrahamS" wrote:
>|||May be you could use the table alias.
for eg. select a.factID billingperiond.periodID,
collectionperiod.PeriodIDfrom Billingfact a, periods as
billingperiond,periods as collectionperiod where
a.billingperiodID=billingperiod.PeriodID and
a.collectionperiodID=collectionperiod.periodID
Thx
Harsh
"GrahamS" wrote:

> Hi,
> SQL 2000 AS.
> I have a dimension table 'Periods' example :
> 1 = Jan 2005
> 2 = Feb 2005
> 3 = Mar 2005
> etc. NB This is not a standard time dimension.
> The table has a key on 'PeiodID' = 1, 2, 3 etc.
> My Fact table has say 2 period entries per row (say Billing, Collection) -
> these can be different per row - eg Billing = 2, Collection = 4 etc.).
> How do I add my Period dimension twice but linked to different columns, so
> that each one shows up with a different name (say BillingPeriod,
> CollectionPeriod).
> I tried to create separate dimensions 'BillingPeriod' and 'CollectionPerio
d'
> but these both display on the table view as 'Period'. I CAN link both key
> fields to Period but then can't see how each one is differentiated '.
> Many Thanks
> Regards
> Graham
>|||You will need to create a view over the dimension table and use that as
the source for one of your dimensions otherwise when the cube gets
populated you will only get facts where the 2 dates are equal.
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <B42197EA-E216-4310-A284-4416BB23A034@.microsoft.com>,
Harsh@.discussions.microsoft.com says...
> May be you could use the table alias.
> for eg. select a.factID billingperiond.periodID,
> collectionperiod.PeriodIDfrom Billingfact a, periods as
> billingperiond,periods as collectionperiod where
> a.billingperiodID=billingperiod.PeriodID and
> a.collectionperiodID=collectionperiod.periodID
> Thx
> Harsh
> "GrahamS" wrote:
>
>

Multiple dimensions from single dimension table

Hi,
SQL 2000 AS.
I have a dimension table 'Periods' example :
1 = Jan 2005
2 = Feb 2005
3 = Mar 2005
etc. NB This is not a standard time dimension.
The table has a key on 'PeiodID' = 1, 2, 3 etc.
My Fact table has say 2 period entries per row (say Billing, Collection) -
these can be different per row - eg Billing = 2, Collection = 4 etc.).
How do I add my Period dimension twice but linked to different columns, so
that each one shows up with a different name (say BillingPeriod,
CollectionPeriod).
I tried to create separate dimensions 'BillingPeriod' and 'CollectionPeriod'
but these both display on the table view as 'Period'. I CAN link both key
fields to Period but then can't see how each one is differentiated ?.
Many Thanks
Regards
Graham
Create and use a View for one of the dimensions.
HTH,
Mike
"GrahamS" wrote:

> Hi,
> SQL 2000 AS.
> I have a dimension table 'Periods' example :
> 1 = Jan 2005
> 2 = Feb 2005
> 3 = Mar 2005
> etc. NB This is not a standard time dimension.
> The table has a key on 'PeiodID' = 1, 2, 3 etc.
> My Fact table has say 2 period entries per row (say Billing, Collection) -
> these can be different per row - eg Billing = 2, Collection = 4 etc.).
> How do I add my Period dimension twice but linked to different columns, so
> that each one shows up with a different name (say BillingPeriod,
> CollectionPeriod).
> I tried to create separate dimensions 'BillingPeriod' and 'CollectionPeriod'
> but these both display on the table view as 'Period'. I CAN link both key
> fields to Period but then can't see how each one is differentiated ?.
> Many Thanks
> Regards
> Graham
>
|||Mike,
Yup - thanks for the reply - I tried this today and it works pretty well.
I was hoping for something maybe not so 'dimension intensive', as I actually
have a number of similar dimensions to 'double up on'.
Thanks again.
regards
Graham
"Mike Austin" wrote:
[vbcol=seagreen]
> Create and use a View for one of the dimensions.
> HTH,
> Mike
> "GrahamS" wrote:
|||May be you could use the table alias.
for eg. select a.factID billingperiond.periodID,
collectionperiod.PeriodIDfrom Billingfact a, periods as
billingperiond,periods as collectionperiod where
a.billingperiodID=billingperiod.PeriodID and
a.collectionperiodID=collectionperiod.periodID
Thx
Harsh
"GrahamS" wrote:

> Hi,
> SQL 2000 AS.
> I have a dimension table 'Periods' example :
> 1 = Jan 2005
> 2 = Feb 2005
> 3 = Mar 2005
> etc. NB This is not a standard time dimension.
> The table has a key on 'PeiodID' = 1, 2, 3 etc.
> My Fact table has say 2 period entries per row (say Billing, Collection) -
> these can be different per row - eg Billing = 2, Collection = 4 etc.).
> How do I add my Period dimension twice but linked to different columns, so
> that each one shows up with a different name (say BillingPeriod,
> CollectionPeriod).
> I tried to create separate dimensions 'BillingPeriod' and 'CollectionPeriod'
> but these both display on the table view as 'Period'. I CAN link both key
> fields to Period but then can't see how each one is differentiated ?.
> Many Thanks
> Regards
> Graham
>
|||You will need to create a view over the dimension table and use that as
the source for one of your dimensions otherwise when the cube gets
populated you will only get facts where the 2 dates are equal.
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <B42197EA-E216-4310-A284-4416BB23A034@.microsoft.com>,
Harsh@.discussions.microsoft.com says...
> May be you could use the table alias.
> for eg. select a.factID billingperiond.periodID,
> collectionperiod.PeriodIDfrom Billingfact a, periods as
> billingperiond,periods as collectionperiod where
> a.billingperiodID=billingperiod.PeriodID and
> a.collectionperiodID=collectionperiod.periodID
> Thx
> Harsh
> "GrahamS" wrote:
>

Multiple datasets in a single chart?

Is it possible to incorporate data from 2 datasets into a single chart? For example I have 1 set of data from 1 db which allows me to display datapoint "A" by week. I have a second dataset from another db which allows me to display datapoint "B" by week. I'd like to plot each of these separate datapoints on a single chart with the x axis being week and the y axis being a stacked bar chart incorporating both A and B values as individual data series. Further, assuming this is possible, is it then possible to have 1 series displayed as an area while the 2nd series is a column or line?
Thanks in advance!

Did you ever find a way to do this? In the middle of a Reporting Services 2005 evaluation and we are trying to reproduce this functionality.|||Sorry this is currently not supported. You would need to join the two result sets already in the dataset query to be able to use it in the same chart.

-- Robert

Multiple datasets in a single chart?

Is it possible to incorporate data from 2 datasets into a single chart? For example I have 1 set of data from 1 db which allows me to display datapoint "A" by week. I have a second dataset from another db which allows me to display datapoint "B" by week. I'd like to plot each of these separate datapoints on a single chart with the x axis being week and the y axis being a stacked bar chart incorporating both A and B values as individual data series. Further, assuming this is possible, is it then possible to have 1 series displayed as an area while the 2nd series is a column or line?
Thanks in advance!

Did you ever find a way to do this? In the middle of a Reporting Services 2005 evaluation and we are trying to reproduce this functionality.|||Sorry this is currently not supported. You would need to join the two result sets already in the dataset query to be able to use it in the same chart.

-- Robert

multiple dataset fields in one table

How to use multiple dataset fields in one table.

Example:

I have one table --Table1.

Two DataSets --DataSet1,DataSet2

Table1 refers DataSet1.I want to use DataSet2 field in Table1.It is taking SUM if did this but I don't need sum.

A table can only be associated with 1 dataset.|||

thanx Adam.

Can you give me the solution

|||

It depends on the data and what else you are doing in the report. What I've done in the past is either

combine the data into 1 dataset in the query or|||

if you only want to use a SUM-Value from your second dataset,

this shouldnt be a problem

go to the field in your table where you want the SUM-Value

-> Expression -> Datasets -> <Dataset2> -> Sum(<yourValueToSum>)

this inserts a Sum-Function for your value over the scope of dataset 2

greets

|||

you could do something like this

=Sum(Fields!fieldnam.Value, "Dataset Name")

and it would get the value from the specified dataset

Multiple Databases?

Is it possible to retrieveDatafromSQL Database #1and insert it intoSQL Database #2using aStored Procedure?Thanks. If so Can you show example. ThanksYou could do something like this perhaps, I'm not sure on how efficient it would be. This is untested but maybe it will give you enough info to go on, be sure that the dbo has access to the second db you are wanting to insert data into. The other option is a trigger, but you asked how to do it from a SPROC.

CREATE PROCEDURE dbo.YourSPROCName
(
@.Whatever INT --I assume you have a UID your're passing in
)
AS
DECLARE @.Val1 VARCHAR
DECLARE @.Val2 VARCHAR
DECLARE @.Val2 VARCHAR
SELECT @.Val1 =(SELECT Col1 FROM Database1.dbo.Table1 WHERE Whatever=@.Whatever)
SELECT @.Val2 =(SELECT Col2 FROM Database1.dbo.Table1 WHERE Whatever=@.Whatever)
SELECT @.Val2 =(SELECT Col3 FROM Database1.dbo.Table1 WHERE Whatever=@.Whatever)
INSERT INTO Database2.dbo.Table1 (Whatever,Col1,Col2,Col3) VALUES (@.Whatever,@.Val1,@.Val2,@.Val3)
GO

Good luck.|||Thanks PD_Goss

I'm Trying to get it to work.
Question:?
How do you check to see if dbo has access to the second db?

---

Here's what I have so far but its not working. Any Ideas? Thanks.

Create Procedure spDatabaseExport

AS
BEGIN
SELECT MyDatabase1.dbo.Inventory
SELECT QtyInStock
WHERE
ProductID = @.6
GO

BEGIN
DECLARE @.SalesCount INT
INSERT INTO MyDatabase2.dbo.counter3
SELECT SalesCount FROM counter3
WHERE
ID = 2
END
GO|||If you are using dbo it should already have access.

Can you explain what it is you are trying to accomplish?

Here's a quick run down.

AS
 --if you are wanting to query a value from one db and insert it into another this is how:
DECLARE @.SalesCount INT
--Grab what you want
SELECT @.SalesCount = (SELECT SalesCount FROM MyDatabase1.dbo.counter3 WHERE
ID = 2 ) --is this supposed to be a static variable?
--put it in the other table
INSERT INTO MyDatabase2.dbo.counter3 (SalesCount) VALUES (@.SalesCount)
GO

|||Thanks PD_Goss,
What I'm Trying to do is set up a SaleHitCounter to keep track of the amount of individual items sold & store the values into a Different Database. Maybe increment the value by + 1
everytime an item is sold.

I have an OrderItems table Created with the following Columns

uid, OrderID, ProductID, AddressID, Quantity, ProName, Price,

I would like to create a Stored Procedure that's able to update (or) transfer into another Database the total values for each individual items sold. Maybe use the (ProductID, & Quantity, ) Columns? What would be the best way to do this?

TheProductID Column - contains all the Item Numbers e.g. 25, 52, 12, etc.

Is there a way (Stored Procedure) to tap into theProductID & Quantityand somehow get an accumulated total for each individual item - then transfer values to a different database.
But I'm confused on how to go about writing a Stored Procedure to accomplish this. Thanks Again for the help.|||Is the OrderItems table very large. If not, it will be wise idea to run the query directly on OrderItems table as following

select
 ProductID,
 sum (Quantity)
from
 OrderItems
--where -- Need the following 2 lines only if you want to filter
-- ProductID in (25, 52)
group by
 ProductID

If you want to dump this to another database, Run this query on the second database as following

Insert into
 tblCounts
select
 ProductID,
 sum (Quantity)
from
 Database1.dbo.OrderItems
--where -- Need the following 2 lines only if you want to filter
-- ProductID in (25, 52)
group by
 ProductID

Hope this helps

Anil|||This is how I would do it (personal preference I guess). It's important to have constraints (ProductID) so you need to place this after the procedure that updates the OrderItems table, hopefully you have a SPROC that is doing this so you can grab the ProductID parameter for the following code.

--After the Order Items Update
DECLARE @.QtySold INT
SELECT @.QtySold =(SELECT SUM(Quantity) FROM OrderItems WHERE ProductID=@.ProductID)
IF ((SELECT COUNT(ProductID) FROM Database2.dbo.SalesHitCounter WHERE ProductID=@.ProductID)=1)
BEGIN
UPDATE Database2.dbo.SalesHitCounter SET QtySold=@.QtySold WHERE ProductID=@.ProductID
END
ELSE
BEGIN
INSERT INTO Database2.dbo.SalesHitCounter (ProductID,QtySold) VALUES (@.ProductID,@.QtySold)
END

|||Thanks Guys for all the help!

But Database2.dbo.tblCounts is not receiving any inserts from OrderItems (via) Stored Procedure.

Here's where I'm at so far.
TableOrderItems is located inDatabase1
And TabletblCountsis Located inDatabase2

Both Tables are the same (except) tblCounts has nothing in it.
Both tables have the following Columns:(uid, OrderID, ProductID, Quantity, ProductName)
Here is how I have my Stored Procedure set up for now:

CREATE PROCEDURE webcounter4

AS
BEGIN
Select
ProductID,
sum (Quantity)
from
OrderItems
where
ProductID in (1)
group by
ProductID
Insert into
tblCounts
select
ProductID,
sum (Quantity)
from
Database2.dbo.tblCounts
where
ProductID in (1)
group by
ProductID
END
GO

---

Here is my the code from my .ASPX page that fire the Stored Procedure

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@. Page Language="VB" Debug="true" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.SQLClient" %
<script runat="server"
 Sub Page_Load(Source as Object, E as EventArgs)
 Dim objCon As New SQLConnection("server=MyServer\InstanceName;User id=SA;password=Password;database=Database1")
 Dim cmd As SQLCommand = New SQLCommand("EXEC dbo.webcounter4", objCon)
 objCon.Open()
 Dim r as SQLDataReader
 r = cmd.ExecuteReader()
 r.read()
 strtblcounts.text = "Sale Hits : " & r.item(0)
 end sub
</script>

---

<BR>
Also I'm not sure how to incorperate this procedure:</B

DECLARE @.Quantity INT
DECLARE @.ProductID INT
DECLARE @.QtySold INT

SELECT @.Quantity =(SELECT SUM(Quantity) FROM OrderItems WHERE
ProductID=@.ProductID)

IF ((SELECT COUNT(PRODUCTID) FROM Database2.dbo.tblcounts WHERE
ProductID=@.ProductID)=1)

BEGIN

UPDATE Database2.dbo.tblCounts SET Quantity=@.Quantity WHERE
ProductID=@.ProductID

END

ELSE

BEGIN

INSERT INTO Database2@..dbo.tblCounts (ProducrID,Quantity) VALUES
(@.ProductID,@.Quantity)
|||This SPROC would insert a count or update the count of each item sold when an order is created, each item having its own row. It would need to be in the SPROC that handles order creation. I assume you are passing in the ProductID so the declaration is not necessary. The tblcounts structure would need to be more like UID, ProductID, QtySold.

--DECLARE @.Quantity INT --Not needed
--DECLARE @.ProductID INT --Not needed
DECLARE @.QtySold INT
--Get the Qty Sold of an item
SELECT @.QtySold =(SELECT SUM(Quantity) FROM OrderItems WHERE
ProductID=@.ProductID)
--Check if the items exists in the table
IF ((SELECT COUNT(ProductID ) FROM Database2.dbo.tblcounts WHERE
ProductID=@.ProductID)=1)
BEGIN
--If a record exists... update it
UPDATE Database2.dbo.tblCounts SET QtySold=@.QtySold WHERE
ProductID=@.ProductID
END
ELSE
BEGIN
--If a record does not exist... insert one
INSERT INTO Database2.dbo.tblCounts (ProductID,QtySold) VALUES
(@.ProductID,@.QtySold)
END

Your webcounter4 SPROC would only need this:

SELECT ProductID,QtySold FROM Database2.dbo.tblcounts WHERE ProductID IN (1)
GROUP BY ProductID

|||Thanks PD_Goss,

I don't have a SPROC that handles the Order Creation, I have aOrderDetails.aspx & OrderDetails.aspx.vb pages I believe that handles Order Creation. If you need to see the OrderDetails.aspx.vb Class file -- I can post it.|||I would definitely use SPROC's for your data work. I started out using inline SQL and when I made the transition it was much easier to manage. You can post the code and I will whip you up a SPROC.|||HI PD_Goss

I was wondering if it would be possible just toemail the class files to you. I have 3 rather large & lenghty class files & I'm not sure which one exactly you need to see --So I'd like to send you all three Class files. But rather than post, could I just Email the files. Thanks.|||Sure, PublicDispAcct@.hotmail.com

Multiple data sources applied to the same report row

Woulf anyone know, if RS would work with multiple data sources applied to
the same report. For example, in a report, there are two data sources
pointing to different servers and few of the report columns are associated
with one data source and few with the other. How RS can handle this sort of
requirement because supposedly there is no option in RS to create a dataset
that is based on multiple data sources.
There are few alternatives like using linked server logic or sub reporting
however we are unable to find any direct way of accomplishing it.You can create as many data sources as you want in each report.
You can only use 1 query per table or graph though. I put some of my tables
next to each other, to look like 1 table in some of my reports.
"Mike Hernandez" <mike@.newage.com> wrote in message
news:%23ZFy0OEwFHA.256@.TK2MSFTNGP15.phx.gbl...
> Woulf anyone know, if RS would work with multiple data sources applied to
> the same report. For example, in a report, there are two data sources
> pointing to different servers and few of the report columns are associated
> with one data source and few with the other. How RS can handle this sort
of
> requirement because supposedly there is no option in RS to create a
dataset
> that is based on multiple data sources.
> There are few alternatives like using linked server logic or sub
reporting
> however we are unable to find any direct way of accomplishing it.
>|||Hi Mike,
Could you tell me how to impose relations between two Datasets.
Regards
AmitK
Cindy Lee wrote:
> You can create as many data sources as you want in each report.
> You can only use 1 query per table or graph though. I put some of my tables
> next to each other, to look like 1 table in some of my reports.
>
> "Mike Hernandez" <mike@.newage.com> wrote in message
> news:%23ZFy0OEwFHA.256@.TK2MSFTNGP15.phx.gbl...
> > Woulf anyone know, if RS would work with multiple data sources applied to
> > the same report. For example, in a report, there are two data sources
> > pointing to different servers and few of the report columns are associated
> > with one data source and few with the other. How RS can handle this sort
> of
> > requirement because supposedly there is no option in RS to create a
> dataset
> > that is based on multiple data sources.
> >
> > There are few alternatives like using linked server logic or sub
> reporting
> > however we are unable to find any direct way of accomplishing it.
> >
> >

Multiple Data Sources

Can I create a report from multiple data sources?
For example can I create a report that pulls Client ID's from SQL and
include data from an Access DB source that had the same Client ID's?Yes create the different datasources using data tab and put the data in the
layout using two datasets.
Amarnath
"Joe" wrote:
> Can I create a report from multiple data sources?
> For example can I create a report that pulls Client ID's from SQL and
> include data from an Access DB source that had the same Client ID's?
>
>

Multiple Columns into Single Row -- Very urgent

Hi. I want to return multiple rows into a single row in different columns. For example my query returns something like this

The query looks like this
Select ID, TYPE, VALUE From myTable Where filtercondition = 1

ID TYPE VALUE
1 type1 12
1 type2 15
2 type1 16
2 type2 19

Each ID will have the same number of types and each type for each ID might have a different value. So if there are only two types then each ID will have two types. Now I want to write the query in such a way that it returns

ID TYPE1 TYPE2 VALUE1 VALUE2
1 type1 type2 12 15
2 type1 type2 16 19

Type1, Type2, Value1, and Value2 are all dynamic. Can someone help me please. Thank you.

I've done something like this, but not in SQL. What I do is build a datatable from my select results, populate it, and then return that to my caller. It works something like;

Get Results

Loope through results to get my columns (In my case there may not be a value for every type.)

Build datatable

Loop through results again, populating datatable.

Return datatable

multiple columns in "in" search

Is it possible to put a set of columns into a select statement using "in"

For example:

select count(*) from table1 where (col1, col2, col3, col4) in (select a.col1, a.col2, a.col3, a.col4 from table1 a where........)

This is possible in this form for Oracle and DB2 but I cannot get it to work in Sql Server.

Any ideas?

No. SQL Server doesn't support row value constructors yet. You can use a correlated sub-query instead like:

select count(*) from table1 as b where exists (select * from table1 a

where a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3 and a.col4 = b.col4)

|||

Thanks, that solves part of my problem.

What I want to be able to do is to delete using the same (similar) syntax, but from an earlier seperate post I cant prefix a table in a delete query!?!?

My select query is:

select count(*) from schema.datas1 as a where exists
(select * from schema.datas1 z where
z.exam=a.exam and
z.customer_id=a.customer_id and
z.language_id=a.language_id and
z.course_id=a.course_id)
and a.exam <=
(select max(b.exam)-NUMBER from schema.datas1 b where
b.customer_id = a.customer_id and
b.course_id = a.course_id and
b.language_id = a.language_id
)

The way it works is that every person can be registered for a specific language and a specific course. They can sit am exam for a specific course and language as many times as they want, but they get a new entry in the datas1 table but with an incremented exam number from the previous entry. All other values can be the same. Now we want to be able to delete some of the older rows from the datas1 table (the earlier exam entries and keep the most recent ones up to a number specified in the NUMBER variable)

For example, a customer 1001 has sat language 1 in course 2 exam 5 times. If I set the NUMBER variable to 3 then all I want to remain in the datas1 table is entries for exam 3, 4 and 5
The sql above will work as a select but cannot get it to work when trying to do the delete .eg.

delete from schema.datas1 as a where exists
(select * from schema.datas1 z where
z.exam=a.exam and
z.customer_id=a.customer_id and
z.language_id=a.language_id and
z.course_id=a.course_id)
and a.exam <=
(select max(b.exam)-NUMBER from schema.datas1 b where
b.customer_id = a.customer_id and
b.course_id = a.course_id and
b.language_id = a.language_id
)

Please help

|||

You need to use the TSQL extension for the DELETE statement. ANSI SQL doesn't allow aliasing of source table in the DELETE statement. You can modify your DELETE statement like:

delete from schema.datas1 /* note additional FROM clause here */

from schema.datas1 as a where exists
(select * from schema.datas1 z where
z.exam=a.exam and
z.customer_id=a.customer_id and
z.language_id=a.language_id and
z.course_id=a.course_id)
and a.exam <=
(select max(b.exam)-NUMBER from schema.datas1 b where
b.customer_id = a.customer_id and
b.course_id = a.course_id and
b.language_id = a.language_id
)

|||

That works great. Thanks!

One more question.

Now I want to break my deletes from the datas1 table into batches of say 1000 records at a time using a loop statement in perl. What exactly would be the transact sql (I assume using top) to do this?

Again I can get this to work for the select statement, but having difficulty with the delete.

Many Thanks!

|||

In SQL Server 2005, you can also specify TOP clause for DML statements. So you can run a batch like below which will delete 1000 rows at a time:

while(1=1)

begin

delete top(1000) from schema.datas1

from schema.datas1

.....

if @.@.rowcount = 0 break

end

-- you can parameterize the TOP clause like:

declare @.n int

set @.n = 10000

while(1=1)

begin

delete top(@.n) from schema.datas1

from schema.datas1

.....

if @.@.rowcount = 0 break

end

In older versions of SQL Server, you can do the same using SET ROWCOUNT like.

-- you can parameterize the TOP clause like:

declare @.n int

set @.n = 10000

set rowcount @.n

while(1=1)

begin

delete from schema.datas1

from schema.datas1

.....

if @.@.rowcount = 0 break

end

set rowcount 0

Multiple column full text search

Is there a way to specify which column weights more when doing full text
search..
For example, I have three column, filename, description and data. I did
full text index on all three column, but want to list the hits in filename
first, is there a way to do it sql 2005?
--Xin Chen
your might want to check out some of these postings for examples of how
to do this.
http://groups-beta.google.com/groups...ff&qt_s=Search
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