anyone use a 3rd party product to load balance SQL Server?

I'm working on a project needing a high-end large mission critical database
solution. The data is in 1 database. So SQL 2000 doesn't support load
balancing, and neither will 2005. That sucks. Oracle supposedly does load
balancing and I can't understand why even SQL Server 2005 won't do it. So
either I go with an 8+ CPU SQL Server or consider a 3rd party load balancer
that will somehow (?) make it work so that multiple SQL brains can balance
the load of 1 single database that is presumably shared on 1 set of disks.
I'm finding very little information on load balancing SQL Server when
searching the net. Is there a product like Legato, for example, (I haven't
tried it) that will somehow give me load balancing with SQL Server?HK wrote:
> I'm working on a project needing a high-end large mission critical
> database solution. The data is in 1 database. So SQL 2000 doesn't
> support load balancing, and neither will 2005. That sucks. Oracle
> supposedly does load balancing and I can't understand why even SQL
> Server 2005 won't do it. So either I go with an 8+ CPU SQL Server or
> consider a 3rd party load balancer that will somehow (?) make it work
> so that multiple SQL brains can balance the load of 1 single database
> that is presumably shared on 1 set of disks.
> I'm finding very little information on load balancing SQL Server when
> searching the net. Is there a product like Legato, for example, (I
> haven't tried it) that will somehow give me load balancing with SQL
> Server?
You may want to take a look at Federated Database Servers on SQL Server
2000/2005 if that architecture will work for you.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uzLN8u5wFHA.3180@.TK2MSFTNGP14.phx.gbl...
> HK wrote:
> > I'm working on a project needing a high-end large mission critical
> > database solution. The data is in 1 database. So SQL 2000 doesn't
> > support load balancing, and neither will 2005. That sucks. Oracle
> > supposedly does load balancing and I can't understand why even SQL
> > Server 2005 won't do it. So either I go with an 8+ CPU SQL Server or
> > consider a 3rd party load balancer that will somehow (?) make it work
> > so that multiple SQL brains can balance the load of 1 single database
> > that is presumably shared on 1 set of disks.
> >
> > I'm finding very little information on load balancing SQL Server when
> > searching the net. Is there a product like Legato, for example, (I
> > haven't tried it) that will somehow give me load balancing with SQL
> > Server?
> You may want to take a look at Federated Database Servers on SQL Server
> 2000/2005 if that architecture will work for you.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Creating the partitioned views sounds very challenging for an already-in-use
DB that needs to be cut over in the middle of the night. Plus ongoing
maintenance. Maybe I'm missing the boat, but that's why I'd want to stay
away from federated databases.

ANYONE PLEASE - Local Synchronization Between SQL Mobile and Local SQL Database

Hi Everyone

I am at the stage of architecting my solution

My goal is to develop the system on a windows application and pda

There is a central server which will create a publication called inventory

The laptops which host the windows application will be subscribers to the central server using merge replication

The client now wants the PDA using SQL Mobile to synchronize with the local subscirber database on the laptop using active sync. They dont want to do it via WIFI to the IIS Server at the central server

I have been reading for days and I am still unsure whether this is possible to do.

I know Appforge provide a conduit for palm to access synchronization but not local sql databases

I would appreciate your help immensley

Can anyone please help me

|||

If you build your mobile application using VS2005 and SQL Mobile, you can use merge replication between your server-side publication and SQL Mobile. The SQL Mobile Books OnLine are the first place to start - read the section on merge replication.

-Darren Shaffer

|||

Hi Darren

Yes I got my sql mobile to synchronize successfully with the local desktop sql database called "Inventory"

However I have a new problem

I would like to have the desktop replicating with the server for database "Inventory"

I also got this to work seperately

I would now want the SQL mobile database to replicate with the local desktop database "Inventory" which inturn will replicate with the server copy called "Inventory"

I would like to have both replication scenarios joined together. I know that most people will say get the SQL mobile to talk directly to the server but this is the most expensive scenario I believe

Is this possible?

Thankyou for your earlier post

Touraj

|||

Touraj,

Sorry for not reading your post more closely - I see what you are trying to do now. You want to have a 3 tier merge replication going on. Yes, this is possible, but the database on the laptop is going to have to be SQL Server 2005, not SQL Express. The way it would have to work is this:

The laptop would have to have IIS installed and the SQL Mobile server tools. As far as the mobile app is concerned, the laptop is the publisher. On the laptop, you have a subscription to the central server in a replication relationship. You would turn around and create a publication on the laptop with the same articles that are in the subscription to the central server. You would need to be extremely careful with uniqueness in this situation - I would avoid identity columns and use GUIDs for everything in this app domain.

Is there any chance that both the laptops and the PDAs can be subscribers to the central server? That would be a much easier implementation, but it is possible to do what you suggested with the extra tier.

-Darren

|||

Hi Darren

Thankyou again for your kind assistance

Yes you are right. It would be easier and 'cheaper' to get the PDA to synch with the central server via the servers IIS process

I am tossing up between a i-Mate and iPaq 4700. I would be interested to know if you have a preference over the two

Thanks from sunny Australia.

Touraj

|||

I think both i-Mate and HP make great devices - I have not had any trouble with SQL Mobile on my JasJar or on a variety of iPaqs. My WM5 JasJar seems a little sluggish overall compared to a Dell Axim X50v (WM5), which others have noted as well. Not slow, just not instant to respond to input. I have not had specific experience with the 4700. There are some public newsgroups (microsoft.public.pocketpc.developer) where people tend to talk more about specific devices and you could use Google Advanced Groups search to get previous opinions on the 4700.

-Darren

anyone can give solution for this problem

2004-08-21 11:52:38.64 spid52 Error: 0, Severity: 19,
State: 0
2004-08-21 11:52:38.64 spid52 language_exec: Process 52
generated an access violation. SQL Server is terminating
this process..
2004-08-21 11:56:12.93 spid52 Using 'sqlimage.dll'
version '4.0.5'
Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQL00062.dmp
2004-08-21 11:56:12.94 spid52 Error: 0, Severity: 19,
State: 0
2004-08-21 11:56:12.94 spid52 SqlDumpExceptionHandler:
Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
process..
************************************************** *********
********************
*
* BEGIN STACK DUMP:
* 08/21/04 11:56:12 spid 52
*
* Exception Address = 0055AB2F
(COpArg::DeriveNormalizedGroupProperties(class CTreeHandle
*) + 0000001E Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 4088 bytes -
* SELECT glmedium.module_id,
glmedium.vouchertype,
* glmedium.vouchernumber,
glmedium.voucherdate,
* glmedium.accountcode,
glmedium.amountdebit,
* glmedium.amountcredit,
glmedium.jobid, cstcon
* tract.ContractName,
glaccounts.AccountName , gljobwisep
* rofit.groupcode ,
glmedium.accountremarks,
glmedium.partycode,
* arcustomer.customername,
contractvalue,provisionamt FROM glm
* edium, cstcontract,
glaccounts, gljobwisep
* rofit, arcustomer WHERE (
glmedium.company_id = cstcontract.Co
* mpany_id ) and ( glmedium.jobid =
cstcontract.ContractNo ) a
* nd ( glmedium.company_id =
glaccounts.Company_id ) and
* ( glmedium.accountcode =
glaccounts.AccountCode ) AND VOUCHER
* TYPE NOT IN ('GI','SR','DN','DJ') and
glmedium.accountcode = gljobwi
* seprofit.accountcode and
glmedium.company_id = arcustomer.company_i
* d and partycode =
arcustomer.customercode and
glmedium.company_i
* d = '05' and
glmedium.voucherdate >= '1-JAN-2000' and
glmedium.
* voucherdate <= '31-DEC-2004' and
glmedium.jobid >= '0' and
glmed
* ium.jobid <= 'ZZZZZZZZ' UNION SELECT
glmedium.module_id,
* glmedium.vouchertype,
glmedium.vouchernumber,
* glmedium.voucherdate,
glmedium.accountcode,
* glmedium.amountdebit, 0,
glmedium.jobid,
* cstcontract.ContractName,
glaccounts.AccountName
* , 'ZI' ,
glmedium.accountremarks,
glmedium.partycode,
* arcustomer.customername,
contractvalue,provisionamt FROM glmedi
* um, cstcontract, glaccounts,
arcustomer
* WHERE ( glmedium.company_id =
cstcontract.Company_id ) and
* ( glmedium.jobid = cstcontract.ContractNo )
and ( glmed
* ium.company_id = glaccounts.Company_id )
and ( glmedium.acco
* untcode = glaccounts.Account
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B19FFF
0071a000
* ntdll 77F80000 77FFCFFF
0007d000
* KERNEL32 7C570000 7C627FFF
000b8000
* ADVAPI32 7C2D0000 7C331FFF
00062000
* RPCRT4 77D30000 77DA0FFF
00071000
* USER32 77E10000 77E74FFF
00065000
* GDI32 77F40000 77F7DFFF
0003e000
* OPENDS60 41060000 41065FFF
00006000
* MSVCRT 78000000 78044FFF
00045000
* UMS 41070000 4107CFFF
0000d000
* SQLSORT 42AE0000 42B6FFFF
00090000
* MSVCIRT 780A0000 780B1FFF
00012000
* sqlevn70 41080000 41086FFF
00007000
* NETAPI32 75170000 751BEFFF
0004f000
* Secur32 7C340000 7C34EFFF
0000f000
* NTDSAPI 77BF0000 77C00FFF
00011000
* DNSAPI 77980000 779A3FFF
00024000
* WSOCK32 75050000 75057FFF
00008000
* WS2_32 75030000 75043FFF
00014000
* WS2HELP 75020000 75027FFF
00008000
* WLDAP32 77950000 77979FFF
0002a000
* NETRAP 751C0000 751C5FFF
00006000
* SAMLIB 75150000 7515EFFF
0000f000
* wmi 76110000 76113FFF
00004000
* SSNETLIB 42CF0000 42D05FFF
00016000
* SSNMPN70 410D0000 410D5FFF
00006000
* security 75500000 75503FFF
00004000
* crypt32 7C740000 7C7C6FFF
00087000
* MSASN1 77430000 7743FFFF
00010000
* userenv 7C0F0000 7C150FFF
00061000
* rnr20 782C0000 782CBFFF
0000c000
* iphlpapi 77340000 77352FFF
00013000
* ICMP 77520000 77524FFF
00005000
* MPRAPI 77320000 77336FFF
00017000
* OLE32 77A50000 77B3EFFF
000ef000
* OLEAUT32 779B0000 77A4AFFF
0009b000
* ACTIVEDS 773B0000 773DEFFF
0002f000
* ADSLDPC 77380000 773A2FFF
00023000
* RTUTILS 77830000 7783DFFF
0000e000
* SETUPAPI 77880000 7790DFFF
0008e000
* RASAPI32 774E0000 77512FFF
00033000
* RASMAN 774C0000 774D0FFF
00011000
* TAPI32 77530000 77551FFF
00022000
* COMCTL32 71710000 71793FFF
00084000
* SHLWAPI 70A70000 70AD4FFF
00065000
* DHCPCSVC 77360000 77378FFF
00019000
* winrnr 777E0000 777E7FFF
00008000
* rasadhlp 777F0000 777F4FFF
00005000
* msafd 74FD0000 74FEDFFF
0001e000
* wshtcpip 75010000 75016FFF
00007000
* SSmsLPCn 42CD0000 42CD6FFF
00007000
* SQLFTQRY 41020000 4103CFFF
0001d000
* CLBCATQ 775A0000 7762FFFF
00090000
* SQLOLEDB 75370000 753E7FFF
00078000
* MSDART 2AD60000 2AD82FFF
00023000
* VERSION 77820000 77826FFF
00007000
* LZ32 759B0000 759B5FFF
00006000
* comdlg32 76B30000 76B6DFFF
0003e000
* SHELL32 782F0000 78534FFF
00245000
* MSDATL3 2AD90000 2ADA5FFF
00016000
* oledb32 2B0B0000 2B11EFFF
0006f000
* OLEDB32R 2B120000 2B130FFF
00011000
* rsabase 7CA00000 7CA22FFF
00023000
* xpstar 410F0000 41133FFF
00044000
* SQLUNIRL 41090000 410BCFFF
0002d000
* WINSPOOL 77800000 7781DFFF
0001e000
* MPR 76620000 7662FFFF
00010000
* SQLRESLD 42AC0000 42AC6FFF
00007000
* SQLSVC 42C40000 42C56FFF
00017000
* ODBC32 2B150000 2B184FFF
00035000
* odbcbcp 41150000 41156FFF
00007000
* W95SCM 41140000 4114BFFF
0000c000
* NDDEAPI 769A0000 769A6FFF
00007000
* odbcint 2B290000 2B2A5FFF
00016000
* clusapi 73930000 7393FFFF
00010000
* resutils 689D0000 689DCFFF
0000d000
* SQLSVC 43970000 43975FFF
00006000
* xpstar 439E0000 439EBFFF
0000c000
* msv1_0 2B600000 2B620FFF
00021000
* srchadm 60000000 60042FFF
00043000
* mssws 2B850000 2B858FFF
00009000
* athprxy 2BBB0000 2BBB7FFF
00008000
* DBGHELP 2BCF0000 2BD02FFF
00013000
* msdbi 6BE90000 6BEABFFF
0001c000
* sqlimage 4A400000 4A40CFFF
0000d000
*
* Edi: 00000000:
* Esi: 1D9F6B50: 00982848 00000002 209B8030
1DA15268 1DA12690 00000000
* Eax: 00000000:
* Ebx: 00000000:
* Ecx: 00000000:
* Edx: 2A88C228: 00000124 20940168 2090D5D0
208FE358 1D9B9838 20940168
* Eip: 0055AB2F: 50FF038B D8F74810 8940C01B
1375EC45 5FF44D8B 5B5EC38B
* Ebp: 2A88BD5C: 2A88BED0 0055A7BB 2A88BD74
00000000 1D9B9A48 00000000
* SegCs: 0000001B:
* EFlags: 00010246: 0061006A 00610076 0063005C
0061006C 00730073 00730065
* Esp: 2A88BD1C: 00000000 1D9F6B50 00000000
2A88BD38 0040204E 209B7400
* SegSs: 00000023:
************************************************** *********
********************
Short Stack Dump
0055AB2F Module(sqlservr+0015AB2F)
(COpArg::DeriveNormalizedGroupProperties(class CTreeHandle
*)+0000001E)
0055A7BB Module(sqlservr+0015A7BB)
(COptExpr::DeriveGroupProperties(unsigned long)+000000B3)
0055A765 Module(sqlservr+0015A765)
(COptExpr::DeriveGroupProperties(unsigned long)+0000005D)
0048BEA2 Module(sqlservr+0008BEA2)
(CImpRuleBaseJoinToIdxLookup::BuildSubstitutes(cla ss
COptExpr *,class CRuleContext *,class CRuleReturn *)
+00000ECC)
00589104 Module(sqlservr+00189104)
(CTask_ApplyRule::Perform(int)+00000268)
0058C939 Module(sqlservr+0018C939) (CMemo::ExecuteTasks
(class COptTask *,int,int)+0000014D)
0058CF2D Module(sqlservr+0018CF2D) (CMemo::OptimizeQuery
(class CQuery *,class COptExpr *,double *,int,int,struct
s_OptimPlans *)+0000051A)
0058CADF Module(sqlservr+0018CADF)
(COptContext::PexprSearchPlan(class COptExpr *)+00000155)
0055E100 Module(sqlservr+0015E100)
(COptContext::PcxteOptimizeQuery(class COptExpr *,class
DRgCId *)+00000B7A)
0055FFBB Module(sqlservr+0015FFBB) (CQuery::Optimize(void)
+00000416)
0055FD54 Module(sqlservr+0015FD54) (CQuery::Optimize
(unsigned long)+00000030)
005642C6 Module(sqlservr+001642C6) (CCvtTree::PqryFromTree
(class TREE *,class IMemObj *,class CRangeCollection
*,unsigned long,class CCompPlan *)+000002C4)
00564019 Module(sqlservr+00164019) (BuildQueryFromTree
(class TREE *,class IMemObj *,class IMemObj *,class
IQueryObj * *,class CRangeCollection *,unsigned long,class
CCompPlan *)+00000046)
00563F78 Module(sqlservr+00163F78) (CStmtQuery::InitQuery
(class CAlgStmt *,class CCompPlan *,unsigned long)
+0000014B)
0049DA48 Module(sqlservr+0009DA48) (CStmtSelect::Init
(class CAlgStmt *,class CCompPlan *,class IBrowseMode *)
+00000091)
00447078 Module(sqlservr+00047078) (CCompPlan::FCompileStep
(class CAlgStmt *,class CStatement * *)+00000AE7)
004510FE Module(sqlservr+000510FE) (CProchdr::FCompile
(class CCompPlan *,class CParamExchange *)+00000D15)
00415080 Module(sqlservr+00015080) (CSQLSource::FTransform
(class CParamExchange *)+0000037C)
004592CE Module(sqlservr+000592CE) (CSQLStrings::FTransform
(class CParamExchange *)+000001A8)
0041534F Module(sqlservr+0001534F) (CSQLSource::Execute
(class CParamExchange *)+00000176)
00459A54 Module(sqlservr+00059A54) (language_exec(struct
srv_proc *)+000003C8)
004175D8 Module(sqlservr+000175D8) (process_commands
(struct srv_proc *)+000000E0)
410735D0 Module(UMS+000035D0) (ProcessWorkRequests(class
UmsWorkQueue *)+00000264)
4107382C Module(UMS+0000382C) (ThreadStartRoutine(void *)
+000000BC)
78008454 Module(MSVCRT+00008454) (_endthread+000000C1)
7C57438B Module(KERNEL32+0000438B) (TlsSetValue+000000F0)
2004-08-21 11:56:16.43 spid52 Error: 0, Severity: 19,
State: 0
2004-08-21 11:56:16.43 spid52 language_exec: Process 52
generated an access violation. SQL Server is terminating
this process..
You may want to contact PSS with details on how this occured:
http://support.microsoft.com/default...s;Prodoffer41a
( If this is due to a bug, you may get your money refunded )
Anith

Any way to use files from separate directories in mgmt studio?

Is there any way to add files from 2 different directories and have them retain their directory identity within the solution? When I attempt to add a file from another directory, mgmt studio makes a copy of the file in the default directory for the solution, which is not what I want. I want to be able to edit the script and have it stay (and update) within the directory from which it came. The only way I see now is to have multiple solutions, but since mgmt studio appears to only allow me to have one solution open at a time, this doesn't work either. Any suggestions?How are you performing this change, DTS or script?

Any way to programmatically deploy an SSAS solution?

Hi all,

I'm trying to add a nightly build/deploy process for my SSAS solution. Basically - to take my AS solution from source control, deploy it to our dev server, and process just a few partitions.

What's the best way to do this? Looking at the solution folder, the .dsv, .dim, .cube, .ds files are all xml-based, which is good, but they don't seem like XML/A. (Seem very similar to a serialized version of AMO objects, but not quite exactly)

The only way I'd guess to do this is to manually load the individual xml files into an object model, and then map them to the AMO objects (since they're pretty similar..), and deploy those to the server. Is there a more efficient way to directly de-serialize the .dim,.cube, etc files into the AMO structures and deploy?

Take a look at the Deployment Wizard which you can run from Start... Programs... Microsoft SQL Server 2005... Analysis Services... Deployment Wizard.

Also see more info including command line switches here:

http://msdn2.microsoft.com/en-us/library/ms174817.aspx

|||

Furmangg, perfect tip on using the deployment wizard. I think this will suit our needs very well.

I was going to follow up w/ the correct method of compiling - I tried msbuild unsuccessfully, but I found this great blog post by Thomas Kejser -

http://schastar.spaces.live.com/blog/cns!12BCB785A5D8B3D4!148.entry?beid=cns!12BCB785A5D8B3D4!148&d=1&wa=wsignin1.0

Sweet!

Any way to kill zombie locks by commandline?

hi
we have some troubles with a object that kills our SQL2000SP4. CPU spikes to
100% and stay there. We are searching for a solution, but currently don't
have one! So it comes to situation where a object named
"company.dbo.fn_example" have more then one ProcessID. If this occour -
"all" other processes are zombies and i only know how to kill them by hand.
this makes me *stressed*, while i must kill 10 every 20 minutes.
is there any way to kill the object for e.g. with all processes inside - by
command line?
Regards
MarcSounds like a very poorly written function that is monopolizing the CPU's.
Try setting MAXDOP to 1 and see if it stays contained to just one CPU. But
I would look at other ways to optimize the statements that use this
function.
--
Andrew J. Kelly SQL MVP
"Marc Bauer" <marc.bau@.gmx.net> wrote in message
news:%23IxhtuvLGHA.2300@.TK2MSFTNGP15.phx.gbl...
> hi
> we have some troubles with a object that kills our SQL2000SP4. CPU spikes
> to 100% and stay there. We are searching for a solution, but currently
> don't have one! So it comes to situation where a object named
> "company.dbo.fn_example" have more then one ProcessID. If this occour -
> "all" other processes are zombies and i only know how to kill them by
> hand. this makes me *stressed*, while i must kill 10 every 20 minutes.
> is there any way to kill the object for e.g. with all processes inside -
> by command line?
>
> Regards
> Marc
>|||hi
> Sounds like a very poorly written function that is monopolizing the CPU's.
> Try setting MAXDOP to 1 and see if it stays contained to just one CPU.
> But I would look at other ways to optimize the statements that use this
> function.
our database specialist, reviewed the function and said - that this one is
correct and should work very fast. but sometimes - if this function gets
executed very very often it comes to something like a deadlock situation.
then all tables are locked... i have had a look to the function today, and
there is everywhere in the selects a with(nolock) used. so it cannot be a
read deadlock.
so - today - we don't know what's wrong. maybe a bug in SQL and we need to
find a workaround... this should be partly a con job with a kill - until we
found a solution.
Regards
Marc|||Can you post the function?
--
Andrew J. Kelly SQL MVP
"Marc Bauer" <marc.bau@.gmx.net> wrote in message
news:unyGffyLGHA.420@.tk2msftngp13.phx.gbl...
> hi
>> Sounds like a very poorly written function that is monopolizing the
>> CPU's. Try setting MAXDOP to 1 and see if it stays contained to just one
>> CPU. But I would look at other ways to optimize the statements that use
>> this function.
> our database specialist, reviewed the function and said - that this one is
> correct and should work very fast. but sometimes - if this function gets
> executed very very often it comes to something like a deadlock situation.
> then all tables are locked... i have had a look to the function today, and
> there is everywhere in the selects a with(nolock) used. so it cannot be a
> read deadlock.
> so - today - we don't know what's wrong. maybe a bug in SQL and we need to
> find a workaround... this should be partly a con job with a kill - until
> we found a solution.
>
> Regards
> Marc
>|||On Sat, 11 Feb 2006 17:26:09 +0100, "Marc Bauer" <marc.bau@.gmx.net>
wrote:
> but sometimes - if this function gets
>executed very very often it comes to something like a deadlock situation.
>then all tables are locked...
What do you mean, "very very often"?
Does the calling code happen to use #temp tables?
If you post source code, please also post what you can of the
invocation.
J.|||we found a endless loop in the function... :-(
Marc

Any structure on Sql Server like WITH ... SELECT structure on DB2

Hi,
I'm using DB2 UDB 7.2.
Also I'm doing some tests on SQL Server 2000 for some statements to
use efectively.
I didn't find any solution on Sql Server about WITH ... SELECT
structure of DB2.

Is there any basic structure on Sql Server like WITH ... SELECT
structure?

A Sample statement for WITH ... SELECT on DB2 like below
WITH
totals (code, amount)
AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code)
SELECT
code, SUM(amount)
FROM totals
GROUP BY code

......................

Note: 'creating temp table and using it' maybe a solution.
However i need to know the definition of the result set of Union
clause. I don't want to use this way.
CREATE TABLE #totals (codechar(10), amount dec(15))
GO
INSERT INTO #totals
SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code
GO
SELECT code, sum(amount) FROM #totals GROUP BY code
GO

Any help would be appreciated
Thanks in advance
MemduhIn SQL Server, you can use SELECT ... INTO to create a table using a SELECT
query as the source for schema and data. Untested example:

SELECT
code,
SUM(amount)
INTO #totals
FROM trans1
GROUP BY code
UNION ALL
SELECT
code,
SUM(amount)
FROM trans2
GROUP BY code

SELECT
code,
SUM(amount)
FROM #totals
GROUP BY code

You can also produce the result using a derived table instead of a temp
table:

SELECT
code,
SUM(amount)
FROM
(
SELECT
code,
SUM(amount) AS amount
FROM trans1
GROUP BY code
UNION ALL
SELECT
code,
SUM(amount)
FROM trans2
GROUP BY code
) AS totals
GROUP BY code

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Memduh Durmaz" <mdurmaz@.derece.com.tr> wrote in message
news:e0c9cfd5.0408291350.31b8585a@.posting.google.c om...
> Hi,
> I'm using DB2 UDB 7.2.
> Also I'm doing some tests on SQL Server 2000 for some statements to
> use efectively.
> I didn't find any solution on Sql Server about WITH ... SELECT
> structure of DB2.
> Is there any basic structure on Sql Server like WITH ... SELECT
> structure?
> A Sample statement for WITH ... SELECT on DB2 like below
> WITH
> totals (code, amount)
> AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code)
> SELECT
> code, SUM(amount)
> FROM totals
> GROUP BY code
> .....................
> Note: 'creating temp table and using it' maybe a solution.
> However i need to know the definition of the result set of Union
> clause. I don't want to use this way.
> CREATE TABLE #totals (code char(10), amount dec(15))
> GO
> INSERT INTO #totals
> SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code
> GO
> SELECT code, sum(amount) FROM #totals GROUP BY code
> GO
> Any help would be appreciated
> Thanks in advance
> Memduh|||On 29 Aug 2004 14:50:25 -0700, Memduh Durmaz wrote:

> Is there any basic structure on Sql Server like WITH ... SELECT
> structure?
> A Sample statement for WITH ... SELECT on DB2 like below
> WITH
> totals (code, amount)
> AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code)
> SELECT
> code, SUM(amount)
> FROM totals
> GROUP BY code

You can always use a derived table:

SELECT code, SUM(amount)
FROM (
SELECT code, SUM(amount) FROM trans1 GROUP BY CODE
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY CODE
) AS TOTALS
GROUP BY CODE

(the "AS" in "AS TOTALS" is optional, and is often omitted.)|||SQL-Server 2005 will support Common Table Expressions (the WITH ...
SELECT stuff). If you don't want to wait a year, then you would have to
work around in SQL-Server 2000, or get hold of a beta-version.

HTH,
Gert-Jan

Memduh Durmaz wrote:
> Hi,
> I'm using DB2 UDB 7.2.
> Also I'm doing some tests on SQL Server 2000 for some statements to
> use efectively.
> I didn't find any solution on Sql Server about WITH ... SELECT
> structure of DB2.
> Is there any basic structure on Sql Server like WITH ... SELECT
> structure?
> A Sample statement for WITH ... SELECT on DB2 like below
> WITH
> totals (code, amount)
> AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code)
> SELECT
> code, SUM(amount)
> FROM totals
> GROUP BY code
> .....................
> Note: 'creating temp table and using it' maybe a solution.
> However i need to know the definition of the result set of Union
> clause. I don't want to use this way.
> CREATE TABLE #totals (code char(10), amount dec(15))
> GO
> INSERT INTO #totals
> SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code
> GO
> SELECT code, sum(amount) FROM #totals GROUP BY code
> GO
> Any help would be appreciated
> Thanks in advance
> Memduh

--
(Please reply only to the newsgroup)

Any solution? Cannot initialize the data source object of OLE DB provider "microsoft.jet.ol

This is a problem that never get solved, sometime I can use other way to avoid it, but havn't found a solution yet, i hope I can get some more idea here.

I am using SQL 2005, when I run

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

I get

Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

when I try to compile a SP with that statement in it, I get the same error, like

create stored procedure test

as begin

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

end

so it seems the error may not relate to the real file, since at the compile stage, it should not check the real file?

On my live db, after I restart the SQL service, the statement will work, after a while, one or several days, I get the same error again. I can not restart my live db quite often for sure, so now I have another backup db server, I need run the statement on the backup server and then read the data from there.

I have the same problem at two places, both use SQL 2005.

So far there are three questions

1, why it works after restart, but only last for a while? something about memory? since the backup db seldom need restart and work fine after many days.

2, why it gives error in compile stage?

3, why two dbs in different Enviroment has the same problem

The most answer I have gathered so far is permission issue, true I got similar error if the import file is located in a place which SQL has no right to access. But in this case, it should not be.

Any other idea or suggestion?

thanks

I have the same puzzle like you.My data source is access.Do you have some other solution now.Give me a help.

|||

When I remove the password from the access file ,the problem solved.But the file must be set a password.What I can do?

Any solution? Cannot initialize the data source object of OLE DB provider "microsoft.jet.ol

This is a problem that never get solved, sometime I can use other way to avoid it, but havn't found a solution yet, i hope I can get some more idea here.

I am using SQL 2005, when I run

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

I get

Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

when I try to compile a SP with that statement in it, I get the same error, like

create stored procedure test

as begin

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

end

so it seems the error may not relate to the real file, since at the compile stage, it should not check the real file?

On my live db, after I restart the SQL service, the statement will work, after a while, one or several days, I get the same error again. I can not restart my live db quite often for sure, so now I have another backup db server, I need run the statement on the backup server and then read the data from there.

I have the same problem at two places, both use SQL 2005.

So far there are three questions

1, why it works after restart, but only last for a while? something about memory? since the backup db seldom need restart and work fine after many days.

2, why it gives error in compile stage?

3, why two dbs in different Enviroment has the same problem

The most answer I have gathered so far is permission issue, true I got similar error if the import file is located in a place which SQL has no right to access. But in this case, it should not be.

Any other idea or suggestion?

thanks

I have the same puzzle like you.My data source is access.Do you have some other solution now.Give me a help.

|||

When I remove the password from the access file ,the problem solved.But the file must be set a password.What I can do?

Any solution for that?

I've got a .dts which load data to several Sql2k servers by Copy SQL Server Objects Task. I'd like to add another task for a Sql25k but I can't because of it's requesting SMO.

It seems that such task need DMO. I know that there are other ways for do such stuff in that DTS but I wonder if there is any trick or shortcut for to avoid this barrier.

How odd, if you use Dts Import/Export Wizard from EM sql25k is accepted.

any scalable solution?

hi,
I have a table which stores some user details like email, name and Flag.
Now currently we have a stored procedure which runs on the table and selects
all the records from this table where flag=0 processes these records and
sets the flag to 1.
This process is working fine.
But now we want to deploy this service on multiple servers meaning this
procedure should be called on say two different servers. The procedures will
now be modified to say select first top 100 records and process them and
then update the flag to 1. simultaneously if the same proc is called from
another server then the 100 records selected by the first server should not
be selected by the proc called by the second server.
I am sorry if i m not clear.
any pointers will be helpful..
thanks for the replies.
jyo.Maybe one solution is to use a three valued logic.
Thats when you fetch rows for processing update them to a differenct status
code.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:uktaSMlGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> hi,
> I have a table which stores some user details like email, name and Flag.
> Now currently we have a stored procedure which runs on the table and
> selects
> all the records from this table where flag=0 processes these records and
> sets the flag to 1.
> This process is working fine.
> But now we want to deploy this service on multiple servers meaning this
> procedure should be called on say two different servers. The procedures
> will
> now be modified to say select first top 100 records and process them and
> then update the flag to 1. simultaneously if the same proc is called from
> another server then the 100 records selected by the first server should
> not
> be selected by the proc called by the second server.
> I am sorry if i m not clear.
> any pointers will be helpful..
> thanks for the replies.
> jyo.
>|||right this is what i m planning to do ... but in the time interval between
the select and update
in the first procedure call, if the second procedure call is made and the
same records may get selected.. and if locking is done then one of them one
of the call is selected as the deadlock victim...
I am looking for something like a rowlock... which even would not allow to
read the locked rows.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:e9krnQlGFHA.3472@.TK2MSFTNGP09.phx.gbl...
> Maybe one solution is to use a three valued logic.
> Thats when you fetch rows for processing update them to a differenct
status
> code.
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "harshal mistry" <harshal_in@.hotmail.com> wrote in message
> news:uktaSMlGFHA.2732@.TK2MSFTNGP15.phx.gbl...
and
from
>|||You can select using UpdLock table hint. This means you are trying to select
with the intent to update it later. So it will try to acquire IX (intent
exclusive) lock and later it will update it to X (exclusive) as and when u
you do the update. This is the way you can avoid deadlocks.
hth,
--
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:em71SflGFHA.2748@.tk2msftngp13.phx.gbl...
> right this is what i m planning to do ... but in the time interval between
> the select and update
> in the first procedure call, if the second procedure call is made and the
> same records may get selected.. and if locking is done then one of them
one
> of the call is selected as the deadlock victim...
> I am looking for something like a rowlock... which even would not allow to
> read the locked rows.
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:e9krnQlGFHA.3472@.TK2MSFTNGP09.phx.gbl...
> status
Flag.
> and
this
procedures
and
> from
should
>|||even after using updlock table hint it deadlocks the other process ...
harshal.
"avnrao" <avn@.newsgroups.com> wrote in message
news:uAQv6mlGFHA.3964@.TK2MSFTNGP14.phx.gbl...
> You can select using UpdLock table hint. This means you are trying to
select
> with the intent to update it later. So it will try to acquire IX (intent
> exclusive) lock and later it will update it to X (exclusive) as and when u
> you do the update. This is the way you can avoid deadlocks.
> hth,
> --
> Av.
> http://dotnetjunkies.com/WebLog/avnrao
> http://www28.brinkster.com/avdotnet
>
> "harshal mistry" <harshal_in@.hotmail.com> wrote in message
> news:em71SflGFHA.2748@.tk2msftngp13.phx.gbl...
between
the
> one
to
> Flag.
records
> this
> procedures
> and
> should
>|||can you check syslockinfo and sysprocesses table ifnormation..and find out
which one is causing the deadlock.
AFAIK, updlock should avoid the deadlock.
can you post your code..
if you want to resolve deadlock..you can use PSSDiag tool
http://www.microsoft.com/downloads/...&displaylang=en
hth,
--
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:uyUX75lGFHA.1528@.TK2MSFTNGP09.phx.gbl...
> even after using updlock table hint it deadlocks the other process ...
> harshal.
>
> "avnrao" <avn@.newsgroups.com> wrote in message
> news:uAQv6mlGFHA.3964@.TK2MSFTNGP14.phx.gbl...
> select
u
> between
> the
them
allow
> to
and
> records
them
called
>|||Try reading the rows, with a lock ( like updlock or xlock), but also use
readpast, which means to NOT wait on locked rows ( by the other process...
DO this in a transaction which includes the updating of the rows...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:uktaSMlGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> hi,
> I have a table which stores some user details like email, name and Flag.
> Now currently we have a stored procedure which runs on the table and
> selects
> all the records from this table where flag=0 processes these records and
> sets the flag to 1.
> This process is working fine.
> But now we want to deploy this service on multiple servers meaning this
> procedure should be called on say two different servers. The procedures
> will
> now be modified to say select first top 100 records and process them and
> then update the flag to 1. simultaneously if the same proc is called from
> another server then the 100 records selected by the first server should
> not
> be selected by the proc called by the second server.
> I am sorry if i m not clear.
> any pointers will be helpful..
> thanks for the replies.
> jyo.
>|||"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:uktaSMlGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> hi,
> I have a table which stores some user details like email, name and Flag.
> Now currently we have a stored procedure which runs on the table and
selects
> all the records from this table where flag=0 processes these records and
> sets the flag to 1.
> This process is working fine.
> But now we want to deploy this service on multiple servers meaning this
> procedure should be called on say two different servers. The procedures
will
> now be modified to say select first top 100 records and process them and
> then update the flag to 1. simultaneously if the same proc is called from
> another server then the 100 records selected by the first server should
not
> be selected by the proc called by the second server.
> I am sorry if i m not clear.
> any pointers will be helpful..
> thanks for the replies.
> jyo.
Maybe something like:
Step 1: Update Flag to an in use value depending on the server (Say flag
= -1 for Server 1, flag = -2 for server 2, etc.)
Step 2: Do the processing on records where flag = (-1) for Server 1
Step 3: Update Flag to 1
The key is to mark the records you want first.
In Step 1 only select from the pool where flag = 0.
The way you describe the problem sounds to me as if you may be using a
cursor. If so, you may want to rethink your solution to a something more
set based. If you aren't using a cursor, ignore this.
Good Luck,
Jim.

any differents and advices about connection manager

first set up data source from solution explorer,then new connection from data source from connection managers

directly new ole db connection or ado.net connection etc by right clicking from connection managers

At the end doesn't matter how you created it; the connection manager will work exactly the same. The only diffrence is that with the first approach you only have to create a connection manager once and then can use it in multiple packages.

Rafael Salas

Any available solution do creat 3 databases correspondence?

Hi,

we have three dabases:

1. Two localDatabases in two offices, Main Office & Branch Office, each office in a diffrent city!!

2. One remoteDatabes exist in on the internet server.

all of it are with the same exact architecture, we are planning to upload and download to & from remoteDatabase to get the same data in all of them.

any available solution to do this with SQL Server 2005 ?

You may wish to investigate the use of Replication (see Books Online).

A couple of other options include: database mirroring and log shipping.

My guess (based on scant information) is that one of the Replication schemes is most likely what you seek.

|||Thanks Arnie,,

Any advice on testing Data Warehouse solution?

We are looking at deploying a data warehouse solution soon that has been
being developed for a few months. The development team has been working on
developing this for the company. Soon they will be turning this over to the
DBA group to do some level of integration testing with the rest of our
production data.
What guidelines are there for testing data warehouse solutions?
What types of things should I be looking for other than user sign off that
the data looks good?Function test of Backup/restore of full data size
Performance/Load testing of full data size
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Doug Needham" <dneedham@.cfsloans.com> wrote in message
news:eqTaIH3xDHA.2148@.TK2MSFTNGP12.phx.gbl...

quote:

---

> We are looking at deploying a data warehouse solution soon that has been
> being developed for a few months. The development team has been working on
> developing this for the company. Soon they will be turning this over to

---

the

quote:

---

> DBA group to do some level of integration testing with the rest of our
> production data.
> What guidelines are there for testing data warehouse solutions?
> What types of things should I be looking for other than user sign off that
> the data looks good?
>
>

---

Anti-virus Exclusion list

Currently, I install an Anti-virus solution on MS SQL Server. It protects windows OS. In order to speed up the SQL performance, I dont want this Anti-virus to scan my SQL database. I want to know what's the exclusion list for the Anti-virus application. Thanks,

Hi,

all file extension that are used for SQL Server, by default this is mdf,ndf,ldf but this can be changed by the user, so you will need to have a look at your database. The logfiles .log can be dismissed, as there is no heavy load on them.

The information about the datafiles is stored in the sysfiles table, therefore you will have to query this table for each database in order to get the extensions. I prepared something for you which should help you to identity the needed extensions.

CREATE Table #Extensions

(

Extension VARCHAR(10)

)

INSERT INTO #Extensions

EXEC sp_msforeachdb 'Select REVERSE(LEFT(REVERSE(filename),CHARINDEX(CHAR(46),REVERSE(filename))-1)) from sysfiles'

SELECT DISTINCT Extension FROM #Extensions

If you data is stored in specific folders, you could also try to exclude this folder instead of using the file extension filter (if you application is able to do this)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

All anti virus server have Exclusion List which means it won't scan those files(Extension) listed , you have to enter .mdf,.ldf & .ndf to its Extention Exclusion List and then it won't scan your Database. http://www.nus.edu.sg/comcen/antivirus/faq.htm#10 FYI & E.G.

Hemantgiri S. Goswami