Multi-page report: time

Showing posts with label time. Show all posts

Monday, March 26, 2012

Multiple instance name

Hi,
Is there a way to make the default instance (SQL 2005) work at the same time as a "named instance" ou make a "named instance" work
with two or more names?
My problem is that I'm using the QuickStart for VS 2005 and all code work with a "SQLExpress" named instance and I just have a
default instance o SQL Server Developer 2005 (and I would not install another instance just for that).
Thanks,
MaxYes, but you have to do this on the client. Use the SQL Server Configuration Manager to add an alias
for the desired server\instance with whatever server\instance name you like.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Max André Bündchen" <nospam@.microsoft.com> wrote in message
news:%238KccV9MGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Is there a way to make the default instance (SQL 2005) work at the same time as a "named instance"
> ou make a "named instance" work with two or more names?
> My problem is that I'm using the QuickStart for VS 2005 and all code work with a "SQLExpress"
> named instance and I just have a default instance o SQL Server Developer 2005 (and I would not
> install another instance just for that).
> Thanks,
> Max
>|||I try it but when I try a connect with the alias name the server I receive: "this failure may be caused by the fact that under the
default settings SQL Server does not allow remote connections".
My alias is like that:
Name: mycomputer\SQLExpress
Port: <blank>
Protocol: TCP/IPa
Server: mycomputer
The default SQL Server is running under Network Service account.
Thanks,
Max
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escreveu na mensagem
news:uHHdea9MGHA.2764@.TK2MSFTNGP10.phx.gbl...
> Yes, but you have to do this on the client. Use the SQL Server Configuration Manager to add an alias for the desired
> server\instance with whatever server\instance name you like.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Max André Bündchen" <nospam@.microsoft.com> wrote in message news:%238KccV9MGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> Is there a way to make the default instance (SQL 2005) work at the same time as a "named instance" ou make a "named instance"
>> work with two or more names?
>> My problem is that I'm using the QuickStart for VS 2005 and all code work with a "SQLExpress" named instance and I just have a
>> default instance o SQL Server Developer 2005 (and I would not install another instance just for that).
>> Thanks,
>> Max
>|||As the message says, the default settings for SQL 2005 do not allow remote
connections. This has nothing to do with name resolution. You need to
enable remote connections using the Surface Area Configuration tool. Unlike
SQL 2000, SQL 2005 (all editions) does not allow remote connections
immediately following the installation. This is so you can patch a SQL
Server before exposing it to network connections, should such future
patching be necessary.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Max André Bündchen" <nospam@.microsoft.com> wrote in message
news:uib%23tp9MGHA.3064@.TK2MSFTNGP10.phx.gbl...
>I try it but when I try a connect with the alias name the server I receive:
>"this failure may be caused by the fact that under the default settings SQL
>Server does not allow remote connections".
> My alias is like that:
> Name: mycomputer\SQLExpress
> Port: <blank>
> Protocol: TCP/IPa
> Server: mycomputer
> The default SQL Server is running under Network Service account.
> Thanks,
> Max
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> escreveu na mensagem news:uHHdea9MGHA.2764@.TK2MSFTNGP10.phx.gbl...
>> Yes, but you have to do this on the client. Use the SQL Server
>> Configuration Manager to add an alias for the desired server\instance
>> with whatever server\instance name you like.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Max André Bündchen" <nospam@.microsoft.com> wrote in message
>> news:%238KccV9MGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> Is there a way to make the default instance (SQL 2005) work at the same
>> time as a "named instance" ou make a "named instance" work with two or
>> more names?
>> My problem is that I'm using the QuickStart for VS 2005 and all code
>> work with a "SQLExpress" named instance and I just have a default
>> instance o SQL Server Developer 2005 (and I would not install another
>> instance just for that).
>> Thanks,
>> Max
>>
>|||Yes, I see. Thanks for the tips!
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> escreveu na mensagem news:O5bday9MGHA.1180@.TK2MSFTNGP09.phx.gbl...
> As the message says, the default settings for SQL 2005 do not allow remote connections. This has nothing to do with name
> resolution. You need to enable remote connections using the Surface Area Configuration tool. Unlike SQL 2000, SQL 2005 (all
> editions) does not allow remote connections immediately following the installation. This is so you can patch a SQL Server before
> exposing it to network connections, should such future patching be necessary.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Max André Bündchen" <nospam@.microsoft.com> wrote in message news:uib%23tp9MGHA.3064@.TK2MSFTNGP10.phx.gbl...
>>I try it but when I try a connect with the alias name the server I receive: "this failure may be caused by the fact that under the
>>default settings SQL Server does not allow remote connections".
>> My alias is like that:
>> Name: mycomputer\SQLExpress
>> Port: <blank>
>> Protocol: TCP/IPa
>> Server: mycomputer
>> The default SQL Server is running under Network Service account.
>> Thanks,
>> Max
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escreveu na mensagem
>> news:uHHdea9MGHA.2764@.TK2MSFTNGP10.phx.gbl...
>> Yes, but you have to do this on the client. Use the SQL Server Configuration Manager to add an alias for the desired
>> server\instance with whatever server\instance name you like.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Max André Bündchen" <nospam@.microsoft.com> wrote in message news:%238KccV9MGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> Is there a way to make the default instance (SQL 2005) work at the same time as a "named instance" ou make a "named instance"
>> work with two or more names?
>> My problem is that I'm using the QuickStart for VS 2005 and all code work with a "SQLExpress" named instance and I just have a
>> default instance o SQL Server Developer 2005 (and I would not install another instance just for that).
>> Thanks,
>> Max
>>
>>
>

Friday, March 23, 2012

Multiple FTP tasks that connect to same server at same time error

I have this job that download 4 files once a month from the same server. The files are sizable and I need to download them in less than 5 hours total. In 2000 I use an active x script to generate the ftp script then execute the script. all four files download at the same time in 4 different tasks with no issues.

I am rewrote the process in 2005 so that it uses the IS FTP function but when all 4 ftp tasks kick off they all fail... instantly. the initially shared the same FTP connection manager so I created different ones for each and still the same result

the error is one that relates to changing directories.... Now if I just run one of the tasks it runs fine it is just when more than one try to run at once. I ended up putting in 10 second delays between each ftp task kicking off and it works just fine...

Does this sound like a bug?

Also... I am on SQL 2005 Enterprise SP1 on Windows 2003 enterprise SP1.

I can also reproduce the problem when multiple FTP tasks share the same FTP connection manager. But everything works fine if I create individual FTP connection manager for each task. This does sound like a bug to me so I am going to log a bug issue. We will investigate the problem and address it ASAP. Thanks you|||Thanks...

Multiple Forms

I have multiple page form that may differ each time they are call. Each may also be mutilple page long. Having trouble trying to print page 1 then swithcing to new form and print it......ect. Any Help would be wonderful. Crystal support don't unserstand my problem.

Thanks In Advance

HenryMe neither, can u please describe ur problem little bit more.|||Let me try one more once.

I have to print an Invoice Form

It has three diferent formats in other words each page has a diff Vb Form

Sometimes we must repeat the same looking form other times we want to switch to another form.

This seem to be such a simple problem have done this kind of thing for years but Crystal Reporter just isn't very willing to do what I want.

I am willing to fax said forms to anyone willing to help.

I feel sure there must be other people out there that would love to be able to do what I have described.

Thanks in advance

Henrysql

Wednesday, March 21, 2012

multiple fields from common lookup table

I haven't written SQL in some time and I'm extremely rusty, so help
would be appreciated and thanks in advance.
I have a table (main) that has multiple fields that contain a lookup id
to a common lookup table (lookup). How do I write the query to pull
back one result set that has the form below?
select ?
where main.key = @.key
result set
--
key (from main)
behavior description (from lookup)
appearance description (from lookup)
other description (from lookup)
main
--
key
behaviorID
appearanceID
otherID
lookup
--
lookupID
descriptionPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Sba,
Try:
DECLARE @.KEYVAL INT
SET @.KEYVAL = 1
SELECT KEYVAL, L1.DESCRIPTION AS 'BEHAVIOR', L2.DESCRIPTION AS 'APPEARANCE',
L3.DESCRIPTION AS 'OTHER'
FROM MAIN M
JOIN LOOKUP L1
ON M.BEHAVORID = L1.LOOKUPID
JOIN LOOKUP L2
ON M.APPEARANCEID = L2.LOOKUPID
JOIN LOOKUP L3
ON M.OTHERID = L3.LOOKUPID
WHERE KEYVAL = @.KEYVAL
HTH
Jerry
"sba" <pub9@.s105192480.onlinehome.us> wrote in message
news:1129764599.221796.33700@.g49g2000cwa.googlegroups.com...
>I haven't written SQL in some time and I'm extremely rusty, so help
> would be appreciated and thanks in advance.
> I have a table (main) that has multiple fields that contain a lookup id
> to a common lookup table (lookup). How do I write the query to pull
> back one result set that has the form below?
> select ?
> where main.key = @.key
>
> result set
> --
> key (from main)
> behavior description (from lookup)
> appearance description (from lookup)
> other description (from lookup)
>
> main
> --
> key
> behaviorID
> appearanceID
> otherID
>
> lookup
> --
> lookupID
> description
>|||Looks like you have the famous One True Lookup Table. A classic database
design error.
Anyway, you can join multiple times to the same table:
SELECT M.key, T1.description, T2.description, T3.description
FROM main AS M
JOIN lookup AS T1
ON M.behaviourid = T1.id
JOIN lookup AS T2
ON M.appearanceid = T2.id
JOIN lookup AS T3
ON M.otherid = T3.id ;
David Portas
SQL Server MVP
--

multiple distinct and time...

Here comes the table:
Id_Prices | Shop | Product | Price | tStamp
----
--
1 eMall Pen 10 20.5.2005
2 eMall Pen 11 21.5.2005
3 eMall Pen 9 22.5.2005
4 webShop Pen 10 22.5.2005
5 webShop Pen 12 23.5.2005
6 InetShop Pen 10 20.5.2005
7 netOultel Pen 9 19.5.2005
8 netOultel Pencil 5 19.5.2005
9 netOultel Pencil 6 20.5.2005
table hold records of:
multiple PRICES (distinguished by date)
for various PRODUCTS
from various SHOPS
now...
A) I need to get only most curent (date) PRICES from all SHOPs
for specific PRODUCT e.q.
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
B) List of all products and their most recent prices...
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
Pencil netOultel 20.5.2005 6
Anybody can point me in the right direction how to aproach this...?
PS: DB table structure and normalization indexes omited for brewity
thanx for any hint... PetttHi ,
Try this :
create table #tmp
(
Id_Prices integer ,
Shop char(9),
Product char(6),
Price integer,
tStamp char (9)
) ;
---
insert into #tmp values (1, 'eMall','Pen',10,'20.5.2005');
insert into #tmp values (2, 'eMall','Pen',11,'21.5.2005');
insert into #tmp values (3, 'eMall','Pen',9,'22.5.2005');
insert into #tmp values (4, 'webShop','Pen',10,'22.5.2005');
insert into #tmp values (5, 'webShop','Pen',12,'23.5.2005');
insert into #tmp values (6, 'InetShop','Pen',10,'20.5.2005');
insert into #tmp values (7, 'netOultel','Pen',9,'19.5.2005');
insert into #tmp values (8, 'netOultel','Pencil',5,'19.5.2005');
insert into #tmp values (9, 'netOultel','Pencil',6,'20.5.2005');
---
select a.* , b.price
from
(
select product , shop , max (tstamp) tstamp
from #tmp
group by product , shop
) a ,
(
select *
from #tmp
) b
where a.shop= b.shop
and a.product = b.product
and a.tstamp = b.tstamp
-- and b.product = 'Pen'
group by a.product , a.shop , a.tstamp , b.price
order by 1
"Petr SIMUNEK" wrote:

> Here comes the table:
> Id_Prices | Shop | Product | Price | tStamp
> ----
--
> 1 eMall Pen 10 20.5.200
5
> 2 eMall Pen 11 21.5.200
5
> 3 eMall Pen 9 22.5.20
05
> 4 webShop Pen 10 22.5.2005
> 5 webShop Pen 12 23.5.2005
> 6 InetShop Pen 10 20.5.2005
> 7 netOultel Pen 9 19.5.200
5
> 8 netOultel Pencil 5 19.5.200
5
> 9 netOultel Pencil 6 20.5.200
5
>
> table hold records of:
> multiple PRICES (distinguished by date)
> for various PRODUCTS
> from various SHOPS
>
> now...
> A) I need to get only most curent (date) PRICES from all SHOPs
> for specific PRODUCT e.q.
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> B) List of all products and their most recent prices...
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> Pencil netOultel 20.5.2005 6
> Anybody can point me in the right direction how to aproach this...?
> PS: DB table structure and normalization indexes omited for brewity
> thanx for any hint... Pettt
>
>
>

multiple distinct and time...

Here comes the table:
Id_Prices | Shop | Product | Price | tStamp
----
--
1 eMall Pen 10 20.5.2005
2 eMall Pen 11 21.5.2005
3 eMall Pen 9 22.5.2005
4 webShop Pen 10 22.5.2005
5 webShop Pen 12 23.5.2005
6 InetShop Pen 10 20.5.2005
7 netOultel Pen 9 19.5.2005
8 netOultel Pencil 5 19.5.2005
9 netOultel Pencil 6 20.5.2005
table hold records of:
multiple PRICES (distinguished by date)
for various PRODUCTS
from various SHOPS
now...
A) I need to get only most curent (date) PRICES from all SHOPs
for specific PRODUCT e.q.
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
B) List of all products and their most recent prices...
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
Pencil netOultel 20.5.2005 6
Anybody can point me in the right direction how to aproach this...?
PS: DB table structure and normalization indexes omited for brewity
thanx for any hint... PetttHi
The difference between the first and second query is that you are
resistricting to be a single product. One possible solution would be:
CREATE TABLE #prices ( Id_Prices int, Shop varchar(20), Product varchar(20),
Price int , tStamp datetime )
INSERT INTO #prices ( Id_Prices, Shop, Product, Price, tStamp )
SELECT 1, 'eMall', 'Pen', 10, '20050520'
UNION ALL SELECT 2, 'eMall', 'Pen', 11, '20050521'
UNION ALL SELECT 3, 'eMall', 'Pen', 9, '20050522'
UNION ALL SELECT 4, 'webShop', 'Pen', 10, '20050522'
UNION ALL SELECT 5, 'webShop', 'Pen', 12, '20050523'
UNION ALL SELECT 6, 'InetShop', 'Pen', 10, '20050520'
UNION ALL SELECT 7, 'netOultel', 'Pen', 9, '20050519'
UNION ALL SELECT 8, 'netOultel', 'Pencil', 5, '20050519'
UNION ALL SELECT 9, 'netOultel', 'Pencil', 6, '20050520'
-- For Pen only
SELECT p.*
from #prices p
JOIN ( SELECT shop, product, Max(tstamp) as tstamp
from #prices GROUP BY shop, product ) q ON q.shop = p.shop AND q.product =
p.product AND p.tstamp = q.tstamp
WHERE p.product = 'Pen'
or alternatively:
SELECT p.*
from #prices p
WHERE tstamp = ( SELECT Max(tstamp)
from #prices q WHERE q.shop = p.shop AND q.product = p.product AND
q.product = 'Pen')
AND p.product = 'Pen'
-- For all products
SELECT p.*
from #prices p
JOIN ( SELECT shop, product, Max(tstamp) as tstamp
from #prices GROUP BY shop, product ) q ON q.shop = p.shop AND q.product =
p.product AND p.tstamp = q.tstamp
If you are using this join alot you may want to create a view.
John
"Petr SIMUNEK" wrote:

> Here comes the table:
> Id_Prices | Shop | Product | Price | tStamp
> ----
--
> 1 eMall Pen 10 20.5.200
5
> 2 eMall Pen 11 21.5.200
5
> 3 eMall Pen 9 22.5.20
05
> 4 webShop Pen 10 22.5.2005
> 5 webShop Pen 12 23.5.2005
> 6 InetShop Pen 10 20.5.2005
> 7 netOultel Pen 9 19.5.200
5
> 8 netOultel Pencil 5 19.5.200
5
> 9 netOultel Pencil 6 20.5.200
5
>
> table hold records of:
> multiple PRICES (distinguished by date)
> for various PRODUCTS
> from various SHOPS
>
> now...
> A) I need to get only most curent (date) PRICES from all SHOPs
> for specific PRODUCT e.q.
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> B) List of all products and their most recent prices...
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> Pencil netOultel 20.5.2005 6
> Anybody can point me in the right direction how to aproach this...?
> PS: DB table structure and normalization indexes omited for brewity
> thanx for any hint... Pettt
>
>|||thanx a lot guys...
It works like a charm of course... thanks again
PS: would you care to share with me how long experience with SQL do you have
?
(so I know how long the road is... :o))
thanx|||Hi
It is a never ending road... when you think you know it all something new
always turns up...and then there is the ageing process where you start to
forget what you have learn't anyhow.
John
"Petr SIMUNEK" wrote:

> thanx a lot guys...
> It works like a charm of course... thanks again
> PS: would you care to share with me how long experience with SQL do you ha
ve
> ?
> (so I know how long the road is... :o))
> thanx
>
>sql

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

Monday, March 12, 2012

Multiple databases and SQL

I am facing a problem within SQL server. Is it possible to create SQL query to two separate databases at the same time ?

There are logical relations between tables in separate databases and I need to create some JOINs between tables in separate dbs into my SQL query. I there any solutions to this problem ?is the databases are on the same server you just need to use a three part nameing convention <Database Name>.<Owner Name>.<Table Name>

So if in db1 you have a table named tbl1 owned by usr1 and in db2 you have a table named tbl2 owned by dbo you would...

select *
from db1.usr1.tbl1 t1
join db2.dbo.tbl2 t2 on t1.ParentKey = t2.ChildKey|||On the same server?
If so, just do a
select * from database1.owner1.table1
join database2.owner2.table2 on table1.field1=table2.field2

If the're on different servers (but still mssql) then use a linked server (heterogenous join)
select * from server1.database1.owner1.table1
join server2.database2.owner2.table2 on table1.field1=table2.field2

If it's a different dbms, use openquery or openrowset (depending on what exactly you're trying to do)

Originally posted by asset1
I am facing a problem within SQL server. Is it possible to create SQL query to two separate databases at the same time ?

There are logical relations between tables in separate databases and I need to create some JOINs between tables in separate dbs into my SQL query. I there any solutions to this problem ?

Friday, March 9, 2012

Multiple Data Sources

Is there away to change the datasource at the time of deployment of a report? I know there is a production and debug deployment for specifying URLs and Folders. Is there anyway to specify the datasource, so you can have one for production and another for debug / test? Do you keep two datasources in the project? The production datasource already exists in the target environment.

Any help would be appreciated.

Thanks!

DotNetNow

Well:

We keep (as I think all people do) environments for test and production - so we test everything with RS and then deploy to the production server and just change (or point to the production environment

within the Project Properties or within the data sources within the Project itself such as:

Data Source=(For Example the Server Name);Initial Catalog=(For Example the data base name)

That means that when a developer is done doing what a developer does - the DataSource Name is changed to the QA group - all applications are deployed to the QA group -

The QA group does what they do and they have a production person change the Data Source parameters to the production environment.

That is one of the features I really like with RS is the deployment capabilities - it all relies on what you define within the data sources within the project or the properties of the project.

Hope this helps!

Multiple data insertion with For clause

Suppose I have a table named test with two fields(sl int,age int).
I want to insert 10 records all at a time.The records are in an incremental manner.I like to insert 1,2,3,4,5,6,7,8,9,10 for sl column and
22,23,24,25,26,27,28,29,30,31 for age column.But the procedure should follow C protype using for (j=1;j<10,j++) clause.
Is it possible to insert in SQL SERVER following c protype?
What is the fastest way for inserting multiple sequevcial data?
SubhasishFor relatively small sequential sets, do something like this...

INSERT INTO TEST(sl, age)
SELECT a.i, a.i+21
FROM (
SELECT i = 1 UNION
SELECT i = 2 UNION
SELECT i = 3 UNION
SELECT i = 4 UNION
SELECT i = 5 UNION
SELECT i = 6 UNION
SELECT i = 7 UNION
SELECT i = 8 UNION
SELECT i = 9 UNION
SELECT i = 10 ) as a

You can build up the derived table query quickly with cut-and-paste, then go back and fix the values

For bigger sequential sets, build yourself a temporary table of sequential integers like this:

CREATE TABLE #i
(x INT IDENTITY(1,1),
y INT)

INSERT INTO #i
VALUES(NULL)

INSERT INTO #i
SELECT y FROM #i

Running the last statement over and over will populate table #i with sequential integers in the x column. 11 executions gets you 1K rows, 21 gets you 1M rows, ... Then use the temporary table to drive your insert.

INSERT INTO test(sl, age)
SELECT #i.x, #i.x+21
FROM #i
WHERE #i.x < 100 -- for 100 rows|||I think s/he's looking for a loop as well...

USE Northwind
GO

CREATE TABLE myTable99 (sl int,age int)
GO

DECLARE @.x int, @.y int

SELECT @.x = 1, @.y = 1

WHILE @.x < 100
BEGIN
INSERT INTO myTable99 (sl, age)
SELECT @.X, 1*@.y UNION ALL
SELECT @.X, 2*@.y UNION ALL
SELECT @.X, 3*@.y UNION ALL
SELECT @.X, 4*@.y UNION ALL
SELECT @.X, 5*@.y UNION ALL
SELECT @.X, 6*@.y UNION ALL
SELECT @.X, 7*@.y UNION ALL
SELECT @.X, 8*@.y UNION ALL
SELECT @.X, 9*@.y UNION ALL
SELECT @.X, 10*@.y

SELECT @.x = @.x + 1, @.y = @.y + 1
END

SELECT COUNT(*) FROM myTable99
GO

DROP TABLE myTable99
GO

Multiple count(*) on same column Same query?

Hello,
Thank you for taking time to read my question.
I have somewhat a similar problem like this person in devarticles forum
- http://shorterlink.com/?NY7F6G
In my case, I have some cities, and every city will have Status column
with values 1/2/3/4 like this:
NYC 1 11/4/2005
NYC 3 11/2/2005
CHI 3 11/1/2005
CHI 4 11/4/2005
LA 1 11/20/2005
...
...
...
I'm trying to have a query that will have a result like this:
Status1Count Status2Count Status3Count Status4Count
NYC 100 80 20 40
LA 20 40 10 25
CHI 38 35 33 20
This next query gives the count for one status. I'm expecting to get
all the count for all the status like the above table:
SELECT a.citydesc, COUNT(a.status) as Status1Count
FROM a
WHERE a.Status=1
GROUP BY a.citydesc
Right now, in the existing code, they are hitting the database for
every cell in every row. I'm researching on how best to rewrite the
code to achieve this within one trip to the DB. Is it possible to
achieve this using just a single SELECT query ? Could you please give
me some pointers?
This is w.r.t SQL Server 2000
Thank you in Advance.See if this works (untested):
select citydesc,
SUM(case status when 1 then 1 else 0 end) as Status1Count,
SUM(case status when 2 then 1 else 0 end) as Status2Count,
SUM(case status when 3 then 1 else 0 end) as Status3Count,
SUM(case status when 4 then 1 else 0 end) as Status4Count
from a
group by citydesc
<dcmetro@.gmail.com> wrote in message
news:1132682010.500230.168440@.f14g2000cwb.googlegroups.com...
> Hello,
> Thank you for taking time to read my question.
> I have somewhat a similar problem like this person in devarticles forum
> - http://shorterlink.com/?NY7F6G
> In my case, I have some cities, and every city will have Status column
> with values 1/2/3/4 like this:
> NYC 1 11/4/2005
> NYC 3 11/2/2005
> CHI 3 11/1/2005
> CHI 4 11/4/2005
> LA 1 11/20/2005
> ...
> ...
> ...
> I'm trying to have a query that will have a result like this:
> Status1Count Status2Count Status3Count Status4Count
> NYC 100 80 20 40
> LA 20 40 10 25
> CHI 38 35 33 20
> This next query gives the count for one status. I'm expecting to get
> all the count for all the status like the above table:
> SELECT a.citydesc, COUNT(a.status) as Status1Count
> FROM a
> WHERE a.Status=1
> GROUP BY a.citydesc
> Right now, in the existing code, they are hitting the database for
> every cell in every row. I'm researching on how best to rewrite the
> code to achieve this within one trip to the DB. Is it possible to
> achieve this using just a single SELECT query ? Could you please give
> me some pointers?
> This is w.r.t SQL Server 2000
> Thank you in Advance.
>|||Hello Raymond,
Thank you very much for the hint. Yes, your suggestion works for me as
it is.
I got couple of questions when I was testing it. How do we rewrite the
query if the status is dynamic and is coming from another table? Is
there any way we could get the total of each StatusCount as a row in
the same query?
Thank you very much again.|||<dcmetro@.gmail.com> wrote in message
news:1132688082.041928.277300@.g43g2000cwa.googlegroups.com...
> Hello Raymond,
> Thank you very much for the hint. Yes, your suggestion works for me as
> it is.
> I got couple of questions when I was testing it. How do we rewrite the
> query if the status is dynamic and is coming from another table? Is
> there any way we could get the total of each StatusCount as a row in
> the same query?
> Thank you very much again.
>
Take a look at ROLLUP and see if this doesn't help you.
Rick Sawtell|||Nothing simple (unless someone else can jump in).
What I would do is this and handle presentation client side.
SELECT a.citydesc, a.status, COUNT(a.status) AS status
FROM a
GROUP BY a.citydesc, a.status
ORDER BY a.citydesc, a.status
Results:
CHI status1 38
CHI status2 35
...
LA status1 20
LA status2 40
LA status3 10
...
NYC...
If you're still looking for something else, post DDL (create table
statements) and insert statements for both tables.
<dcmetro@.gmail.com> wrote in message
news:1132688082.041928.277300@.g43g2000cwa.googlegroups.com...
> Hello Raymond,
> Thank you very much for the hint. Yes, your suggestion works for me as
> it is.
> I got couple of questions when I was testing it. How do we rewrite the
> query if the status is dynamic and is coming from another table? Is
> there any way we could get the total of each StatusCount as a row in
> the same query?
> Thank you very much again.
>|||Rick: Thanks for the pointer on ROLLUP. I was looking for that exact
functionality. Thanks again.
Raymond:
Here are the DDL for the two tables I'm talking about.
CREATE TABLE tblCities (
[CityID] [int] NULL ,
[CityDesc] [varchar] (20) NULL ,
[StatusID] [int] NULL,
[CreatedDate] [datetime] NULL )
CREATE TABLE [tblStatuses] (
[StatusID] [int] NOT NULL ,
[Description] [varchar] (25) NOT NULL ,
)
and some insert statements as well.
INSERT INTO [tblStatuses]([StatusID], [Description])
VALUES(1 , 'Status1 ' )
INSERT INTO [tblStatuses]([StatusID], [Description])
VALUES(2 , 'Status2 ' )
INSERT INTO [tblStatuses]([StatusID], [Description])
VALUES(3 , 'Status3 ' )

Monday, February 20, 2012

Multiple Ado Connections

Hi, I have a VB application running on SQL Enterprise Edition. Physically there are only 20 users loging in to the software at one time. However i can see more that 12 SPIDs for each user. The developers have been opening a new connection for each form in the application i guess. Is it normal or is there any specific requirement that foces them to do so? Shall i need to change the default WORKER-THREADS option on the server for peformance? Suggestions please!!!

Regards.They should close connections when the retrieving of data is completed
(or updates, or inserts are completed)

one connection per user is the best way to control connection leaks
(when developpers forget to close an unusefull connection)|||Means that they can open multiple connections for retrieving data for multiple forms however the connections must be closed later. What about the max-worker threads. Is there any need to change the settings, for i have more that 300 SPIDs at a time on the server.
Thanx for the kind response.|||programs are almost always "linear"
> open connection
> retrieve data1
> close connection

> open connection
> retrieve data2
> close connection
...

even if you've got 300 users there's rarely 300 connections
opened at the same time because they are closed most of the time
(except if users do big SELECTs on your database connections will
be opened longer)

Originally posted by TALAT
Means that they can open multiple connections for retrieving data for multiple forms however the connections must be closed later. What about the max-worker threads. Is there any need to change the settings, for i have more that 300 SPIDs at a time on the server.
Thanx for the kind response.|||The developers(genius) say that they close the connections in an error handler routine only, i.e., if an exception is thrown; they have written the code to close the connection for that particular form, otherwise not. What a nice coding technique they have adopted.

Regards.|||they could open a connection and leave it opened
but they should reuse this connection
instead of reopening new ones

this could be an other way to control the connection leaks

> start program : open ONE connection
> window1
> window2
> use program
> use program
...
> end program : close THE connection

in this case
Nb users = Nb connections

but if you have for example a max nb of connections at 100
only 100 users can connect at the same time

but with the other solution (open/close unuseful connection)
you can have more users than the max connections (100 in this example)
the users are not connected all the time of the use of the program
and they do not connect at the same time
if the max connections is reached a user just has to wait a little for a
connection to be liberated by an other user|||good luck with your consuming-connections-developper

just say to him that a connection is a very precious thing to waste
many users dreamed of having one free
and db-administrator cannot multiply them indefinitly

If ONE should use a connection
ONE should use it shortly
ONE should think of liberating it rapidly
ONE should carefully close it
ONE should know that ONE is not alone
For MANY use the program
Even if only ONE develops it|||I shall rather force them to stop doing this and to change their coding techiniques. Thanx for guiding and responding so quick. Thanx again!!

Regards.

Multipe DBs on 1 Server backup schedule

is it best to kick off the backups at the same time, i.e. 1am, or to space
them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
Any links or articles welcome.
Thanks,
MPM
As always it depends. But in general if you can issue the backups at
different times you will most likely get better performance for each and
less impact on the general users. This is due to the large amounts of I/O
(and to some extent CPU) that backups can induce. If you run multiple
backups at the same time you run the risk of maximizing your I/O bandwidth
and even causing network bottlenecks if done remotely. If you are going to
backup multiple dbs one right after the other it is best to do them in a
loop vs. setting specific times. That is because you never know if the
first is finished before the second one starts and so on. If you kick them
off after the previous completes you never have to worry about adjusting the
times as DB sizes change. Here is a sample of that:
-- Backup all user Databases --
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Pubs_Full')
EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
'C:\Data\Backups\DD_Pubs_Full.bak'
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Northwind_Full')
EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
'C:\Data\Backups\DD_Northwind_Full.bak'
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'DD_' + @.DBName + '_Full'
SET @.Name = @.DBName + N' Full Backup'
SET @.Descript = @.DBName + N' Full Backup - ' +
CONVERT(NVARCHAR(16),GETDATE(),112)
BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Andrew J. Kelly SQL MVP
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
> is it best to kick off the backups at the same time, i.e. 1am, or to space
> them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
> Any links or articles welcome.
> Thanks,
> MPM
|||Andrew,
Thanks a lot for that excellent reply.
MPM
"Andrew J. Kelly" wrote:

> As always it depends. But in general if you can issue the backups at
> different times you will most likely get better performance for each and
> less impact on the general users. This is due to the large amounts of I/O
> (and to some extent CPU) that backups can induce. If you run multiple
> backups at the same time you run the risk of maximizing your I/O bandwidth
> and even causing network bottlenecks if done remotely. If you are going to
> backup multiple dbs one right after the other it is best to do them in a
> loop vs. setting specific times. That is because you never know if the
> first is finished before the second one starts and so on. If you kick them
> off after the previous completes you never have to worry about adjusting the
> times as DB sizes change. Here is a sample of that:
>
> -- Backup all user Databases --
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Pubs_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
> 'C:\Data\Backups\DD_Pubs_Full.bak'
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Northwind_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
> 'C:\Data\Backups\DD_Northwind_Full.bak'
>
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
> DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'DD_' + @.DBName + '_Full'
> SET @.Name = @.DBName + N' Full Backup'
> SET @.Descript = @.DBName + N' Full Backup - ' +
> CONVERT(NVARCHAR(16),GETDATE(),112)
> BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
> DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
> news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
>
>

Multipe DBs on 1 Server backup schedule

is it best to kick off the backups at the same time, i.e. 1am, or to space
them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
Any links or articles welcome.
Thanks,
MPMAs always it depends. But in general if you can issue the backups at
different times you will most likely get better performance for each and
less impact on the general users. This is due to the large amounts of I/O
(and to some extent CPU) that backups can induce. If you run multiple
backups at the same time you run the risk of maximizing your I/O bandwidth
and even causing network bottlenecks if done remotely. If you are going to
backup multiple dbs one right after the other it is best to do them in a
loop vs. setting specific times. That is because you never know if the
first is finished before the second one starts and so on. If you kick them
off after the previous completes you never have to worry about adjusting the
times as DB sizes change. Here is a sample of that:
-- Backup all user Databases --
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] ='DD_Pubs_Full')
EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
'C:\Data\Backups\DD_Pubs_Full.bak'
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] ='DD_Northwind_Full')
EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
'C:\Data\Backups\DD_Northwind_Full.bak'
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'DD_' + @.DBName + '_Full'
SET @.Name = @.DBName + N' Full Backup'
SET @.Descript = @.DBName + N' Full Backup - ' +
CONVERT(NVARCHAR(16),GETDATE(),112)
BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Andrew J. Kelly SQL MVP
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
> is it best to kick off the backups at the same time, i.e. 1am, or to space
> them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
> Any links or articles welcome.
> Thanks,
> MPM|||Andrew,
Thanks a lot for that excellent reply.
MPM
"Andrew J. Kelly" wrote:
> As always it depends. But in general if you can issue the backups at
> different times you will most likely get better performance for each and
> less impact on the general users. This is due to the large amounts of I/O
> (and to some extent CPU) that backups can induce. If you run multiple
> backups at the same time you run the risk of maximizing your I/O bandwidth
> and even causing network bottlenecks if done remotely. If you are going to
> backup multiple dbs one right after the other it is best to do them in a
> loop vs. setting specific times. That is because you never know if the
> first is finished before the second one starts and so on. If you kick them
> off after the previous completes you never have to worry about adjusting the
> times as DB sizes change. Here is a sample of that:
>
> -- Backup all user Databases --
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] => 'DD_Pubs_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
> 'C:\Data\Backups\DD_Pubs_Full.bak'
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] => 'DD_Northwind_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
> 'C:\Data\Backups\DD_Northwind_Full.bak'
>
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
> DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'DD_' + @.DBName + '_Full'
> SET @.Name = @.DBName + N' Full Backup'
> SET @.Descript = @.DBName + N' Full Backup - ' +
> CONVERT(NVARCHAR(16),GETDATE(),112)
> BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
> DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
> news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
> > is it best to kick off the backups at the same time, i.e. 1am, or to space
> > them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
> >
> > Any links or articles welcome.
> >
> > Thanks,
> > MPM
>
>

Multipe DBs on 1 Server backup schedule

is it best to kick off the backups at the same time, i.e. 1am, or to space
them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
Any links or articles welcome.
Thanks,
MPMAs always it depends. But in general if you can issue the backups at
different times you will most likely get better performance for each and
less impact on the general users. This is due to the large amounts of I/O
(and to some extent CPU) that backups can induce. If you run multiple
backups at the same time you run the risk of maximizing your I/O bandwidth
and even causing network bottlenecks if done remotely. If you are going to
backup multiple dbs one right after the other it is best to do them in a
loop vs. setting specific times. That is because you never know if the
first is finished before the second one starts and so on. If you kick them
off after the previous completes you never have to worry about adjusting the
times as DB sizes change. Here is a sample of that:
-- Backup all user Databases --
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Pubs_Full')
EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
'C:\Data\Backups\DD_Pubs_Full.bak'
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Northwind_Full')
EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
'C:\Data\Backups\DD_Northwind_Full.bak'
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'DD_' + @.DBName + '_Full'
SET @.Name = @.DBName + N' Full Backup'
SET @.Descript = @.DBName + N' Full Backup - ' +
CONVERT(NVARCHAR(16),GETDATE(),112)
BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Andrew J. Kelly SQL MVP
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
> is it best to kick off the backups at the same time, i.e. 1am, or to space
> them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
> Any links or articles welcome.
> Thanks,
> MPM|||Andrew,
Thanks a lot for that excellent reply.
MPM
"Andrew J. Kelly" wrote:

> As always it depends. But in general if you can issue the backups at
> different times you will most likely get better performance for each and
> less impact on the general users. This is due to the large amounts of I/O
> (and to some extent CPU) that backups can induce. If you run multiple
> backups at the same time you run the risk of maximizing your I/O bandwidth
> and even causing network bottlenecks if done remotely. If you are going t
o
> backup multiple dbs one right after the other it is best to do them in a
> loop vs. setting specific times. That is because you never know if the
> first is finished before the second one starts and so on. If you kick the
m
> off after the previous completes you never have to worry about adjusting t
he
> times as DB sizes change. Here is a sample of that:
>
> -- Backup all user Databases --
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Pubs_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
> 'C:\Data\Backups\DD_Pubs_Full.bak'
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Northwind_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
> 'C:\Data\Backups\DD_Northwind_Full.bak'
>
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
> DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMP
DB')
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'DD_' + @.DBName + '_Full'
> SET @.Name = @.DBName + N' Full Backup'
> SET @.Descript = @.DBName + N' Full Backup - ' +
> CONVERT(NVARCHAR(16),GETDATE(),112)
> BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name
,
> DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
> news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
>
>

Monday, March 26, 2012

Multiple instance name

Multiple instance name

Multiple instance name

Friday, March 23, 2012

Multiple FTP tasks that connect to same server at same time error

Multiple Forms

Wednesday, March 21, 2012

multiple fields from common lookup table

multiple distinct and time...

multiple distinct and time...

Multiple dimensions from single dimension table

Multiple dimensions from single dimension table

Monday, March 12, 2012

Multiple databases and SQL

Friday, March 9, 2012

Multiple Data Sources

Multiple data insertion with For clause

Multiple count(*) on same column Same query?

Monday, February 20, 2012

Multiple Ado Connections

Multipe DBs on 1 Server backup schedule

Multipe DBs on 1 Server backup schedule

Multipe DBs on 1 Server backup schedule

Multi-page report

Blog Archive

About Me