SQL Server 2000 - PIVOTING

Nov 29, 2007

I need some help here in Pivoting the table.
I have the table with the Following Columns.. and here is the sample data. Assume that below table will just have one weeks worth of data.

I can write a stored Proc using cursor, but I just want to learn how
to do it with using cursors

TD-------- Acct------ RouteID----Symbol---- Quantity----
---------- ---------- ---------- ---------- -----------
11/26/2007 40B91209-- CSFB------ GBL--------100
11/26/2007 40B91209-- SIGMA-X----TDY--------100
11/26/2007 4W3L1209-- CSFB------ BIDZ------ 1300
11/26/2007 4W3L1209-- CSFB------ SURW------ 100
11/27/2007 HFS10003-- SIGMA-X----URBN------ 500
11/27/2007 RAM10001-- ISE--------DSCP------ 300
11/27/2007 RAM10001-- SIGMA-X----HYC--------468
11/28/2007 HFS10003-- CSFB------ ARO--------5900
11/28/2007 HFS10003-- CSFB------ CAB--------1300
11/28/2007 HFS10003-- CSFB------ PNRA------ 4600
11/29/2007 RAM10001-- CSFB------ DSCP------ 175
11/29/2007 HFS10003-- CSFB------ CL-------- 220
11/29/2007 WIL10008-- SIGMA-X----CBM--------1400


The output should look some thing like this. If some can help me

AcctNum----RouteID--symbol--MON--TUE--Wed--THU---- FRI
WIL10008---SIGMA-X--CBM-----0----0----0----1400-- 0

Thanks for any help.

View 2 Replies


ADVERTISEMENT

Pivoting

Sep 21, 2007

hello guys !!!

i have a table as

id date data
1 3/10/2007 "hello"
1 4/10/2007 "hi"
2 3/10/2007 "hello"
2 4/10/2007 "why"


i need the output like

id 3/10/2007 4/10/2007
1 "hello" "hi"
2 "hello" "why"


Any idea ???
any means to do it??

View 5 Replies View Related

Pivoting

May 7, 2008



Hi ,


My source file looks like this.








Month

Mar-07
Apr-07
May-07







Non-Accruals

$304,732,515
$307,051,978
$308,274,921

REO

$115,072,839
$123,957,394
$149,744,174

Home Equity Total NPA

$419,805,354
$431,009,372
$458,019,095


Destination table should look like this.










Month
HE Non-Accruals
HE REO
Home Equity Total NPA

Mar-07
$ 304,732,515
$ 115,072,839
$ 419,805,354

Apr-07
$ 307,051,978
$ 123,957,394
$ 431,009,372

May-07
$ 308,274,921
$ 149,744,174
$ 458,019,095



Can anyone help me to write t-sql code to transfer data into destination table as its shown above.
Thanks

View 1 Replies View Related

Pivoting A CTE

Apr 25, 2008



I've done both a CTE and a pivot, but never together. I did see a few examples out there and followed them, but mine isn't working. I have four 'tables' within the CTE, and then my final select statement joins all of them and attempts to pivot. My error is:


Msg 156, Level 15, State 1, Line 90

Incorrect syntax near the keyword 'PIVOT'.



WITH TwoYrsActual (LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,"06Actual")

AS (

SELECT DISTINCT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD AS '06Actual'

FROM dbo.FactInventoryHistory HIS

INNER JOIN DimLocation LOC ON

His.LocationType_Code = Loc.LocationType_Code

INNER JOIN DimDate Date ON

His.Week_idx = Date.Date_Idx

INNER JOIN DimScenario Scenario ON

His.Scenario_Idx = Scenario.Scenario_Idx

WHERE Scenario_Code = 'FY06A'

GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD

),

OneYearActual (LocationType_Name, FiscalYear_Name,FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,

"07Actual")

AS (

SELECT DISTINCT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD AS '07Actual'

FROM dbo.FactInventoryHistory HIS

INNER JOIN DimLocation LOC ON

His.LocationType_Code = Loc.LocationType_Code

INNER JOIN DimDate Date ON

His.Week_idx = Date.Date_Idx

INNER JOIN DimScenario Scenario ON

His.Scenario_Idx = Scenario.Scenario_Idx

WHERE Scenario_Code = 'FY07A'

GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD

),

PresentYrActual (LocationType_Name,FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,

"08Actual")

AS (

SELECT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD AS '08Actual'

FROM dbo.FactInventoryHistory HIS

INNER JOIN DimLocation LOC ON

His.LocationType_Code = Loc.LocationType_Code

INNER JOIN DimDate Date ON

His.Week_idx = Date.Date_Idx

INNER JOIN DimScenario Scenario ON

His.Scenario_Idx = Scenario.Scenario_Idx

WHERE Scenario_Code = 'FY08A'

GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD

),





PresentYrPlanned (LocationType_Name,FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedTY,

ActualSalesAmtUSD)

AS (

SELECT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD AS PlannedTY,

ActualSalesAmtUSD

FROM dbo.FactInventoryHistory HIS

INNER JOIN DimLocation LOC ON

His.LocationType_Code = Loc.LocationType_Code

INNER JOIN DimDate Date ON

His.Week_idx = Date.Date_Idx

INNER JOIN DimScenario Scenario ON

His.Scenario_Idx = Scenario.Scenario_Idx

WHERE Scenario_Code = 'FY08P'

GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,

ActualSalesAmtUSD

)





select * from (

SELECT PresentYrActual.LocationType_Name, PresentYrActual.FiscalPeriodOfYear, PresentYrActual.FiscalWeekOfPeriod, PresentYrActual.FiscalWeekOfYear, PresentYrActual.LocationType_Code, PresentYrActual.Scenario_Idx, PresentYrActual.Scenario_Code, "08Actual", "07Actual", "06Actual",PlannedTY

FROM PresentYrActual

FULL OUTER JOIN OneYearActual ON

PresentYrActual.FiscalWeekOfYear = OneYearActual.FiscalWeekOfYear AND

PresentYrActual.LocationType_Code = OneYearActual.LocationType_Code

FULL OUTER JOIN TwoYrsActual ON

PresentYrActual.FiscalWeekOfYear = TwoYrsActual.FiscalWeekOfYear AND

PresentYrActual.LocationType_Code = TwoYrsActual.LocationType_Code

FULL OUTER JOIN PresentYrPlanned ON

PresentYrActual.FiscalWeekOfYear = PresentYrPlanned.FiscalWeekOfYear AND

PresentYrActual.LocationType_Code = PresentYrPlanned.LocationType_Code

)

PIVOT

(

SUM(PlannedTY)

FOR PresentYrActual.LocationType_Code IN (C, M))

AS pivottable

View 13 Replies View Related

Pivoting A Recordset

Nov 12, 2004

I have a reference table that looks like this

id | value
==========
1,abc
1,def
1,ghi
2,def
2,jkl

I want these values to go horizontally into another table matched on id, to look like this:

id | value
========
1,abc def ghi
2, def jkl

I built a cursor to parse through it but was taking forever (there's 185,000 records in the reference table). Any idea's on the fastest way to perform this function?

View 1 Replies View Related

Getting Nulls When Pivoting

Aug 29, 2013

I'm getting nulls on execution of the below query. I have tried both isnull and coalesce, but to no avail.

select *
from
(
select
a.BusinessUnitCode,
a.AdmitCCYYMM As Date, a.[Count of Admits]*1.0 / m.MemberCount * 12000 AdmitsPer1000

[Code] .....

View 3 Replies View Related

Related To Pivoting

Jan 4, 2006

there is a prblem with data in pivoting the table.
problem is like this--

there is some data 'xy' and some data 'xy '. when i m giving 'xy' as a pivot key value it doesent recognise 'xy ' and viseversa..

i can't reduce the size of the datatype coz there is some data of diffrent size as 'abcd'.

this data is loaded from excel sheet to sql sever table.

wht can i do for this problem.

is there any method to truncate the indivisual data, i m using nvarchar datatype for this.

View 2 Replies View Related

Pivoting Query On T-sql

Jul 20, 2005

gud day.please help me. im working right now on a case study that willretrieve/produce a simple report on sql. my problem is I dont know howto pivot queries like in access. please help me. thanks

View 2 Replies View Related

Pivoting Table

Jan 28, 2008



Hello there I have a table like so

Q1 Q2 Q3 Q4 Sales Rep
1 5 6 0 John

2 3 6 0 Rod
3 2 3 0 Gill
4 5 1 0 Guy



And I would like to rearrange it like

John Rod Gill Guy
Q1
Q2
Q3
Q4



Is this something I can accomplish with the PIVOT or UNPIVOT commands? Is there another way?

View 9 Replies View Related

Pivoting A Table

May 7, 2008



Hi ,

I want to pull data from XLs file and put them in a table.

Source file looks like this.








Month

Mar-07
Apr-07
May-07







Non-Accruals

$304,732,515
$307,051,978
$308,274,921

REO

$115,072,839
$123,957,394
$149,744,174

Home Equity Total NPA

$419,805,354
$431,009,372
$458,019,095


Destination table should look like this.
Date(Only the first row ) in XL file is in the following format.
DATE(YEAR($O1)-1,MONTH($O1)-1,DAY($O1)).
From second row onwords data format is Money type.
I hope I need to convert the date row into SQL datetime type too. Otherwise it comes as NULL.










Month
Non-Accruals
REO
Home Equity Total NPA

Mar-07
$ 304,732,515
$ 115,072,839
$ 419,805,354

Apr-07
$ 307,051,978
$ 123,957,394
$ 431,009,372

May-07
$ 308,274,921
$ 149,744,174
$ 458,019,095



Can i create a SSIS package to do this job? if so , How? I'm not sure which transformation should i used and how?
Hope some one can help me

Thanks

View 5 Replies View Related

Pivoting Data

Apr 28, 2008


Hi,

Can anybody help me with the following...I want to Pivot the following data






Pcode
Year
Month
Mcode
Value



2
2008
March
EN10A
56349.1



2
2008
March
EN10B
1061.6



2
2008
March
EN10C
2.67



2
2008
March
EN10D
8370



2
2008
April
EN10A
819.31



2
2008
April
EN10B
245.09



2
2008
April
EN10C
33.38



2
2008
April
EN10D
2.31



























After Pivot...the data should be like this






















Pcode
Year
Month
EN10A
EN10B
EN10C
EN10D

2
2008
March
56349.1
1061.6
2.67
8370

2
2008
April
819.31
245.09
33.38
2.31



Can we use Pivot function or is their a easier way for doing this...Also the MCodes are dynamic so now there are only 4 distinct MCodes but they may be more than four...

View 4 Replies View Related

Pivoting Row Values Into Colums

Oct 11, 2004

I’d like to get some data which includes month values bound to a data grid. The data is stored in a table like so:

Measure Month Value
A June 10.00
A July 9.00
A Aug 11.00
B Jun 100.00
B Jul 98.00
B Aug 99.00
C Jun 0.75
C Jul 0.8
C Aug 0.91

I need to report the data like this:
Measure Jun Jul August
A 10 9 11
B 100 98 99
C 75% 80% 91%

This was simple in classic ASP. Just use two recordsets, create a new table cell for each month using the first recordset then use the second recordset for each row.

But is there a way to “Pivot� or rotate the data so I can use the DataGrid? It only seems possible if each month has its own column field in table. Each month add a new column.

I can restructure the database, if needed.

I thought about creating a Cube, but that seems to have its own limitations. For example what if I want to add a Column for Quarter and year totals? I don’t think it’s possible to show multiple planes like that in an query of a cube.

It seems that this might be resolved in the presentation layer or the data layer. Any Suggestions?

View 1 Replies View Related

How To Set Null Values To Zero When Pivoting

Jun 4, 2008

I have a table with the following structure

[ID] [A] [B] [Cnt] [Qty]
1 s v1 4 40
2 g v1 2 23
3 p v2 7 22
4 l v3 1 6
5 v v4 7 5


Since I do not know before hand what [B] will be , I have created a dynamic script to pivot the data :


Select *
from ( select [ID],[A],[B],sum([Cnt]) AS Cnt
group by [ID],[A],[B]
) a
PIVOT ( sum(cnt) for [B] in (@list)) b


Now thequstion is :
1. How do I amend this qry to eliminate nulls in my output
[ID] [A] [v1] [v2] ......[vn]
1 s null 9
2 g 10 null


2. Is it possible to include both [cnt] and [Qty] traspose along
[B] = v1,v2 ...... vn


Any advice will be most welcome.

Thanks

View 4 Replies View Related

Pivoting Data Works In One Db But Not Another

Mar 20, 2008

In the following code examples I got to learn PIVOT, I found an error for SUM. However when this is ran against the AdventureWorks db it works fine. Notice it is using a table variable and not an actual table. What do I need to do to my db to get this to work?

Thanks!




declare @sales table
(
[Year] int,
Quarter char(2),
Amount float
)

insert into @sales values(2001, 'Q1', 70)
insert into @sales values(2001, 'Q1', 150)
insert into @sales values(2002, 'Q1', 20)
insert into @sales values(2001, 'Q2', 15)
insert into @sales values(2002, 'Q2', 25)
insert into @sales values(2001, 'Q3', 50)
insert into @sales values(2002, 'Q3', 20)
insert into @sales values(2001, 'Q4', 90)
insert into @sales values(2001, 'Q4', 80)
insert into @sales values(2002, 'Q4', 35)

select * from @sales
PIVOT (
SUM(Amount)
for Quarter in (Q1, Q2, Q3, Q4)) as p

Yields...
Incorrect syntax near 'SUM'.

View 2 Replies View Related

Pivoting A Results Table MSSQL

Nov 12, 2007

I have a query in which I would like to pivot the resultsI presently have my results displaying something like this. OrderNumber    Product       OrderQuantity---------------        ---------------    ----------------------0608                Prod1          30608                Prod2          120608                Prod3          2 What I am after is for the results to display something like this.OrderNumber    Prod1   Prod2   Prod3 
---------------        ---------   ---------  ---------
0608                3           12       2 This is using SQL Server ver 8.0  

View 3 Replies View Related

T-SQL (SS2K8) :: GROUP BY CUBE Aggregation - Pivoting On 2 Totals

Aug 1, 2014

I'm trying using the GROUP BY CUBE aggregation. Currently I have this working as such:

SELECT
ISNULL(CONVERT(VARCHAR,Date), 'Grand Total') Date
,ISNULL([1 Attempt],0) [1 Attempt]
,ISNULL([2 Attempts],0) AS [2 Attempts]
,ISNULL([3 Attempts],0) AS [3 Attempts]
,ISNULL([4 Or More],0) AS [4 Or More]

[Code] .....

Basically this is used to work similar to a Pivot table in excel. My data will look as follows:

Date 1 Attempt2 Attempts3 Attempts4 Or MoreTotal
2012-09-04 239 68 2 8 317

The problem I'm having is the Total column. Although this is summing the line values correctly, the total should be based on the sum not count of attempts i.e. 1 x 239, 2 x 68, 3 x 2, 4 x 8

If I change the FROM select clause to use SUM instead of COUNT

SELECT
CONVERT(DATE,[Date]) Date
,ISNULL(AttemptsFlag,'Total') as Attempt
,SUM(NoOfTimes) AS Totals
FROM
XXXXX
GROUP BY
CUBE([Date],AttemptsFlag)

It will return the correct Total amount but not the right numbers for the Attempt groupings...

View 1 Replies View Related

SSRS -- Data Driven Subscription And Pivoting For Dynamic Parameter Mapping

Feb 12, 2007

Hi,

For the Data Driven Subscription in SSRS we are using the following stored procedure

In Step 3 - Create a data-driven subscription



create procedure spRSGetReportSettings

(

@ReportID as integer

) as

begin

set nocount on

declare @t as table(y int not null primary key)

declare

@cols as nvarchar(max),

@y as int,

@sql as nvarchar(max)

set @cols=stuff(

(select N',' + quotename(y) as [text()]

from (select ParameterName as y from Reportsettings where reportid=1) as Y

order by y

For XML Path('')),1,1,N'');

set @sql=N'select * from

(select reportid,parametername, parametervalue from ReportSettings where reportid= ' + Cast(@ReportID as varchar(5)) +' ) as D

pivot(min(parametervalue) for parametername in(' + @cols +N')) as p'

exec sp_executesql @sql

end



Basically the idea is to maintain a single report parameter setting table for multiple reports.

Structure of the table is as given below

ReportID, ParameterName, ParameterValue.

Using Pivot we can generate the ParameterName/ParameterValue combinations for each report. This stored procedure is working fine in query editors(Management Studio)

But, in SSRS it is giving any results.

In Step 4 - Create a data-driven subscription,

Get the value from the database drop down, I am not getting any database columns.

Please help.

Kumar

View 3 Replies View Related

Can I Keep Sql Server 2000 If Upgrade Win 2000 To Win 2003 (was Sql Server 2000)

Feb 24, 2005

Hello, i have a question that the sql server 2000 is install in window 2000 server. If i want to update to window 2003. Is that any problem in sql server 2000. I am worry about whether we will have problem after update. What i need to do? Many thanks.

View 5 Replies View Related

SQL SERVER 2000: In Which Format The Datetime Will Be Stored In Sql Server 2000?

Feb 28, 2008

Hi All,
I would like to know, how the datetime will be stored in the sqlserver datetime column.
Because some time i am giving the date in dd/mm/yyyy and sometime mm/dd/yyyy.
while give the date in mm/dd/yyyy works fine but not in the another case. and also while i execute a query on query analyser it shows the datetime in
yyyy/mm/dd format.
So anyone can please tell me how the dates will be stored in the datetime column of sqlserver database?
Thanks in Advance.
Regards,
Dhanasekaran. G

View 2 Replies View Related

Upgrading SQL Server 2000 Standard To SQL Server 2000 Enterprise

Sep 14, 2004

I am currently running SQL Server 2000 Standard on my production system, and I am looking to upgrade the system to Windows 2000 Adv. Server. I would also like to upgrade SQL Server 2000 Standard to SQL Server 2000 Enterprise to utilize more than 2GB of memory. Can anyone tell me what is the best way to upgrade the system, and please provide some feedback on your experiences with the upgrade. Thanks in advance.

View 2 Replies View Related

Sql Server 2000 Linked Server To Access 97/2000 Incompatability

Jul 20, 2005

We are experiencing a problem with Sql Server 2000 linking to anAccess 97 file. We have two machines that link to this .mdb file, andwe recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. Thelink on this upgraded machine no longer works, giving this message:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: Cannot open a database created witha previous version of your application.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].The link on the older machine still works. We decided to tryconverting a copy of the file to Access 2000 to see if the newerpatches/drivers/whatever no longer supported 97. We set up a link onboth machines to this file, and they both work. However, on theupgraded machine, the following error is receievedServer: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: System resource exceeded.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'ICommandText::Execute returned 0x80004005: ].when making 1-3 connections to the the linked server, while the oldermachine supports at least 7 simultaneous queries connecting to thelinked server and still hasn't produced that error.Does anyone have any idea if there is a known issue with linking toAccess 97/2000 files under MDAC 2.8, Jet 4.0, etc? Any light anyonecan shine on this subject would be greatly appreciated.

View 1 Replies View Related

SQL Server 2000 Standard On Windows 2000 Advanced Server

Jul 20, 2005

What is the limitation of memory that SQL Server 2000 Standard can usewhen running on a Windows 2000 Advanced Server platform?

View 1 Replies View Related

Upgrate Sql Server 2000 Standard To Sql Server 2000 Enterprise

Sep 15, 2006

Hi, I need to know if somebody knows like making the update of Standard SQL 2000 to Enterprise. Greetings.

View 3 Replies View Related

DB File Size Limit With SQLServer 2000 In Small Business Server 2000

Mar 15, 2006

Thanks in advance. What is maximum SQL Server database (*.mdf) file size with SQL Server 2000 as part of Microsoft Small Business Server 2000? (Database files were limited to 10 GB in SBS 4.5 with SQLServer 7.0... has this changed?).

View 1 Replies View Related

Sql Server 2000 Developer Edition W/MSDE 2000 Release A Install

May 21, 2005

Can you install Sql Server 2000 Developer Edition with MSDE 2000 release A already installed?

View 2 Replies View Related

Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A

Feb 17, 2004

My objective is to use Enterprise Manager to move (copy) my SQL db from the server to my windows desktop computer.

I downloaded MSDE and am having trouble installing it, no doubt because I do not understand the documentaion (ReadMeMSDE2000A.htm).

When I try to run setup, I get that message that says:

"A strong SA password is required for security reasons. Please use SAPWD switch to supply the same."

Considering my purpose, do I need a "strong" SA password? If not, how do I get around it? If yes, how do I set it up?

I am a Mac user so I have poor windows skills, please make it as painless as possible for me, thanks!

Ron

View 3 Replies View Related

Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release

Feb 16, 2006

venu writes "Hi,

Am very new to MS SQL adminstration
Can anybody help me out how to work on Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A just for the practice.

The activity which am going to workout on MSDE is below.

How to install SQL(on XP)
How the layout will be(like if i insall MSDE what are all Application will be and how they depends on each other)
How to create/delete tables if so, how can we do it either by GUI or CUI

just i need a clarifications reg same

Thank you,
venu"

View 1 Replies View Related

Performance Issues - Access 2000 Frontend SQL Server 2000 Backend

Jul 23, 2005

Hi,Simple question: A customer has an application using Access 2000frontend and SQL Server 2000 backend. Data connection is over ODBC.There are almost 250 concurrent users and is growing. Have theysqueezed everything out of Access? Should the move to a VB.Net frontendtaken place ages ago?CheersMike

View 4 Replies View Related

Vista Business ODBC/DSN To Remote Windows 2000 Server / SQL 2000

Oct 8, 2007

Hi,
Just upgraded some development desktops to Vista Business. However we need
to still connect to some older remote windows 2000/SQL 2000 servers.

Trying to setup an ODBC system DSN on our Vista Business local desktop we get the
following errors -

-START ERROR WINDOW-
Connection Failed:
SQLState: '01000'
SQL Server Error: 772
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(SECDoClientHandshake()0.
Connection failed:
SQLState: '08001'
SQL Server Error: 18
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SSL Security Error
-END ERROR WINDOW-

Any help greatly appreciated as this is stopping us from making
database/table connections etc. We've checked the firewall setup and all is well there.

PS - we can still connect fine using XP or windows 2000 desktops and their
local DSNs.

View 8 Replies View Related

Maximum Capacity Specifications Comparison Table For Access, SQL Server 7, 2000 And MSDE 2000

May 27, 2008











Parameter
Access 2000/XP
SQL Server 7.0
SQL Server 2000
MSDE 2000

Number of instances per server
n/a
n/a
16
16

Number of databases per instance / server
n/a
32,767
32,767
32,767

Number of objects per database
32,768
2,147,483,647
2,147,483,647
2,147,483,647

Number of users per database
n/a
16,379
16,379
16,379

Number of roles per database
n/a
16,367
16,367
16,367

Overall size of database (excluding logs)
2 GB
1,048,516 TB
1,048,516 TB
2 GB

Number of columns per table
255
1024
1024
1024

Number of rows per table
limited by storage
limited by storage
limited by storage
limited by storage

Number of bytes per row





(Excluding TEXT/MEMO/IMAGE/OLE)
2 KB
8 KB
8 KB
8 KB

Number of columns per query
255
4,096
4,096
4,096

Number of tables per query
32
256
256
256

Size of procedure / query
64 KB
250 MB
250 MB
250 MB

Number of input params per procedure / query
199
1,024
2,100
2,100

Size of SQL statement / batch
64 KB
64 KB
64 KB
64 KB

Depth of subquery nesting
50
32
32
32

Number of indexes per table
32
250 (1 clustered)
250 (1 clustered)
250 (1 clustered)

Number of columns per index
10
16
16
16

Number of characters per object name
64
128
128
128

Number of concurrent user connections
255
32,767
32,767
5

View 1 Replies View Related

Bit-data From SQL Server 2000 (2005 Working, 2000 Doesn't)

May 19, 2008

 Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring             <SelectParameters>                <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" />            </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000?  (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael   

View 2 Replies View Related

Problem Using Access 2000 As A Front-end To SQL Server 2000 Tables

Jul 23, 2005

I've created a small company database where the tables reside in a SQLServer database. I'm using Access 2000 forms for a front end.I've got a System DSN set-up to SQL Server and am using links withinAccess 2000 to get to the SQL Server tables.My forms worked fine until I made a few minor changes to the databaseschema on SQL Server (e.g. added a foreign key, or added a column).After that, all the links break - I click on a table link and get anerror msg like "invalid object name."Deleting the links after a schema change and re-adding the links seemedto fix the problem. The forms I'd already created seemed to work fineafter re-creating the links.But then I got more advanced with my forms. I have it set up so thatfor certain entry fields, the combobox gets populated with values froma table (the description appears in the drop-down and the correspondingprimary key value gets populated in the table). I created a number offorms using this technique, entered data, and everything worked fine.Made a small schema change and it broke everything -- not the actualtable links, but the functionality for the drop-downs. My values nolonger appeared, and this was true for forms that accessed tables whoseschemas did not change.This is driving me nuts. Is there any way to keep my forms frombreaking each time I make a small schema change?Thanks.- Dana

View 5 Replies View Related

SQL Server 2000 Installation Problem On Windows 2000 Professional

Jul 20, 2005

Hello,I received the error message below when i'm trying to install SQLServer 2000 standard edition into a Windows 2000 Professionaleworkstation.Error :Microsoft SQL server 2000 Standard Edition server components is notsupported on this operating system. Only client components will beavailable for installation.Any request modification ?Best regards,Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved